Problems with DataGrid, DataSet, and DataAdapter combination

[nju0843]

I am using a DataSet with 3 tables which is bound to a DataGrid
I am trying to take data that is entered/changed in the DataGrid
which should automatically update the dataset. Then
call dataAdapter.Update(datasetname,"TableName")

I am trying to update my database using the dataadapter

Private Sub UpdateWholeBudgetTable()
		Dim conn As New MySqlConnection
		Dim myCommand As New MySqlCommand
		Dim NoOfRows As Integer
		Dim NoOfCols As Integer
		Dim rowNo As Integer
		Dim colNo As Integer
		Dim budgetDate As String
		Dim start_bal As Double
		Dim remaining_bal As Double
		Dim budget_id As Integer
		Dim adapter As New MySqlDataAdapter("SELECT * FROM db1.budget", conn)
		Dim commandBuilder As New MySqlCommandBuilder(adapter, True)
		Dim strUpdateCommand As String
		conn.ConnectionString = GetConnectionString()

		Try
			' After fixing errors, update the data source with the DataAdapter 
			' used to create the DataSet.

			conn.Open()
			Dim dsChanged As DataSet = myDataSet.GetChanges
			adapter.UpdateCommand = commandBuilder.GetUpdateCommand
		    adapter.UpdateCommand.Parameters.Add("?id", MySqlDbType.Int32)
		    adapter.UpdateCommand.Parameters.Add("?date", MySqlDbType.VarChar, 10)
		    adapter.UpdateCommand.Parameters.Add("?starting_balance", MySqlDbType.Float)
		    adapter.UpdateCommand.Parameters.Add("?amount_left", MySqlDbType.Float)
			adapter.UpdateCommand.Parameters(0).Value = 3
			adapter.UpdateCommand.Parameters(1).Value = "12/12/12"
			adapter.UpdateCommand.Parameters(2).Value = 21
			adapter.UpdateCommand.Parameters(3).Value = 31
			adapter.Update(dsChanged, "Budget")
			conn.Close()
		Catch myerror As MySqlException
		    MessageBox.Show("Error Connecting to Database: " & myerror.Message)
			adapter.Dispose()
			conn.Dispose()
		End Try
End Sub

I don't really know what I am doing wrong suggestions or comments on
my code will be helpful.

 

[jmcilhinney]

If you are using a CommandBuilder to update data that you have already edited then you don't need to assign the UpdateCommand property of the DataAdapter and add all the parameters. That's what the CommandBuilder does for you. Remove these lines:

adapter.UpdateCommand = commandBuilder.GetUpdateCommand
adapter.UpdateCommand.Parameters.Add("?id", MySqlDbType.Int32)
adapter.UpdateCommand.Parameters.Add("?date", MySqlDbType.VarChar, 10)
adapter.UpdateCommand.Parameters.Add("?starting_balance", MySqlDbType.Float)
adapter.UpdateCommand.Parameters.Add("?amount_left", MySqlDbType.Float)
adapter.UpdateCommand.Parameters(0).Value = 3
adapter.UpdateCommand.Parameters(1).Value = "12/12/12"
adapter.UpdateCommand.Parameters(2).Value = 21
adapter.UpdateCommand.Parameters(3).Value = 31

and you should find it works.

Plus, if you have already filled your DataSet using a DataAdapter then why are you creating another? Your data access objects (DataAdapter, CommandBuilder) should be class-level variables, not local to a method. You can even add them to the form in the designer instead of declaring them in your code. Ths gives you access to properties via the Properties window instead of doing everything in code.