dbote
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.
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(cnSt ring)
cmd.CommandType = CommandType.Text
cmd.CommandText = SQL
cmd.Parameters.AddWithValu e("?", "John")
cmd.Parameters.AddWithValu e("?", "Doe")
cmd.Connection.Open()
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
Stop
cmd.Connection.Close()
cmd.Dispose()
cmd = Nothing
reader = Nothing
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(cnSt
cmd.CommandType = CommandType.Text
cmd.CommandText = SQL
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
cmd.Connection.Open()
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
Stop
cmd.Connection.Close()
cmd.Dispose()
cmd = Nothing
reader = Nothing
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.
I hate "building" string SQL in code so I would like to avoid it.
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).Valu e and see that that the values are there as expected but still no records are returned.
Ideas?
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
I can cycle through the parameters and get the values of each with cm.Parameters.Item(i).Valu
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", "*")
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
Thanks again!
ASKER
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.