Named Parameter Problem (1 Viewer)

cosmarchy

Registered User.
Local time
Today, 05:33
Joined
Jan 19, 2010
Messages
116
I have the following code which I cannot get to work using VBA:

Query "z_TestQuery"
Code:
SELECT PN, DE
FROM tblPN
WHERE (PN like [@Param1] AND MN like [@Param2]);
VBA code:
Code:
Function TestQuery2()

    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim cm As New ADODB.Command
    Dim PN As String
    Dim MN As String
    Dim RecordsAffected As Integer
  
    Set cn = CurrentProject.Connection
    cn.CursorLocation = adUseClient
        
    PN = "GE*"
    MN = "Al*"
    
    cm.ActiveConnection = cn
    cm.NamedParameters = True
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "z_testQuery"
    
    cm.Parameters.Append cm.CreateParameter("@Param2", adVarChar, adParamInput, Len(MN), MN)
    cm.Parameters.Append cm.CreateParameter("@Param1", adVarChar, adParamInput, Len(PN), PN)
    
    Set rs = cm.Execute(RecordsAffected)
    
    Debug.Print "Record count (RecordCount): " & rs.RecordCount
    Debug.Print "Record count (RecordsAffected): " & RecordsAffected
    Debug.Print

End Function

If I run the query directly and fill in the appropriate parameters when prompted, I get results returned so this query acts as you would expect.

But, if I run the VBA function, I do not get any results. At this point I am not concerned with the returned data itself, as I'll do something with the RecordSet in due course, but I am not able to populate the RecordSet.

I initially wondered whether I was not checking the RecordSet for the record count correctly, hence I tried two ways to get the record count in order to see if I was actually returning results.

Does anyone have any ideas as to why this doesn't work?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,357
Hmm, is this in the correct forum? If not, please let us know where to move it.

Is this line correct?
Code:
Set rs = cm.Execute(RecordsAffected)

I don't use ADO much, but I was expecting something more like:
Code:
Set rs = cm.Open(...)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:33
Joined
Jan 20, 2009
Messages
12,849
The wildcard character for ADO is the percent sign (%) .
 

cosmarchy

Registered User.
Local time
Today, 05:33
Joined
Jan 19, 2010
Messages
116
Hmm, is this in the correct forum? If not, please let us know where to move it.
Probably not as I hadn't noticed that I'd put it in the Visual Basic forum :rolleyes:
I would have said that Modules & VBA forum was more appropriate, so please feel free to move as necessary...

Also tried
Code:
Set rs = cm.Open(...)
but this didn't work either.

The wildcard character for ADO is the percent sign (%)
Tried this and unfortunately it still doesn't work.

Thanks for your suggestions guys...😀
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:33
Joined
Oct 29, 2018
Messages
21,357
Thread moved to new forum. Sorry, I have no further suggestions at this time.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 19, 2013
Messages
16,553
pretty sure you can't apply parameters when executing a query in vba, you need to hard code them into your sql str. Certainly applies to dao.

perhaps something like

querydefs("z_testQuery").sql=replace(replace(querydefs("z_testQuery").sql,"@Param2",MN),"@Param1",PN)

or alternatively if the query is a stored procedure in sql server, the parameters will have been declared in the stored procedure and are effectively populated using an optional variant array.

cm.Parameters(0).Value=MN
cm.Parameters(1).Value=PN

or perhaps

cm.Parameters("@Param2").Value=MN
cm.Parameters("@Param1").Value=PN

Creating a parameter dynamically does not make sense to me - the sql won't be using it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:33
Joined
May 7, 2009
Messages
19,169
It's a No, No.
from which DB does z_testQuery belong?
obviously Outside of the current db, otherwise you can just use a DAO method.
Your Connection is wrong, the connection should point to the external db.
 

Users who are viewing this thread

Top Bottom