Microsoft Access Query problem

[SLMQC]

I am trying to create a form that allows the user to search (Access database) for a record by last name or customer number. It can be a partial last name or customer number.

The SQL statement and code are below. This is the same method used successfully for a SQL Server database, but it is not working with Access. The error message I am receiving is "Argument not specified for parameter 'Customer_Number' of 'Public Overridalbe Overloads Function Fill(dataTable As FindAccountDataSet.CustomerAccountsDataTable, Last_Name As String, Customer_Number As Integer) As Integer".

Can someone explain how to correct?

SELECT [Customer Number], [Last Name], [First Name], Address, City, State, [ZIP Code], [Telephone Number], [Account Balance], [Date Last Payment]
FROM CustomerAccounts
WHERE ([Last Name] LIKE ?) OR ([Customer Number] LIKE ?)

Note: I have tried using the astericks and percentage symbols as well.

    Private Sub btnSearchLastName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchLastName.Click
        'Search by last name
        Me.CustomerAccountsTableAdapter.Fill(Me.FindAccountDataSet.CustomerAccounts, txtLastName.Text)
    End Sub

    Private Sub btnSearchCustomerNum_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchCustomerNum.Click
        'Search by customer number
        Me.CustomerAccountsTableAdapter.Fill(Me.FindAccountDataSet.CustomerAccounts, txtAccountNum.Text)
    End Sub

[G_Waddell]

Hi,

The OLEDB connection to Access is not as sophisticated as the SQLDB to SQL server, basically it doesn't support named parameters so you must pass each parameter in in order. i.e. taking your query: SELECT [Customer Number], [Last Name], [First Name], Address, City, State, [ZIP Code], [Telephone Number], [Account Balance], [Date Last Payment] FROM CustomerAccounts WHERE ([Last Name] LIKE ?) OR ([Customer Number] LIKE ?)

You have to pass in two parameters in the order value to search against Last Name, value to search against Customer Number. So if you only want to search against one, the other must be blank.
With a SQL connection you can do this:

     dim cmd as SQLCommand
    ' establish connection string blah blah blah
    Cmd.text = "SELECT [Customer Number], [Last Name], [First Name], Address, City, State, [ZIP Code], [Telephone Number], [Account Balance], [Date Last Payment] " & _
     "FROM CustomerAccounts WHERE ([Last Name] LIKE @LastName ) OR ([Customer Number] LIKE @CustNumber)"
    cmd.Parmeters.add("@CustNumber", "123%')
    cmd.Parameters.add("@LastName", "Smith")
    'Just use a datareader for example
    MyDatareader =cmd.ExecuteReader()

Because we can specify parameter names with the SQL query we can put them in any order and we know the correct parameter is populated. FindAccountDataSet.CustomerAccounts with the SQL server connection is behaving as if it was passing in a blank string for whatever parameter is not specified.

Now with OLEDB to the Access it has to have both a parameter for Last Name and Customer Number passed in. Look at the Query you have to use - no named parameters so it puts the first parameter it receives in to the part where you search Last Name and then falls over because there is no second parameter hence "Argument not specified for parameter Customer_Number" error.

This Link explains about OLEDB Parameters