How to edit/update some selected rows from datagridview to database through VB.NET?

[ronelpisan]

I have a datagridview and loaded with records from a browsed database and combobox with items (different colors). The datagridview has 4 columns, the ID, FullName, Teamname, and last is the teamcolor. I want to update or edit the text in "teamcolor" column for each selected rows in datagridview. The code below can change the value of teamcolor in datagridview interface but it did not update actually in the database.

Below is my constructed code:

Dim ObjConnection As New OleDbConnection()
       Dim i As Integer

        viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value = ComboBox1.Text
       ObjConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & browserec.txtpath.Text & "'"
       Dim ObjCommand As New OleDbCommand()
       ObjCommand.Connection = ObjConnection
       For i = viewrecordsfrombackup.DataGridView1.SelectedRows.Count - 1 To 0 Step -1

           ObjCommand.CommandText = "UPDATE table1 SET teamcolor = '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value & "' where ID like '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("ID").Value & " '"
           ObjConnection.Open()
           ObjCommand.ExecuteNonQuery()
           ObjConnection.Close()

           Next
MsgBox("Record(s) updated successfully!")

[jmcilhinney]

Follow the Codebank link in my signature below and check out my thread on Retrieving & Saving Data for an example of using a data adapter, with or without a command builder, to retrieve data and save changes. In between, you should bind your DataTable to a BindingSource and then bind that to the grid. When you're ready to save, call Validate on the form and EndEdit on the BindingSource before calling Update on the data adapter.

[ChrisE]

I have a datagridview and loaded with records from a browsed database and combobox with items (different colors). The datagridview has 4 columns, the ID, FullName, Teamname, and last is the teamcolor. I want to update or edit the text in "teamcolor" column for each selected rows in datagridview. The code below can change the value of teamcolor in datagridview interface but it did not update actually in the database.

Below is my constructed code:

Dim ObjConnection As New OleDbConnection()
       Dim i As Integer

        viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value = ComboBox1.Text
       ObjConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & browserec.txtpath.Text & "'"
       Dim ObjCommand As New OleDbCommand()
       ObjCommand.Connection = ObjConnection
       For i = viewrecordsfrombackup.DataGridView1.SelectedRows.Count - 1 To 0 Step -1

           ObjCommand.CommandText = "UPDATE table1 SET teamcolor = '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value & "' where ID like '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("ID").Value & " '"
           ObjConnection.Open()
           ObjCommand.ExecuteNonQuery()
           ObjConnection.Close()

           Next
MsgBox("Record(s) updated successfully!")

I don't know if this is the cause, but check your code ....

 ObjCommand.CommandText = "UPDATE table1 SET teamcolor = '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("teamcolor").Value & "' where ID like '" & viewrecordsfrombackup.DataGridView1.SelectedRows(i).Cells("ID").Value & " '"
           ObjConnection.Open()
           ObjCommand.ExecuteNonQuery()
           ObjConnection.Close() '<------- here you close

           Next '<----- here you have Next, but you closed the connection