Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Get  ADO recordset from Access query with parameters.

Posted on 2001-07-24
10
Medium Priority
?
258 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 200 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
Independent Software Vendors: 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: 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 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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

578 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