Re: ADODB Recordset Not Loading From Parameter Query

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: OpenRecordset
    ... query is taking values or criteria from fields on a form (which are treated ... Dim qdf As DAO.QueryDef ... Dim prm As DAO.Parameter ...
    (microsoft.public.access.formscoding)
  • 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)
  • 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)
  • Re: QueryDef
    ... enough to evaluate when the query is opened from the database window. ... Dim db1 As Database ... Dim prm As Parameter ...
    (microsoft.public.access.formscoding)