Problem updating mySQL table usign information in DataGrid

[nju0843]

Hello All,

I am having trouble updating the table in my database that corresponds
to my datagrid. I am able to change the first row of my datagrid but if
I change another row in the datagrid before clicking update then
only the first row gets updated and nothing else does.

This routine is in a called from an 'Update' button:

	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

		conn.ConnectionString = GetConnectionString()
		Try
			rowNo = 0
			colNo = 0
			NoOfRows = DataGrid2.VisibleRowCount()
			NoOfCols = DataGrid2.VisibleColumnCount()
			For rowNo = 0 To NoOfRows - 2
				budget_id = DataGrid2.Item(rowNo, 0)
				budgetDate = DataGrid2.Item(rowNo, 1)
				start_bal = DataGrid2.Item(rowNo, 2)
			    remaining_bal = DataGrid2.Item(rowNo, 3)
				conn.Open()
				myCommand.Connection = conn
			    myCommand.Parameters.Add("?id", budget_id)
			    myCommand.Parameters.Add("?date", budgetDate)
			    myCommand.Parameters.Add("?starting_balance", start_bal)
			    myCommand.Parameters.Add("?amount_left", remaining_bal)
			    myCommand.CommandText = "UPDATE db1.budget SET date = ?date, starting_balance = ?starting_balance, amount_left = ?amount_left WHERE (id = ?id);"
				myCommand.ExecuteNonQuery()
				conn.Close()
			Next

		Catch myerror As MySqlException
		    MessageBox.Show("Error Connecting to Database: " & myerror.Message)
			conn.Dispose()
		End Try
	End Sub

 

[nju0843]

Call myCommand.Parameters.Clear() after ExecuteNonQuery

Call myCommand.Parameters.Clear() after ExecuteNonQuery

 

[jmcilhinney]

To update multiple rows you use a DataAdapter and call Update. You do not update one row at a time using a Command and calling ExecuteNonQuery. You should do a little reading about the DataAdapter class that corresponds to the other data access objects you are using, presumably MySqlDataAdapter.

 

[nju0843]

Thanks for showing me that!
My co-worker showed me that today... and I forgot exactly
what function he showed me and that was it.

Thanks!