[RESOLVED] Datagridview looping issue?

[coolwater]

I have this Datagridview that has a Checkbox column. I also have a button to update the database. What I want to accomplish is to loop all rows that has a checkbox column value will be submitted to the database for updates. The problem is it will only update 1 row at a time. It does not loop to all rows. I tried different techniques to no avail. The UpdateRecord() is just a simple code to update a Boolean field to true or false.


I also have a combobox with list items (Uncleared, Cleared)


Version(1)

        
Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

Dim counter As Integer = 0



        If Me.cboisCleared.SelectedIndex = 0 Then  -- (0 = Uncleared) code to update all uncleared to true.
            For counter = 1 To dgvDisbursements.RowCount
                If dgvDisbursements.CurrentRow.Cells("isCleared").Value = True Then
                    UpdateRecord()
                End If
            Next
        Else (1 = Cleared) --code to update all cleared to false.
            For counter = 1 To dgvDisbursements.RowCount
                If dgvDisbursements.CurrentRow.Cells("isCleared").Value = False Then
                    UpdateRecord()
                End If
            Next
        End If

end Sub

Version(2)

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

        If Me.cboisCleared.SelectedIndex = 0 Then --(0 = Uncleared) code to update all uncleared to true.
            For Each row As DataGridViewRow In dgvDisbursements.Rows
                If row.Cells("isCleared").Value = True Then
                    UpdateRecord()
                End If
            Next
        Else --(1 = Cleared) code to update all cleared to False.
            For Each row As DataGridViewRow In dgvDisbursements.Rows
                If row.Cells("isCleared").Value = False Then
                    UpdateRecord()
                End If
            Next
        End If

End Sub

Version 3

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

        If cboisCleared.SelectedIndex = 0 Then  --(0 = Uncleared) code to update all uncleared to true.
            For Each row As DataGridViewRow In dgvDisbursements.Rows
                If Boolean.TryParse(row.Cells("isCleared").Value.ToString, isTrue) Then
                    If isTrue = True Then
                        UpdateRecord()
                    End If
                End If
            Next
        Else -- --(1 = Cleared) code to update all cleared to False.
            For Each row As DataGridViewRow In dgvDisbursements.Rows
                If Boolean.TryParse(row.Cells("isCleared").Value.ToString, isTrue) Then
                    If isTrue = False Then
                        UpdateRecord()
                    End If
                End If
            Next
        End If

End sub

    Private Sub UpdateRecord()


        Dim CS As String = ConfigurationManager.ConnectionStrings("SimpleAccounting.My.MySettings.SimpleAcctgConnectionString").ConnectionString
        Dim connection As SqlConnection = New SqlConnection(CS)
        Dim updateStatment As String = "sproc_tblDisbursements_isCleared_DateCleared_Update"
        Dim updateCommand As New SqlCommand(updateStatment, connection)
        updateCommand.CommandType = CommandType.StoredProcedure
        updateCommand.Parameters.AddWithValue("@DisbursementID", CLng(dgvDisbursements.CurrentRow.Cells("DisbursementID").Value))
        updateCommand.Parameters.AddWithValue("@isCleared", CBool(dgvDisbursements.CurrentRow.Cells("isCleared").Value))
        updateCommand.Parameters.AddWithValue("@DateCleared", CDate(dgvDisbursements.CurrentRow.Cells("DateCleared").Value))
        updateCommand.Parameters.AddWithValue("@UserID", g_UserID)
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
        Catch ex As SqlException
            Throw ex
        Finally
            connection.Close()
        End Try

    End Sub

I want to loop to all records and update the database. All of the above codes will only update 1 row at a time. What am I doing wrong?

[kaliman79912]

OK, I am tired and may not go through all your code. In the first sub. You create a loop with counter from 1 to number of rows. But you never change row and try to check the value of CurrentRow.Cells("isCleared").Value. This is always only checking one row, not all the rows. Try something like

