Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / May 2008

Tip: Looking for answers? Try searching our database.

ADODB Recordset Not Loading From Parameter Query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Tim - 08 May 2008 20:47 GMT
Trying to load a recordset from a command object.  Command executes without
problems.  No errors, that I can detect, in the count but it continues to
return no rows.

The query itself runs fine when running manually and supplying the parameters.
Here's the query:
------------------------------
PARAMETERS [@LastName] Text ( 255 ), [@FirstName] Text ( 255 ), [@City] Text
( 255 ), [@Firm] Text ( 255 );
SELECT tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName,
tblZip.City, tblFirms.FirmName AS Firm
FROM tblZip INNER JOIN (tblFirms INNER JOIN tblContacts ON
tblFirms.FirmID=tblContacts.FirmID) ON tblZip.Zip=tblContacts.Zip
WHERE (((tblContacts.LastName) Like [@LastName]) AND
((tblContacts.FirstName) Like [@FirstName]) AND ((tblZip.City) Like [@City])
AND ((tblFirms.FirmName) Like [@Firm]))
ORDER BY tblContacts.LastName;
------------------------------

Here's the code:
------------------------------
   Dim con As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim prm As ADODB.Parameter
   Dim rs As ADODB.Recordset

   Dim iTotal As Integer
   Dim iItems As Integer
   Dim iSubItems As Integer
   Dim lstItem As ListItem
   Dim lstItemNew As ListItem
   
   ' Clear the ListView control.
   lvContacts.ListItems.Clear

   ' Create db objects
   Set con = CurrentProject.Connection
       
   ' Set up command
   Set cmd = New ADODB.Command
   cmd.CommandText = "qryContacts"
   cmd.CommandType = adCmdStoredProc
   cmd.NamedParameters = True
   Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 25)
   cmd.Parameters.Append prm
   
   Set prm = cmd.CreateParameter("@FirstName", adVarChar, adParamInput, 25)
   cmd.Parameters.Append prm
   
   Set prm = cmd.CreateParameter("@City", adVarChar, adParamInput, 25)
   cmd.Parameters.Append prm
   
   Set prm = cmd.CreateParameter("@Firm", adVarChar, adParamInput, 25)
   cmd.Parameters.Append prm
   
   ' Add parameter values -- make sure not null
   cmd.Parameters("@LastName").Value = IIf(Len(txtLastName) > 0,
txtLastName, "%")
   cmd.Parameters("@FirstName").Value = IIf(Len(txtFirstName) > 0,
txtFirstName, "%")
   cmd.Parameters("@City").Value = IIf(Len(txtCity) > 0, txtCity, "%")
   cmd.Parameters("@Firm").Value = IIf(Len(txtFirm) > 0, txtFirm, "%")
   Set cmd.ActiveConnection = con
   Set rs = cmd.Execute
------------------------------
Have also tried creating the paramters differently.  Changing wild card char
to "*".  No matter what I try I get no rows.

Hoping there's a simple explanation.  Thanks for any help or guidance.
Douglas J. Steele - 08 May 2008 22:24 GMT
Are you saying that right after Set rs = cmd.Execute, you check
rs.RecordCount and it's equal to zero? The RecordCount property is known not
to be correct in many cases. Usually issuing a rs.MoveLast first solves the
problem.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Trying to load a recordset from a command object.  Command executes
> without
[quoted text clipped - 71 lines]
>
> Hoping there's a simple explanation.  Thanks for any help or guidance.
Tim - 08 May 2008 22:57 GMT
Yes, that's I'm saying.  I did also  rs.MoveLast -- inspecting in the locals
window no data.  Recordcount = -1 but no errors.  But running the query
direct and supplying the parameters does return data...

> Are you saying that right after Set rs = cmd.Execute, you check
> rs.RecordCount and it's equal to zero? The RecordCount property is known not
[quoted text clipped - 76 lines]
> >
> > Hoping there's a simple explanation.  Thanks for any help or guidance.
Douglas J. Steele - 09 May 2008 12:28 GMT
Sorry, no further ideas.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Yes, that's I'm saying.  I did also  rs.MoveLast -- inspecting in the
> locals
[quoted text clipped - 86 lines]
>> >
>> > Hoping there's a simple explanation.  Thanks for any help or guidance.
Tim - 09 May 2008 15:21 GMT
Thanks anyway.  Annoying as I prefer using the ADODB model.  Anyway, I
converted to DAO and it works fine.  I'm thinking something about ADO for
parameter queries might be broke.

