Inserts to DataSet aren't added to db - updates work fine

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
Take out these two lines:

dr.AcceptChanges()
dsCust.AcceptChanges()

'This line:
daCustContact.Update(dsCust, "CustomerContact")
' accepts the changes for you!

timbobd said:
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
Click to expand...
 
Back
Top