Link to home
Start Free TrialLog in
Avatar of dbote
dboteFlag for United States of America

asked on

Order of Parameters using an MS Access "StoredProcedure" with VB.NET

I have a query in an MS Access database with 2 parameters, ParamA and ParamB.  I am able to successfully run the query IF I send the parameters in the same order Access creates/uses them.  If I send the parameters by name out of order the query fails.

I can also name the parameters anything in VB.NET as long as the parameters are sent in the order MS Access expects them.

I stumbled upon this because I wanted to create "Optional Parameters" for queries.

This is not the behavior with MS SQL server so I was caught off guard.
Dim cm As New OleDbCommand("QRY_TEST_PARAMS", cn)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.AddWithValue("AnyName1", 2)
cm.Parameters.AddWithValue("AnyName2", 1)

Produces the same result as:

Dim cm As New OleDbCommand("QRY_TEST_PARAMS", cn)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.AddWithValue("ParamA", 2)
cm.Parameters.AddWithValue("ParamB", 1)

And the following fails:

Dim cm As New OleDbCommand("QRY_TEST_PARAMS", cn)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.AddWithValue("ParamB", 2)
cm.Parameters.AddWithValue("ParamA", 1)

Open in new window

SOLUTION
Avatar of Meir Rivkin
Meir Rivkin
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbote

ASKER

Thank you for the article reference.  It definitely spells out that the order of parameters matters.  

Do you know a way to send these parameters in an optional fashion.  Sending an empty string does not work as the article suggests.  Although, an empty string on the MS Access side works fine.

Thank you.
sedqwick is correct.  

A standard Access parameterized query can look like this (parameters being non-unique):

        Dim SQL As String = "SELECT * FROM Employees WHERE FirstName = ? AND LastName = ?"
        Dim cnString As String = "connection string"

        Dim cmd As New OleDb.OleDbCommand()
        cmd.Connection = New OleDb.OleDbConnection(cnString)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = SQL

        cmd.Parameters.AddWithValue("?", "John")
        cmd.Parameters.AddWithValue("?", "Doe")

        cmd.Connection.Open()
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()


        Stop
        cmd.Connection.Close()

        cmd.Dispose()
        cmd = Nothing
        reader = Nothing

Avatar of dbote

ASKER

VBRocks your example is absolutely correct, I understand this much.  How would I get optional parameter values into the MS Access query?  Sending empty strings does not seem to work as the reference article suggests.

I hate "building" string SQL in code so I would like to avoid it.

Avatar of dbote

ASKER

Here's what I get....
In Access when I run the query with two Parameters specified, ParamA and ParamB, and then leave ParamA as and empty string by hitting Enter when prompted and giving the value 1 for ParamB when prompted....I get the expected 521 records.

If I send an empty string as the article suggests by using  cm.Parameters.AddWithValue("ParamA", "") and sending paramB with cm.Parameters.AddWithValue("ParamB", "1") I get no records returned.  

I can cycle through the parameters and get the values of each with cm.Parameters.Item(i).Value and see that that the values are there as expected but still no records are returned.

Ideas?
Why don't you design your query using "LIKE" instead of "="?

For example, notice the 2 parameters in this Access Query (@FirstName, @LastName), and how they are used, using the "LIKE" keyword:

    PARAMETERS [@FirstName] Text ( 255 ), [@LastName] Text ( 255 );
    SELECT Employees.*
    FROM Employees
    WHERE (((Employees.[FirstName]) LIKE [@FirstName]) AND ((Employees.[LastName]) LIKE
    [@LastName]));


Then, instead of passing a zero-length string (""), pass a wildcard (*):

    cm.Parameters.AddWithValue("ParamA", "*")


ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbote

ASKER

VBRocks thanks for sticking with me on this one and giving me some great suggestions.  I will be dividing the points on this one since Sedgwick showed me that article and straightened out one finding.

I was using LIKE in my queries but here's the confusing part.  Access 2003/2000 likes the * as the wildcard and not the %.  So I would get it to work in one, VB vs. Access, but not the other.  Now I just have to remember to use the % from VB.NET hitting Access 2003/2000 queries and use the * when running the query from within Access 2003.

Whew!  Thanks for putting me on the correct path!
Avatar of dbote

ASKER

Thanks again!