Here's the full code in DAO.  This procedure might be interesting to others
as it takes the input from four text boxes as filtering criteria and then
populates a Listview control which I use to present selectable records for
the user.  As opposed to the standard Access of approach of binding all
records to the form and then using form navigation to traverse the records --
which just strikes me as awkward.....Different strokes I guess.
---------------------CODE-----------
Private Sub LoadContacts()
   On Error GoTo ErrorHandler

   ' Defines
   Dim iTotal As Integer
   Dim iItems As Integer
   Dim iSubItems As Integer
   Dim lstItem As ListItem
   Dim lstItemNew As ListItem
   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset

   ' Create data objects
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("qryContacts")

   ' Add parameter values -- make sure not null
   qdf.Parameters("[@LastName]").Value = IIf(Len(txtLastName) > 0,
txtLastName, "*")
   qdf.Parameters("[@FirstName]").Value = IIf(Len(txtFirstName) > 0,
txtFirstName, "*")
   qdf.Parameters("[@City]").Value = IIf(Len(txtCity) > 0, txtCity, "*")
   qdf.Parameters("[@Firm]").Value = IIf(Len(txtFirm) > 0, txtFirm, "*")
   
   ' Get data
   Set rst = qdf.OpenRecordset()
   rst.MoveLast
 
   ' Clear the ListView control.
   lvContacts.ListItems.Clear

   ' Add items and subitems to list control. Set Total Records Counter.
   iTotal = rst.RecordCount
   
    ' Loop through recordset and add Items to the control.
   rst.MoveFirst
   For iItems = 1 To iTotal
       If IsNumeric(rst(0).Value) Then
           Set lstItemNew = lvContacts.ListItems.Add(, , Str(rst(0).Value))
       Else
           Set lstItemNew = lvContacts.ListItems.Add(, , rst(0).Value)
       End If
       
       For iSubItems = 1 To rst.Fields.Count - 1
           lstItemNew.SubItems(iSubItems) = rst(iSubItems).Value
       Next iSubItems
       ' Advance to the next record
       rst.MoveNext
   Next iItems
   
   ' Clean up
   qdf.Close
   Set rst = Nothing
   Set qdf = Nothing
   Set dbs = Nothing
   Exit Sub
     
ErrorHandler:
   ' Ignore Error 94 which indicates you passed a NULL value.
   If Err = 94 Then
       Resume Next
   Else
   ' Otherwise display the error message.
       MsgBox "Error: " & Err.Number & Chr(13) & Chr(10) & Err.Description
   End If
End Sub
------------------------------------------------

> Sorry, no further ideas.
>
[quoted text clipped - 88 lines]
> >> >
> >> > Hoping there's a simple explanation.  Thanks for any help or guidance.
David W. Fenton - 10 May 2008 02:47 GMT
> I prefer using the ADODB model.

Why? It's wholly inappropriate when working with Jet data.

Signature

David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/

Tim - 10 May 2008 16:22 GMT
Fair question.  Probably three reasons:
1) ADO, update, is more similar to ADO.Net -- the std data model within .Net
which I use elsewhere -- so tool standardization is the driver; though I'm
realizing that DAO is the "native" data API for Access
2) Syntax. DAO, to me, is just not as elegant
3) Personal.  I still do some MFC/ Native windows development.  DAO used to
be the data model of choice in MFC but its been mostly stripped from the
current MFC tool set within Visual Studio -- though I think you can still use
it.  The existing DAO class libraries, in Vis Stud, have not been updated and
so probably wouldn't work with MFC <-> Access 2007.  ADO is supported but is
a hell of a lot harder to use.  So, we had this great library that MS removed
because ADO was to be the future but the "replacement" was about 1000 times
harder to use (sound familiar).  Much as that just irks me, I've just gotten
in the habit of ignoring DAO.  Which takes me back to #1 above.  I try not to
be one of these people who use the same tool/solution for every problem...but
there is a limit to the number of tools/APIs/what-have-you that I can
master....

;  )

> > I prefer using the ADODB model.
>
> Why? It's wholly inappropriate when working with Jet data.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.