VB Forum / Database Access / September 2005

Here is another newbie question, but this one is driving me crazy.

I am inserting and updating records in a DataSet, which show up in a bound
DataGrid just fine. I am also issuing a DataAdapter.Update command, and the
updates are saved to the database but the inserts are not. As far as I can
tell, I am following the two examples that I have to the tee, but it just
doesn't work. I'm sure that I'm overlooking some simple detail. FYI I am
using an OleDBCommandBuilder to build the insert and update commands.

I moved all of the relevant code into a single form to create a simplified
example, and it follows. Thanks in advance!!!!

----------------------------------------------------------
' this project has a single window with a bound DataGrid that
'displays a single user's "contact information", which is one row
'
'clicking on btnUpdate updates that one record
'clicking on btnInsert inserts a new row
'both updates and inserts are immediately visible in the DataGrid
'but only the updates are saved in the database

Public Class Form1
   Inherits System.Windows.Forms.Form

   Private oleConnection As OleDbConnection
   Private Shared connString As String
   Private sql As String
   Private daCustContact As New OleDbDataAdapter
   Private dsCust As DataSet
   Private cmdCustBuilder As OleDbCommandBuilder
   Private cmdCustContact As New OleDbCommand
   Private cmdCustContactBuilder As OleDbCommandBuilder

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

       connString = " .. my conncetion string .."

       oleConnection = New OleDbConnection(connString)
       oleConnection.Open()
       dsCust = New DataSet("CustomerContact")

       cmdCustContact.Connection = oleConnection
       cmdCustContact.CommandText = "SELECT CustomerNumber, ContactAddress,
MethodType, SequenceNumber, InactiveDate " _
   & "FROM CustomerContact " _
   & " WHERE CustomerNumber = 526"
       daCustContact.SelectCommand = cmdCustContact
       daCustContact.TableMappings.Add("Table", "CustomerContact")

       cmdCustContactBuilder = New OleDbCommandBuilder(daCustContact)
       cmdCustContactBuilder.QuotePrefix = "["
       cmdCustContactBuilder.QuoteSuffix = "]"
       daCustContact.UpdateCommand = cmdCustContactBuilder.GetUpdateCommand
       daCustContact.InsertCommand = cmdCustContactBuilder.GetInsertCommand
       daCustContact.DeleteCommand = cmdCustContactBuilder.GetDeleteCommand
       daCustContact.Fill(dsCust, "CustomerContact")

       DataGrid1.SetDataBinding(dsCust, "CustomerContact")

   End Sub

   Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
       With dsCust.Tables("CustomerContact").Rows(0)
           .Item("MethodType") = "Fax"
           .Item("SequenceNumber") = Convert.ToByte(10)
           .Item("ContactAddress") = .Item("ContactAddress") & "xxx"
       End With
       UpdateCustomerContact()
   End Sub

   Public Sub UpdateCustomerContact()
       BindingContext(dsCust.Tables("CustomerContact")).EndCurrentEdit()

       daCustContact.Update(dsCust, "CustomerContact")  
   End Sub

   Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsert.Click
       Dim dtCc As DataTable = dsCust.Tables("CustomerContact")
       Dim dr As DataRow = dtCc.NewRow()

       dr("CustomerNumber") = 526
       dr("MethodType") = "Email"
       dr("SequenceNumber") = 2
       dr("ContactAddress") = "hacking code"
       dr("InactiveDate") = System.DBNull.Value

       Try
           dsCust.Tables("CustomerContact").Rows.Add(dr)
           dr.AcceptChanges()
           dsCust.AcceptChanges()

           UpdateCustomerContact()
       Catch ex As Exception
           MessageBox.Show("Exception occurred: " & ex.Message,
MessageBoxButtons.OK, MessageBoxIcon.Error)
       End Try

   End Sub

 Public Sub UpdateCustomerContact()
     BindingContext(dsCust.Tables("CustomerContact")).EndCurrentEdit()
     daCustContact.Update(dsCust, "CustomerContact")  
 End Sub

End Class