Re: ADODB Recordset Not Loading From Parameter Query

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
------------------------------------------------

"Douglas J. Steele" wrote:

Sorry, no further ideas.

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


"Tim" <Tim@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:19A4A88F-05CA-4911-A765-308B56725A78@xxxxxxxxxxxxxxxx
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...

"Douglas J. Steele" wrote:

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.

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


"Tim" <Tim@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E90A7B7B-C9D4-44FA-AB8A-2497C55CDEAA@xxxxxxxxxxxxxxxx
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.






.

Relevant Pages

  • Re: ADODB Recordset Not Loading From Parameter Query
    ... Doug Steele, Microsoft Access MVP ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@FirstName", adVarChar, adParamInput, ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADODB Recordset Not Loading From Parameter Query
    ... The query itself runs fine when running manually and supplying the ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 25) ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADODB Recordset Not Loading From Parameter Query
    ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 25) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Übergabeproblem cmd.Execute
    ... Dim Comment As String ... Dim LehrgangsId As Long ... Dim prm As ADODB.Parameter ... Dim ReturnValue As Long ...
    (microsoft.public.de.access.clientserver)
  • ADODB Recordset Not Loading From Parameter Query
    ... Trying to load a recordset from a command object. ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@FirstName", adVarChar, adParamInput, 25) ...
    (microsoft.public.access.modulesdaovba)