none
HOW TO UPDATE ONLY FILTERED RECORDS VISIBLE IN DATAGRIDVIEW IN VB.NET RRS feed

  • Question

  • Hi 

    Am writing lending program whereby clients apply for loans to be approved by management. I am able to filter records based on combo box item selection (PENDING, REJECTED, APPROVED). My problem is that when I filter "PENDING" records and click on Approve Button ONLY the first record's Approval column (7) is updated with "APPROVED", the rest are maintaining "PENDING". where did I go wrong? My code is attached.

    I need to update Approval column(7) for ALL records in the default filter view of datagridview whenever Approve button is clicked excluding the hidden (filtered out records). Someone out there kindly assist am stuck.

     Private Sub cmdApproval_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdApproval.Click
    
            Dim con As OleDbConnection = New OleDbConnection
            con.Close()
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\NOPAServer.accdb;Jet OLEDB:Database Password=ti$op"
            con.Open()
            For i As Integer = 0 To TblApplyLoansDataGridView.Rows.Count - 1
    
                Dim cmd As New OleDbCommand("Update tblApplyLoans Set Approval = '" & TblApplyLoansDataGridView.Rows(i).Cells(7).Value & "'where NID = '" & TblApplyLoansDataGridView.Rows(i).Cells(0).Value & "'", con)
                cmd.ExecuteNonQuery()
            Next
            MessageBox.Show("Approval Successfull!!!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
    
        End Sub

    Thanks in Advance

    Man TopCus

    • Edited by Man TopCus Sunday, July 8, 2018 2:58 PM
    Sunday, July 8, 2018 2:50 PM

Answers

  • Let's check what the type is for the BindingSource DataSource.

    Console.WriteLine(TblApplyLoansBindingSource.DataSource.GetType)
    BTW The reason I suggested to cast to a DataSet then a DataTable was because your error indicated a DataSet was in the mix e.g.

    Am getting an error: 

    Unable to cast object of type '_2L2S.NOPAServerDataSet' to type 'System.Data.DataTable'.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, July 9, 2018 1:41 PM
    Moderator

All replies

  • Rolling with a DataGridView.DataSource set to a DataTable or a DataSet.Table("some table") and not a DataGridView without its data source set.

    The DataTable would have a primary key field with ColumnMapping set to Hidden so it's not visible.

    The following we have dgv as a DataGridView, bs as a BindingSource.

    Dim rows As EnumerableRowCollection(Of DataRow) =
            CType(bs.DataSource, DataTable).AsEnumerable().Where(Function(row) row.Field(Of String)("SomeField") = "APPROVED")
    
    For Each row As DataRow In rows
        ' do your update
    Next


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, July 8, 2018 4:14 PM
    Moderator
  • Maybe you can remove the For loop and replace it with a single SQL statement: UPDATE tblApplyLoans SET Approval='APPROVED' WHERE Approval='PENDING'. Then refresh (reload) the grid.


    Sunday, July 8, 2018 7:13 PM
  • Hi

    You update the tblApplyLoans table, so you update all the data. I think you should add filtering criteria when updating your data.
    For example: bind.Filter = "Id>1"
               Update Table...where Id>1

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 9, 2018 2:46 AM
  • Hi Karen,

    I still have a problem, here is my table. There is no Primary key, NID is a foreign Key and it is a Text Field.

    NID

    AppDate

    LoanType

    LoanAmountReq

    ProcessingFee

    AvailableAmount

    PayableAmount

    Approval

    555555

    04-Jul-2018 3:38 PM

    PERSONAL LOAN

    58000

    1740

    56260

    58000

    PENDING

    666666

    04-Jul-2018 3:38 PM

    PERSONAL LOAN

    100000

    5000

    85000

    100000

    PENDING

    12345698

    04-Jul-2018 3:32 PM

    PERSONAL LOAN

    58000

    1740

    56260

    58000

    REJECTED

    23538307

    04-Jul-2018 3:33 PM

    PERSONAL LOAN

    78900

    2367

    76533

    78900

    PENDING

    123456789

    04-Jul-2018 3:38 PM

    PERSONAL LOAN

    80000

    800

    66000

    80000

    APPROVED

    Am getting an error: 

    Unable to cast object of type '_2L2S.NOPAServerDataSet' to type 'System.Data.DataTable'.

    Code

    Private Sub cmdApproval_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdApproval.Click

            Dim con As OleDbConnection = New OleDbConnection

            con.Close()

            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\NOPAServer.accdb;Jet OLEDB:Database Password=mo$op"

            con.Open()

            Dim rows As EnumerableRowCollection(Of DataRow) = CType(TblApplyLoansBindingSource.DataSource, DataTable).AsEnumerable().Where(Function(row) row.Field(Of String)("Approval") = "APPROVED") ' This is the line where I get the error quoted above

            For Each row As DataRow In rows

                Dim cmd As New OleDbCommand(" UPDATE tblApplyLoans SET Approval='APPROVED' WHERE Approval='PENDING'", con)

                TblApplyLoansDataGridView.Refresh()

                cmd.ExecuteNonQuery()

            Next

            MessageBox.Show("Approval Successfull!!!!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End Sub

    I filter data first before clicking "cmdApproval" in order to change and update only "PENDING" column for filtered data.

    Any Help shall be appreciated.

    Monday, July 9, 2018 9:49 AM
  • In the following replace "TODO" with either the table name (as a string) or the ordinal position of the table in the DataSet as an integer e.g. if this is the first table than use 0.

    CType(TblApplyLoansBindingSource.DataSource, DataSet).Tables("TODO").AsEnumerable().Where(Function(row) row.Field(Of String)("Approval") = "APPROVED")


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, July 9, 2018 9:57 AM
    Moderator
  • Hi Karen,

    it looks as if am making some progress with your code.  I think am close to getting it right but am facing some few hitches

    When i use my table name "tblApplyLoans" I get the error 'Tables' is not a member of systems.Data.DataTable'

    Code:

     Dim rows As EnumerableRowCollection(Of DataRow) = CType(TblApplyLoansBindingSource.DataSource, DataTable).Tables("tblApplyLoans").AsEnumerable().Where(Function(row) row.Field(Of String)("Approval") = "APPROVED")

    >>Blue wavy line is underlining the code in Bold

    Same happens when I use the Ordinal Position of table in my dataset which is 0 integer (The firstTable)

    Code:

    Dim rows As EnumerableRowCollection(Of DataRow) = CType(TblApplyLoansBindingSource.DataSource, DataTable).Tables(0).AsEnumerable().Where(Function(row) row.Field(Of String)("Approval") = "APPROVED")


    Monday, July 9, 2018 12:43 PM
  • Let's check what the type is for the BindingSource DataSource.

    Console.WriteLine(TblApplyLoansBindingSource.DataSource.GetType)
    BTW The reason I suggested to cast to a DataSet then a DataTable was because your error indicated a DataSet was in the mix e.g.

    Am getting an error: 

    Unable to cast object of type '_2L2S.NOPAServerDataSet' to type 'System.Data.DataTable'.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, July 9, 2018 1:41 PM
    Moderator
  • Thanks it works fine now
    Saturday, August 25, 2018 10:51 AM