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

x
?
Solved

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

Posted on 2010-01-05
9
Medium Priority
?
846 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:dbote
  • 5
  • 3
9 Comments
 
LVL 42

Assisted Solution

by:Meir Rivkin
Meir Rivkin earned 600 total points
ID: 26181357
from http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access:

OleDb Parameters are recognised by their position, not by their name. Consequently, it is vital to ensure that parameters are added to the collection in the order they appear in the SQL, otherwise a "Too few parameters..." exception could occur. At the very least, your values will get inserted into the wrong fields, or nothing happens at all. For the sake of code readability, AddWithValues(string, object) can take a non-empty string giving a name to the parameter, although an empty string ("") will do.
0
 
LVL 1

Author Comment

by:dbote
ID: 26181487
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.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26181574
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

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 1

Author Comment

by:dbote
ID: 26181657
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.

0
 
LVL 1

Author Comment

by:dbote
ID: 26181869
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?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26182261
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", "*")


0
 
LVL 27

Accepted Solution

by:
VBRocks earned 1400 total points
ID: 26182317
Ooops, 1 correction on that, use the "%" wildcard, not the "*".  (At least that's what works with Access 2007)

Here's an example of executing this query from VB:

        'Pass the name of the query
        Dim SQL As String = "qryEmployees"          

        'Configure connection string
        Dim cnString As String = "connection string"


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

        'Set CommandType to StoredProcedure
        cmd.CommandType = CommandType.StoredProcedure

        'Configure Parameters in the same order
        cmd.Parameters.AddWithValue("?", "Gary")

        'Use "%" as a wildcard
        cmd.Parameters.AddWithValue("?", "%")

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

        'Process...

        cmd.Connection.Close()

        cmd.Dispose()
        cmd = Nothing
        reader = Nothing
0
 
LVL 1

Author Comment

by:dbote
ID: 26183378
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!
0
 
LVL 1

Author Closing Comment

by:dbote
ID: 31672937
Thanks again!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
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