Parameters Question - How do I set up multiple parameters used in a MS Access query?

[emaduddeen]

Hi Everyone,

Can you look at my code and tell me what I'm doing wrong?

I get a "Failed to convert parameter value from a string to a int32" error.

I have a form with a "Search" text box and "Search" button that is supposed to load a data grid with data if the search is successful.

If the form load sub procedure is this code:

        ' Create the SQL String for the data grid.
        '-----------------------------------------
        strSQL = "Select FirstName + ' ' + LastName AS [Full Name], ID, " & _
                       " Format(PhoneCell, ""(###) 000-0000"") As [Cell Phone], " & _
                       " Format(PhoneHome, ""(###) 000-0000"") As [Home Phone], " & _
                       " Format(PhoneWork, ""(###) 000-0000"") As [Work Phone] " & _
                   "From Customers " & _
                  "Where ID Like ? Or FirstName Like ? " &
                  "Order By 1 "

            ' Create the connection object.
            '------------------------------
            objConnection = New OleDbConnection(FormMain.strDatabaseConnection)

            ' Create an instance of the data adapter.
            '----------------------------------------
            objDataAdapterCustomerNames = _
                New OleDbDataAdapter(strSQL, objConnection)

            ' Add the parameters to the parameters collection of the Data Adapter.
            '---------------------------------------------------------------------
            objDataAdapterCustomerNames.SelectCommand.Parameters _
               .Add("SearchCriteriaInt", SqlDbType.Int, 10).Value = 0

            objDataAdapterCustomerNames.SelectCommand.Parameters _
               .Add("SearchCriteriaString", SqlDbType.Text, 30).Value = ""

In the "Search" button click is this code:

            If IsNumeric(EditBoxSearch.Text) Then
                objDataAdapterCustomerNames.SelectCommand _
                        .Parameters("SearchCriteriaInt").Value = EditBoxSearch.Text
            Else
                objDataAdapterCustomerNames.SelectCommand _
                        .Parameters("SearchCriteriaString").Value = EditBoxSearch.Text
            End If

            ' Get query results into the Data Set.
            '-------------------------------------
            intTotalRowsFound = objDataAdapterCustomerNames.Fill(objDataSetCustomerNames, "Customers")

I get the error when the fill is executed.

Thanks.

Truly,
Emad

 

[jmcilhinney]

The problem is, as the error message suggests, that you're assigning data of the wrong type to the parameter's Value property. You specify that the parameter named "SearchCriteriaInt" is data type Int and then you proceed to assign a String to its Value. If it's data type is Int then you need to assign an Integer to it.

Apart from that, you can't use LIKE with anything other than text values, so your ID criterion can't work.

 

[emaduddeen]

Hi,

Ok, Thanks for the information.

Truly,
Emad