MS Access - Dataset - How to you reset a Dataset?

[emaduddeen]

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

[crapulency]

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()