OleDbCommandBuilder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear all

I hope some one can help me with this.

I have been trying for ages to update a disconnected dataset and then write
the changes back to the SQL database. However when I make changes to the
relivant fields it updates the datagrid but does not write the edits back to
the main SQL database.

This is a summary of the code I am using, as you will see there is alot of
code commented out as I have been trying all sorts of things to make it work.
Any bodies help would be grafully accepted.

**********************************************************
Private Sub cmdEdit1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles cmdEdit1.Click
'this edits to contact details
Dim dr As DataRow
Dim cmdBuilder As New OleDbCommandBuilder(daContacts)
Dim tbItem As Object
Dim obj As Object
Dim strID As String
Dim counter As Integer

If txtContactname.ReadOnly = True Then

obj = Me.contactPanel.Controls()
For Each tbItem In obj
If tbItem.GetType() Is GetType(System.Windows.Forms.TextBox)
Then
tbItem.ReadOnly = False 'turns off read only
End If
Next
cmdAdd1.Enabled = False
cmdDelete1.Enabled = False
cmdEdit1.Text = "Save"
txtContactname.Focus() 'set the focus to ID

Else
daContacts.UpdateCommand = cmdBuilder.GetUpdateCommand
'daContacts.UpdateCommand.CommandText = "UPDATE Contacts " _
' & "SET Contact_Name =
txtContactname.Text, " _
' & "Contact_Mobile =
txtContactMobile.Text, " _
' & "Contact_Designation =
txtContactTitle.Text, " _
' & "Contact_email =
txtContactEMail.Text " _
' & "WHERE (Contact_ID =
txtContactID.text"

strID = txtContactID.Text

'now we need to delete all the contacts within the company we
have deleted
counter = 0
For Each dr In dsCompany.Tables("Contacts").Rows
'Debug.WriteLine(Trim(dr(0)))
Console.WriteLine(dr("Contact_ID"))
If Trim(dr("Contact_ID")) = Trim(strID) Then
With dsCompany.Tables("Contacts")
'dr(("Contact_Name")) = txtContactname.Text
'Console.WriteLine(dr(("Contact_ID")) & " - " &
txtContactname.Text)
'dr(("Contact_Mobile")) = txtContactMobile.Text
'dr(("Contact_Designation")) = txtContactTitle.Text
'dr(("Contact_email")) = txtContactEMail.Text
'With dsCompany.Tables("Contacts")
Console.WriteLine(.Rows(counter).RowState())
.Rows(counter)("Contact_Name") = txtContactname.Text
Console.WriteLine(.Rows(counter)("Contact_ID") & " -
" & txtContactname.Text)
.Rows(counter)("Contact_Mobile") =
txtContactMobile.Text
.Rows(counter)("Contact_Designation") =
txtContactTitle.Text
.Rows(counter)("Contact_email") = txtContactEMail.Text
Console.WriteLine(.Rows(counter).RowState())

daContacts.Update(dsCompany, "Contacts")
dsCompany.AcceptChanges()
Console.WriteLine(.Rows(counter).RowState())
End With
End If
counter = counter + 1
Next


'da = Nothing
cmdAdd1.Enabled = True
cmdDelete1.Enabled = True
cmdEdit1.Text = "Edit"

SetReadOnly(Me.contactPanel.Controls)
dgContacts.Refresh()

End If
End Sub

**********************************************************

It's sub Edit1 that I have been playing with. The Add and Deletes work
fine, it's just the Edit's.

Thanks in advance




Dave
 
Dave,

Probably you make it yourself much to dificult.
You only need to use the commandbuilder for a command when the first fill is
done when you have instanced your dataadapter global.

Probably than you can do

\\\
Private Sub cmdEdit1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles cmdEdit1.Click
'The next row for testing, I would replace it to a place where it is less
done.
Dim cmdBuilder As New OleDbCommandBuilder(daContacts)
BindingContext(dsCompany.Tables("Contacts")).EndCurrentEdit()
daContacts.Update(dsCompany, "Contacts")
'The acceptchanges is done by the dataadaper
End Sub
///
I hope this helps

Cor
 
Back
Top