Solved

Get  ADO recordset from Access query with parameters.

Posted on 2001-07-24
10
216 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now