.GetInsertCommond works ok but .GetUpdateCommand does not. Can you tell me why?

[emaduddeen]

Hi Everyone,

I'm using MS Access as the database and using a command builder.

.GetInsertCommand get the correct insert command text but .GetUpdateCommand gets the wrong command text. It even returns a primary key value of 1 which is not the actual value it should be.

In order to get my form to work, I had to comment the .GetUpdateCommand statement out and put extra lines of code to do it by hand. Is my program wrong where I'm missing something that .GetUpdateCommand needs?

Here is the code showing the commented out lines to get it to update.

All help will be appreciated.

' Create an instance of the command builder.
            '-------------------------------------------
            objCommandBuilderCustomerDetails = _
                New OleDbCommandBuilder(objDataAdapterCustomerDetails)

            If booleanAddNew Then

                ' Have the command builder create an Insert SQL command.
                '-------------------------------------------------------
                objDataAdapterCustomerDetails.InsertCommand = _
                    objCommandBuilderCustomerDetails.GetInsertCommand
            Else

                ' Have the command builder create an update SQL command.
                '-------------------------------------------------------
                '  objDataAdapterCustomerDetails.UpdateCommand = _
                ' objCommandBuilderCustomerDetails.GetUpdateCommand()

                objDataAdapterCustomerDetails.UpdateCommand = objConnection.CreateCommand

                objDataAdapterCustomerDetails.UpdateCommand.Parameters.Clear()

                objDataAdapterCustomerDetails.UpdateCommand.CommandText = _
                    "Update Customers " & _
                       "SET FirstName = ?, LastName = ?, Address1 = ?, " & _
                           "OtherID = ? " & _
                     "Where ID = ?"

                objDataAdapterCustomerDetails.UpdateCommand.Parameters. _
                    AddWithValue("FirstName", objDataRowCustomerDetails("FirstName"))
                objDataAdapterCustomerDetails.UpdateCommand.Parameters. _
                    AddWithValue("LastName", objDataRowCustomerDetails("LastName"))
                objDataAdapterCustomerDetails.UpdateCommand.Parameters. _
                    AddWithValue("Address1", objDataRowCustomerDetails("Address1"))
                objDataAdapterCustomerDetails.UpdateCommand.Parameters. _
                    AddWithValue("OtherID", objDataRowCustomerDetails("OtherID"))
                objDataAdapterCustomerDetails.UpdateCommand.Parameters. _
                    AddWithValue("ID", intCustomerID)
            End If

            ' Perform the update SQL command; then close the connection.
            '-----------------------------------------------------------
            objDataAdapterCustomerDetails.FillSchema(objDataSetCustomerDetails, SchemaType.Mapped)

            objDataAdapterCustomerDetails _
                .Update(objDataSetCustomerDetails, "Customer Details")

Truly,

Emad