Solved

Get  ADO recordset from Access query with parameters.

Posted on 2001-07-24
10
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

734 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