PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2010 [RESOLVED] Datagridview looping issue?-VBForums
Results 1 to 13 of 13

Thread: [RESOLVED] Datagridview looping issue?

  1. #1

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Resolved [RESOLVED] Datagridview looping issue?

    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)
    Code:
            
    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)
    Code:
    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
    Code:
    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

    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

    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?
    Last edited by coolwater; Nov 23rd, 2013 at 03:31 AM.

  2. #2
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    Re: Datagridview looping issue?

    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

    Code:
    For Each myRow in dgvDisbursements.Rows
        If Not myRow("isCleared").Value then Update Record
    Next
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  3. #3
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    Re: Datagridview looping issue?

    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
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  4. #4

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Datagridview looping issue?

    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.

  5. #5

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Datagridview looping issue?

    cboisCleared list items
    Code:
    0 = Uncleared
    1 = Cleared

    This is my code:

    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

  6. #6

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Datagridview looping issue?

    Those are comments. sorry

  7. #7
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    Re: Datagridview looping issue?

    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.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  8. #8

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Datagridview looping issue?

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

  9. #9
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    Re: Datagridview looping issue?

    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:
    Code:
    For Each myRow in dgvDisbursements.Rows
        If myRow("isCleared").Value then UpdateRecord(myRow)
    Next
    This is how you would declare the Sub:

    Code:
    Private Sub UpdateRecord(ByVal myRow as DataGridViewRow)
    And this is how it coud be used:
    Code:
    updateCommand.Parameters.AddWithValue("@DisbursementID", CLng(myRow.Cells("DisbursementID").Value))
    Last edited by kaliman79912; Nov 23rd, 2013 at 03:47 AM.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  10. #10

    Thread Starter
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    215

    Re: Datagridview looping issue?

    Quote Originally Posted by kaliman79912 View Post
    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:

    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
    Code:
        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

  11. #11
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    Re: [RESOLVED] Datagridview looping issue?

    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?
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  12. #12
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    Re: Datagridview looping issue?

    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.

    Code:
        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
    Last edited by kaliman79912; Nov 23rd, 2013 at 04:20 AM.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  13. #13
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,230

    Re: [RESOLVED] Datagridview looping issue?

    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:-

    vb.net Code:
    1. Public Class Form1
    2.   Private sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    3.   Private daDisbursements As New SqlDataAdapter("Select * From tblDisbursements", sqlConn)
    4.   Private cbDisbursements As New SqlCommandBuilder(daDisbursements)
    5.  
    6.   Private DT As New DataTable
    7.   Private bsDisbursements As New BindingSource
    8.  
    9.   Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    10.     daDisbursements.Fill(DT)
    11.     bsDisbursements.DataSource = DT
    12.     DataGridView1.DataSource = bsDisbursements
    13.   End Sub
    14. 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:-

    vb.net Code:
    1. Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    2.   bsDisbursements.EndEdit()
    3.   daDisbursements.Update(DT)
    4.  
    5.   MsgBox("All Edits Updated!")
    6. 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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width