Hi Everyone,

Can you look at this code because I am sure I'm missing something. I am trying to reset a Dataset.

I have a form that has 1 text box and has 2 queries that locate the data. One is by customer ID and the other is by customer last name. They both work but when the code works and the user enters a customer ID and the query locates it then enters a customer last name it also locates it. If the user enters the same customer id again the query seems to fail. This is why I'm sure I need to add some kind of code to reset the Dataset.

Can you tell me what I'm missing?

Thanks.

Truly,
Emad

Here's the code in the load sub procedure:

EditBoxSearch.Focus()

        ' Start out clean.
        '-----------------
        LightGridCustomerNames.Rows.Clear()

        ' Create the SQL String for the data grid.
        '-----------------------------------------
        strSQLById = _
            "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 = ? " & _
              "Order By 1 "

        strSQLByLastName = _
            "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 LastName Like ? " & _
             "Order By 1 "

        ' Set up the exception catch.
        '----------------------------
        Try
            ' Create the connection object.
            '------------------------------
            objConnection = New OleDbConnection(FormMain.strDatabaseConnection)

            ' Create a DataAdapter to load the Customer table with ID results.
            '-----------------------------------------------------------------
            objDataAdapterCustomerNamesByID = _
                New OleDbDataAdapter(strSQLById, objConnection)

            ' Create a parameter for this table.
            '-----------------------------------
            objDataAdapterCustomerNamesByID.SelectCommand.Parameters _
               .Add(New OleDb.OleDbParameter("SearchCriteriaCustID", ""))

            ' Create a DataAdapter to load the Customer table with Last Name results.
            '------------------------------------------------------------------------
            objDataAdapterCustomerNamesByLastName = _
                New OleDbDataAdapter(strSQLByLastName, objConnection)

            ' Create a parameter for this table.
            '-----------------------------------
            objDataAdapterCustomerNamesByLastName.SelectCommand.Parameters _
               .Add(New OleDb.OleDbParameter("SearchCriteriaLastName", ""))

        Catch exSqlErrors As OleDbException

            MessageBox.Show("Sorry, I can't display your data " & _
                            "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                            "Other Error")

        Catch exErrors As Exception
            MessageBox.Show("Sorry, I can't display your data " & _
                            "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                            "Other Error")
        End Try

This is in a search button click sub procedure:

Dim intTotalRowsFound As Integer = 0

        ' Start out clean.
        '-----------------
        LightGridCustomerNames.Rows.Clear()

        ' Set up the exception catch.
        '----------------------------
        Try

            ' Get query results into the Data Set by trying all possible parameters.
            ' It will try:
            ' Customer ID, Customer last name, cell phone, work phone, home phone.
            '
            ' When all of these fail, the user can add a new customer.
            '-----------------------------------------------------------------------
            objDataAdapterCustomerNamesByID.SelectCommand _
               .Parameters("SearchCriteriaCustID").Value = EditBoxSearch.Text

            If objDataAdapterCustomerNamesByID.Fill(objDataSetCustomerNames, _
                                               "CustomersByID") = 0 Then

                objDataSetCustomerNames.Reset()

                objDataAdapterCustomerNamesByLastName.SelectCommand() _
                   .Parameters("SearchCriteriaLastName").Value = EditBoxSearch.Text

                If objDataAdapterCustomerNamesByLastName _
                    .Fill(objDataSetCustomerNames, _
                        "CustomersByLastName") = 0 Then

                    objDataSetCustomerNames.Reset()
                End If
            End If

            ' Wake up the Data Grid to show the data.
            '----------------------------------------
            With LightGridCustomerNames
                .ClearSort(0)
                .Columns(0).Filter = ""
                .DataSource = objDataSetCustomerNames
            End With

        Catch exSqlErrors As OleDbException

            MessageBox.Show("Sorry, I can't display your data " & _
                            "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                            "Other Error")

        Catch exErrors As Exception
            MessageBox.Show("Sorry, I can't display your data " & _
                            "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                            "Other Error")
        End Try

Hi Emad. You tell us the query "seems to fail", but you don't tell us how. Is an exception thrown? Or is the data not being returned as you expected?

Here's a guess though: If your customer names datatable is strongly typed (after using a wizard?), it probably has a unique key constraint on customerID, and so will throw an exception if you try to fill it with the same customer record twice. Try this:

' Start out clean.        
'-----------------
objDataSetCustomerNames.Clear()