Member Avatar for Ehtesham Siddiq

Hi,
My application is in VS2008 coded in Vb.net.I have a datagridview which is populated from Database.I have a save button on the same form that has DatagridView.
My requirement is i want that all the records that are populated in DatagridView should be saved in a certain table of the database when the user clicks save.
Im done with my save and Update code.What im not able to do is,im not able to increment the counter and move to the next row of datagridView when my For loop is executing after the click of Save button.
Below is my Code.

For i As Integer = 0 To DGVStudRecord.RowCount - 1
My Code for Update and Save.
Next

Here what is happening is when save button is clicked my for Loop is executing and looping till RowCount-1.But i want that after completion of each loop my i should get to the next row in the datagirdView.I should be able to get the values of next row to pass it to my controls that are carrying update and delete operation.
The entire data in the datagridview will be saved in a certain table at the click of save button.
Please suggest.

  • 3 Contributors
  • 4 Replies
  • 350 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Ehtesham Siddiq

Dani AI

Generated

Nice catch, — the behaviour you saw (the loop runs but the same row keeps being read) comes from using the DataGridView’s focused cell. CurrentCell always points to the active cell, so your loop kept reading that one row. Use the loop variable or iterate the grid’s Rows collection to address each row explicitly.

A practical, safe pattern is: open the DB connection once, prepare a parameterized command, then iterate rows and set parameters per row. This avoids string concatenation, repeated opens, and SQL injection. Example (VB.NET):

Using con As New SqlConnection(connString)
  con.Open()
  Dim tran As SqlTransaction = con.BeginTransaction()
  Using cmd As New SqlCommand("UPDATE TableName SET Column1=@c1, Column2=@c2 WHERE No=@no", con, tran)
    cmd.Parameters.Add("@c1", SqlDbType.VarChar, 200)
    cmd.Parameters.Add("@c2", SqlDbType.VarChar, 200)
    cmd.Parameters.Add("@no", SqlDbType.Int)
    For Each r As DataGridViewRow In DGVStudRecord.Rows
      If r.IsNewRow Then Continue For
      Dim v1 = r.Cells("Column1").Value
      Dim v2 = r.Cells("Column2").Value
      Dim id = r.Cells("No").Value
      cmd.Parameters("@c1").Value = If(v1 Is Nothing OrElse IsDBNull(v1), DBNull.Value, v1)
      cmd.Parameters("@c2").Value = If(v2 Is Nothing OrElse IsDBNull(v2), DBNull.Value, v2)
      cmd.Parameters("@no").Value = Convert.ToInt32(id)
      cmd.ExecuteNonQuery()
    Next
  End Using
  tran.Commit()
End Using

As suggested, an even cleaner approach is databinding: bind the grid to a DataTable (or DataSet) and use a SqlDataAdapter with properly configured Insert/Update/Delete commands (or a SqlCommandBuilder) and call adapter.Update(myTable) — this automatically persists only changed rows and preserves typed data.

Quick tips: skip the editable “new row” (IsNewRow), validate/convert DBNulls before assigning parameters, avoid executing a reader while trying to update (close it first), and prefer a single open connection or a transaction for many rows. If performance matters for large batches, consider batching strategies (DataAdapter updates, stored procedures, or table-valued parameters).

Recommended Answers

All 4 Replies

Member Avatar for Oxiegen

If the table that you want to save the data to is the same table that you use to populate the DataGridView, then I suggest that you use databinding to bind the DataGridView to a DataTable.
You can find several solutions for databinding right here on DaniWeb.

Every time that you make a change in the DataGridView, the DataTable will be updated to match those changes.
When you click the Save button, you can push those changes from the DataTable onto the database.

Member Avatar for adam_k

If your loop is working, perhaps your problem is not the loop, but the way you read data from your datagridview.
Please post what is inside the loop.

Member Avatar for Ehtesham Siddiq

Below is my actual Code.
I want the cursor to move to next row and provide respective colum values to my update query.

For i As Integer = 0 To DGVStudRecord.RowCount - 1
           
            DBConnect()
            Dim strsql1 As String = ""
            con = DBActions.DBConnect
            strsql1 = "Select No from TableName where No='" & DGVStudRecord.Item(0, DGVStudRecord.CurrentCell.RowIndex).Value & "'"
            cmd = New SqlCommand(strsql1, con)
            dr = cmd.ExecuteReader
            If dr.HasRows = True Then
               
                dr.Close()
                strsql = "Update TableName set No='" & DGVStudRecord.Item(1, DGVStudRecord.CurrentCell.RowIndex).Value & "'," & _
                      "Column1='" & DGVStudRecord.Item(2, DGVStudRecord.CurrentCell.RowIndex).Value & "'," & _
                      "Column2='" & DGVStudRecord.Item(3, DGVStudRecord.CurrentCell.RowIndex).Value & "'," & _
                      "Column3='" & DGVStudRecord.Item(4, DGVStudRecord.CurrentCell.RowIndex).Value & "'," & _
                      "Column4='" & DGVStudRecord.Item(6, DGVStudRecord.CurrentCell.RowIndex).Value & "'," & _
                    
                "where No='" & DGVStudRecord.Item(0, DGVStudRecord.CurrentCell.RowIndex).Value & "'"
                cmd = New SqlCommand(strsql, con)
                cmd.ExecuteNonQuery()
                dr.Close()

Please dont look for code it may contain error as i have truncated it.Just tell me how can i get the new row values to give it to query parameter to update.

Member Avatar for Ehtesham Siddiq

Hi frnds,
got the solution.
instead of using DGVStudRecord.Item(0, DGVStudRecord.CurrentCell.RowIndex).Value
I was missing this simple logic
im supposed to use.

DGVStudRecord.Item(0, i).Value
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.