Need help in the syntax to pass parameters to an MS Access query.

[emaduddeen]

Hi Everyone,

I need help in the syntax to pass parameters to an MS Access query.

Here is the code I use. It works in SQL Server but fails with a "No value given for one or more required parameters" error.

I'm assuming SQL Server syntax does not work with MS Access even though they are both Microsoft databases.

            ' Create the SQL String for the text boxes.
            '------------------------------------------
            strSQL = "Select ID, FirstName, MiddleName, LastName, " & _
                           " Format(RingSizeLeft, ""#0.00"") AS [RingSizeLeft], " & _
                           " Format(RingSizeRight, ""#0.00"") AS [RingSizeRight] " & _
                       "From Customers " & _
                      "Where ID = @CustomerID"

            ' Create an instance of the data adapter and then fill with data.
            '----------------------------------------------------------------
            objDataAdapterCustomerDetails = _
                New OleDbDataAdapter(strSQL, objConnection)

            ' Get query results into the Data Set.
            '-------------------------------------
            objDataAdapterCustomerDetails.Fill(objDataSetCustomerDetails, _
                                               "CustomerDetails")

            ' Add the parameter to the parameters collection of the Details Data Adapter.
            '----------------------------------------------------------------------------
            objDataAdapterCustomerDetails.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.Int)

Can you tell me what the MS Access syntax for this code should be?

Thanks.

Truly,
Emad

 

[jmcilhinney]

It's telling you that you haven't supplied a value because you haven't supplied a value. For a start, you're adding the parameter to the SelectCommand AFTER executing it. Secondly, you're adding the parameter but not setting it's value. Either use Add and then set the Value property or, better yet, use AddWithValue.

 

[emaduddeen]

Hi Everyone,

I have to get out of the bad habit of asking how to do stuff before I research a bit before posting questions.

Anyway, I figured out what I did wrong.

There were 2 things:
1. I placed the "Fill" statement before the statement that creates the parameter when it should be after it.
2. I needed to use the "?" character as a place holder for the parameter in the query because I found it MS Access doesn't know about the "@" character.

Here's the revised code:

            ' Create the SQL String for the text boxes.
            '------------------------------------------
            strSQL = "Select ID, FirstName, MiddleName, LastName, " & _
                           " Format(RingSizeLeft, ""#0.00"") AS [RingSizeLeft], " & _
                           " Format(RingSizeRight, ""#0.00"") AS [RingSizeRight] " & _
                       "From Customers " & _
                      "Where ID = ?"

            ' Create an instance of the data adapter and then fill with data.
            '----------------------------------------------------------------
            objDataAdapterCustomerDetails = _
                New OleDbDataAdapter(strSQL, objConnection)

            ' Add the parameter to the parameters collection of the Details Data Adapter.
            '----------------------------------------------------------------------------
            objDataAdapterCustomerDetails.SelectCommand.Parameters.Add("CustomerID", 0)

            ' Get query results into the Data Set.
            '-------------------------------------
            objDataAdapterCustomerDetails.Fill(objDataSetCustomerDetails, _
                                               "CustomerDetails")

Truly,
Emad

 

[emaduddeen]

Hi jmcilhinney,

Thanks for the reply and help.

That's what happens when I try to rush things.

Truly,
Emad