Data not passed from DGCV to DataSet

[DaveP]

I thought that my database was not being updated but after some testing I find that infact my DataSet is not being updated by the DGV via a bindingsource
This code sucessfully opens a database and displays the data in a DGV, but if I edit a cell in the grid and click on the next row it does not update the dataset
Please put me out of my misery.

Friend CustomerName As String
Friend CompanyID As Integer
Friend objConnection As OleDbConnection
Friend daCust As OleDbDataAdapter
Friend Customers As DataSet
Friend CmdBuilder As OleDbCommandBuilder
Friend strSQL As String

Friend Sub LoadDatabase()
Dim CustTable As String = "Companies"
'fill a dataset from the database
Dim cnCust As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Cust.mdb
objConnection = New OleDbConnection(cnCust)
strSQL = "Select * from " & CustTable & " ORDER BY Company"
daCust = New OleDbDataAdapter(strSQL, cnCust)
Customers = New DataSet()
daCust.Fill(Customers, "Companies")
BindingSource1.DataSource = Customers
BindingSource1.DataMember = "Companies"
'display dataset in datagridview
DataGridView1.DataSource = BindingSource1
End Sub

I tried this code to end the edit and update the DataSet, but found no change. Do I need to iterate through the DGV row by row to update the dataset?

Private Sub btnTest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTest.Click
Dim mystring As String
BindingSource1.EndEdit()
daCust.Update(BindingSource1.DataSource)
'examine contents of changed cell
mystring = Customers.Tables("Companies").Rows(1).Item(0).ToString
End Sub


I also tried without a binding source using this code

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
'update database with changes
'this does not work - why?
objConnection.Open()
daCust.UpdateCommand = objConnection.CreateCommand
daCust.UpdateCommand.CommandText = "SELECT * FROM Companies"
daCust.UpdateCommand.ExecuteNonQuery()
objConnection.Close()
End Sub

 

[Paszt]

Your main problem is not having a correct updatecommand with correct parameters. Personally I would use the available data wizards to do this without writing code. Here's an old article that I quickly found; as it is old, the screenshots may not be exact, but the idea is the same: Using the DataGridView (with no code).

If your interest is learning the correct methods for coding, you can view the code generated by the wizard and inspect the methods used.

'this does not work - why?
daCust.UpdateCommand.CommandText = "SELECT * FROM Companies"

Your updatecommand is a select command and not an update command. An SQL update command will include the term "UPDATE".