G
Guest
Here is a newbie question, but this one is driving me crazy.
In a VB.NET Windows form application, 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
In a VB.NET Windows form application, 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