For Each myRow in dgvDisbursements.Rows
    If Not myRow("isCleared").Value then Update Record
Next

[kaliman79912]

Now I see you did something a bit like that on the second. I have a question... is that (0=Uncleared) on your code or are those comments in your post?
You may need to show us what UpdateRecord does

[coolwater]

Tnx kaliman79912 for replying. I've updated my post above. What I meant was I want to loop to the datagridview and check if the checkbox column is true or false and update the database. Right now my code will only update 1 row at a time instead of looping to all rows.

[coolwater]

cboisCleared list items

0 = Uncleared
1 = Cleared

This is my code:

    Private Sub UpdateRecord()


        Dim CS As String = ConfigurationManager.ConnectionStrings("SimpleAccounting.My.MySettings.SimpleAcctgConnectionString").ConnectionString
        Dim connection As SqlConnection = New SqlConnection(CS)
        Dim updateStatment As String = "sproc_tblDisbursements_isCleared_DateCleared_Update"
        Dim updateCommand As New SqlCommand(updateStatment, connection)
        updateCommand.CommandType = CommandType.StoredProcedure
        updateCommand.Parameters.AddWithValue("@DisbursementID", CLng(dgvDisbursements.CurrentRow.Cells("DisbursementID").Value))
        updateCommand.Parameters.AddWithValue("@isCleared", CBool(dgvDisbursements.CurrentRow.Cells("isCleared").Value))
        updateCommand.Parameters.AddWithValue("@DateCleared", CDate(dgvDisbursements.CurrentRow.Cells("DateCleared").Value))
        updateCommand.Parameters.AddWithValue("@UserID", g_UserID)
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
        Catch ex As SqlException
            Throw ex
        Finally
            connection.Close()
        End Try

    End Sub

[coolwater]

Those are comments. sorry

[kaliman79912]

There you go, the problem is not in the loops themselves but in UpdateRecord(). You are trying to update CurrentRow, but you never change current row. Either pass the row as a parameter to UpdateRecord, or the index. But looping through the rows in a For Each loop does not change the Current Row.

[coolwater]

Thaks for replying Kaliman79912. Ok I think I got what you mean. I'll try that.

[kaliman79912]

I do not have my development computer with me right now so this could be a bit off.

this would be part of your loop:

For Each myRow in dgvDisbursements.Rows
    If myRow("isCleared").Value then UpdateRecord(myRow)
Next

This is how you would declare the Sub:

Private Sub UpdateRecord(ByVal myRow as DataGridViewRow)

And this is how it coud be used:

updateCommand.Parameters.AddWithValue("@DisbursementID", CLng(myRow.Cells("DisbursementID").Value))

[coolwater]

There you go, the problem is not in the loops themselves but in UpdateRecord(). You are trying to update CurrentRow, but you never change current row. Either pass the row as a parameter to UpdateRecord, or the index. But looping through the rows in a For Each loop does not change the Current Row.

Thanks kaliman79912 for pointing me the solution.

