Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get  ADO recordset from Access query with parameters.

Posted on 2001-07-24
10
Medium Priority
?
249 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

721 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