ADODB Recordset Not Loading From Parameter Query

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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
 
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.
 
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 said:
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

(no private e-mails, please)


Tim said:
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.
Click to expand...
Click to expand...
 
Sorry, no further ideas.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tim said:
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 said:
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

(no private e-mails, please)


Tim said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Sorry, no further ideas.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tim said:
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 said:
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

(no private e-mails, please)


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.
Click to expand...
Click to expand...
Click to expand...
 
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....

; )
 
Back
Top