Updated code:

        Dim isTrue As Boolean

        If cboisCleared.SelectedIndex = 0 Then
            For Each row As DataGridViewRow In dgvDisbursements.Rows
                If Boolean.TryParse(row.Cells("isCleared").Value.ToString, isTrue) Then
                    If isTrue = True Then
                        UpdateRecord(row.Cells("DisbursementID").Value, row.Cells("isCleared").Value, row.Cells("DateCleared").Value, g_UserID)
                    End If
                End If
            Next
        Else
            For Each row As DataGridViewRow In dgvDisbursements.Rows
                If Boolean.TryParse(row.Cells("isCleared").Value.ToString, isTrue) Then
                    If isTrue = False Then
                        UpdateRecord(row.Cells("DisbursementID").Value, row.Cells("isCleared").Value, row.Cells("DateCleared").Value, g_UserID)
                    End If
                End If
            Next
        End If


        If isLoaded = True Then
            LoadData(CLng(g_CompanyID), Me.cboisCleared.SelectedIndex, CLng(Me.cboSLID.SelectedValue))
        End If

    Private Sub UpdateRecord(ByVal DisbursementID As Long, ByVal isCleared As Boolean, ByVal DateCleared As Date, ByVal UserID As Long)


        Dim CS As String = ConfigurationManager.ConnectionStrings("SimpleAccounting.My.MySettings.SimpleAcctgConnectionString").ConnectionString
        Dim connection As SqlConnection = New SqlConnection(CS)
        Dim updateStatment As String = "sproc_tblDisbursements_isCleared_DateCleared_Update"
        Dim updateCommand As New SqlCommand(updateStatment, connection)
        updateCommand.CommandType = CommandType.StoredProcedure
        updateCommand.Parameters.AddWithValue("@DisbursementID", CLng(DisbursementID))
        updateCommand.Parameters.AddWithValue("@isCleared", CBool(isCleared))
        updateCommand.Parameters.AddWithValue("@DateCleared", CDate(DateCleared))
        updateCommand.Parameters.AddWithValue("@UserID", CLng(UserID))
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
        Catch ex As SqlException
            Throw ex
        Finally
            connection.Close()
        End Try

    End Sub

[kaliman79912]

You can actually pass the whole row as posted. but both could work. I am not familiar with parameters in stored procedures but besides that, all looks good. Does it work now?

[kaliman79912]

If the column is really a Checkbox column, it is already Boolean. No need to TryParse it. Here is my version of the cmd code. Hope it helps.

    Private Sub cmdUpdate_Click(sender As System.Object, e As System.EventArgs) Handles cmdUpdate.Click

        For Each myRow As DataGridViewRow In dgvDisbursements.Rows
            If myRow.Cells("isCleared").Value = (cboIsCleared.SelectedIndex = 0) Then
                UpdateRecord(myRow.Cells("DisbursementID").Value,
                             myRow.Cells("isCleared").Value,
                             myRow.Cells("DateCleared").Value,
                             g_UserID)
            End If
        Next

        If isLoaded Then LoadData(CLng(g_CompanyID), cboIsCleared.SelectedIndex, CLng(cboSLID.SelectedValue))

    End Sub

[IanRyder]

Hi Guys/Girls,

Jut so that you know you are making life difficult for yourselves. All this can easily be achieved by using a DataAdapter rather that looping through all the rows in a DataGridView that have been changed and then persisting the changes in those rows back to the Database one row at a time.

If you use a DataAdapter to load the Data from the Database in the first place, you can then attach that information to a DataGridView via a BindingSource to the local DataTable. You can then use a CommandBuilder to generate the Action Statements for the DataAdapter so that after you have made changes to the DataGridView you can persist all those changes back to the External Database by calling the Update Method of the DataAdapter. Have a look at this:-

Create your Objects and load your Data into a DataGridView:-

Public Class Form1
  Private sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  Private daDisbursements As New SqlDataAdapter("Select * From tblDisbursements", sqlConn)
  Private cbDisbursements As New SqlCommandBuilder(daDisbursements)
 
  Private DT As New DataTable
  Private bsDisbursements As New BindingSource
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    daDisbursements.Fill(DT)
    bsDisbursements.DataSource = DT
    DataGridView1.DataSource = bsDisbursements
  End Sub
End Class

Now make whatever changes you want to the DataGridView (via the code in your last thread) and then update the Database. i.e:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  bsDisbursements.EndEdit()
  daDisbursements.Update(DT)
 
  MsgBox("All Edits Updated!")
End Sub

That's it. No loops and everything saved back to the Database in one go.

Hope that helps.

Cheers,

Ian

BTW, this method requires that you have a PrimaryKey field defined in the Table in the Database but I am guessing that you already have this with the DisbursementID Field.