Re: ADODB Recordset Not Loading From Parameter Query

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
    ... 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)
  • Re: Calling SP from ADO and passing parameters
    ... I moved the @checkreturn param to the local declarations section ... > Dim prm As ADODB.Parameter ... > Set prm = cmd.CreateParameter("@MatchID", adInteger, adParamInput,, ...
    (microsoft.public.data.ado)
Loading