Solved

Get  ADO recordset from Access query with parameters.

Posted on 2001-07-24
10
225 Views
Last Modified: 2010-05-02
I need help to get ADO recorset from query "qryTest" in VB.

Query was created in NorthWind.
Here you are SQL view:

SELECT Employees.FirstNAME, Employees.LastName
FROM Employees
WHERE (Employees.FirstNAME Like [FName] & "*" Or [FName]="") AND
(Employees.LastNAME Like [LName] & "*" Or [LName]="");


"qryTest"  has two parameters: [FName] and [LName].


In VB project I developed very simple code:


Public Sub main()
 Dim conn As ADODB.Connection
 Dim cmd As ADODB.Command
 Dim rs As ADODB.Recordset, paramFname As Parameter
 dim paramLname As Parameter
 Set conn = New ADODB.Connection
 Set cmd = New ADODB.Command
 
 
 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 Data Source=" & App.Path & "\northWind.mdb; Jet OLEDB;"
 Set cmd.ActiveConnection = conn

 cmd.CommandText = "qryTest"
 cmd.CommandType = adCmdStoredProc
 Set paramFname = cmd.CreateParameter("FName", _          adVarChar, adParamInput, 10, "a")
 cmd.Parameters.Append paramFname
 Set paramLname = cmd.CreateParameter("LName", _  adVarChar, adParamInput, 20, "")
 cmd.Parameters.Append paramLname
  Set rs = cmd.Execute(, cmd.Parameters)

 Debug.Print rs.Fields(1)
End Sub

I know, there are records there(in NorhWind),
 but RS.eof=true, I could not get any records with any values of parameters(FName or LName)

What is wrong with this code?
0
Comment
Question by:orrarr
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6314962
Not sure but I believe wildcards only work at the beginning of strings.

Zaphod.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 6315746
Are you sure you don't have On Error Resume Next somewhere?

The reason I ask is that the line:
Set rs = cmd.Execute(, cmd.Parameters)

Should just be:
Set rs = cmd.Execute

The parameters argument is for a variant array of parameter values not the Parameter collection.

Other than that it seems OK.
0
 
LVL 3

Expert Comment

by:Rem
ID: 6316163
Zaphod

Wildcards work all the way

"SELECT * from YourTable WHERE YourField Like '%" & frm.YourField.Text & "%'"

Bye

Rem
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6316191
Maybe I was thinking of MSSQL then, I know one of the common DB's has this limit....

Zaphod.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6318268
Zaphod,

You are probably thinking of the following KB article
INFO: ADO's Find and Filter Support Usage of Wildcards (Q226119)

An in particular the following:

%example is NOT supported (ends with):

rs.Find "MyField Like '%St'", 0, adSearchForward, ADODB.adBookmarkCurrent

But this does not apply in this case as the Find method is not used.

Anthony
0
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6320373
Ah yeah, that must have been it.

Thanks,

Zaphod.
0
 

Author Comment

by:orrarr
ID: 6323983
Acperkins,

Did You try to run this code with "Set rs = cmd.Execute"?
Could you please, copy & paste query into NorthWind - save as "QryTest,copy & paste VB code into new VB project, get reference ADO 2.5 and run this code.
I'm getting "rs.eof=true" Error
What about You???

Thanks.

P.S. This query is working fine in Access.

0
 
LVL 3

Expert Comment

by:Rem
ID: 6324040
Hi,

Replace your wildcards (*) with % (percentage sign.
The query won't run in access but will work when run through your vb programme.

OLEDB 4.0 acts like a reel SQL database.

This should work.
I ran into the same "bug" while copying a access query into my vb code. (ie rs.EOF)

C U

Rem
0
 
LVL 3

Expert Comment

by:Rem
ID: 6324108
Hi,

Replace your wildcards (*) with % (percentage sign.
The query won't run in access but will work when run through your vb programme.

OLEDB 4.0 acts like a reel SQL database.

This should work.
I ran into the same "bug" while copying a access query into my vb code. (ie rs.EOF)

C U

Rem
0
 
LVL 3

Expert Comment

by:Rem
ID: 6324116
Don't know what went wrong, but sorry for the double input.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question