Need to speed up record selection in a datagridview-VBForums
Results 1 to 6 of 6

Thread: Need to speed up record selection in a datagridview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Need to speed up record selection in a datagridview

    I have roughly 100,000 records in a datagridview that I am selecting records using a user input criteria in another search form. But its taking way to long to loop through each record and select them. Is there a faster way of doing this? Here is my code:

    Code:
    For Each strLine As String In TextBox2.Text.Split(vbNewLine)
            Dim SearchFor As String = strLine.ToString
            If SearchFor = vbLf & "AND" Or SearchFor = vbLf & "OR" Then
            Else
                If EquationVariable = "=" Then
                    ItemToFind = SearchFor.Substring(SearchFor.LastIndexOf("=") + 1).TrimStart
                    For Each R0w In DataGridView1.Rows
                        NewCount = R0w.index
                        ProgressBar1.Value = NewCount
                        If DataGridView1(0, R0w.index).Value = True Then
                        Else
                            ColumnName = SearchFor.Substring(0, SearchFor.IndexOf("=") - 1)
                            ColumnNameSearch = ColumnName
                            DataGridView1.CurrentCell = DataGridView1.Item(ColumnName, R0w.index)
                            If Not IsDBNull(DataGridView1.CurrentCell.Value) AndAlso DataGridView1.CurrentCell.Value = ItemToFind Then
                                DataGridView1.CurrentRow.DefaultCellStyle.ForeColor = Color.Red
                                DataGridView1(0, R0w.index).Value = True
                            Else
                            End If
                        End If
                    Next
                Else
                    MsgBox(ListBox1.SelectedItem.ToString & " Doest not = " & ItemToFind)
                End If
            End If
        Next
    Here is How I am importing the data:

    Code:
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Work\Import.xlsx;Extended Properties=Excel 12.0;")
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
    MyCommand.TableMappings.Add("Table", "Net-informations.com")
    DtSet = New System.Data.DataSet
    MyCommand.Fill(DtSet)
    tab = DtSet.Tables(0)
    DataGridView1.DataSource = tab
    MyConnection.Close()

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,111

    Re: Need to speed up record selection in a datagridview

    You're doing it too hard...
    Since the DGV is bound to a datatable, filtering the view is easy. Every datatable contains a .DefaultView that you can manipulate... through the .RowFilter property...
    Code:
    dt.DefaultView.RowFilter ="Col1=123 and Col2='Some Text'"
    Manipulating the DefaultView then affects the grid. All you need to do is boil it down to a SQL-compatible WHERE statement ...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2016
    Posts
    66

    Re: Need to speed up record selection in a datagridview

    I'm not sure I follow... I'm to manipulate the data within a DataTable, but where does the code you provide come in handy and what to you mean about the SQL statement? And how would I get these records to turn red like I am doing in my code above?
    Last edited by Christhemist; Nov 1st, 2016 at 06:26 PM.

  4. #4
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    731

    Re: Need to speed up record selection in a datagridview

    Doing what tg suggested would display only the "selected" rows but it sounds like you simply want the "selected" rows to be red while still displaying all the other rows? If that is the case then I'd try tying in your filtering code with the RowPrePaint event.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,830

    Re: Need to speed up record selection in a datagridview

    If you have 100,000 records, is turning red really sufficient? I'd think that asking the user to scroll through 100,000 records looking for highlighted records would likely turn them plenty red. What TG was suggesting would allow you to reduce the records in the DGV down to just the ones selected. That would tame the mass rather than just color it.

    What he meant about the SQL, I believe, is that the .RowFilter for the default view looks very much like the WHERE clause in a SQL statement. They are largely built the same way.
    My usual boring signature: Nothing

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,268

    Re: Need to speed up record selection in a datagridview

    I would also be inclined to follow tg's suggestion, as it would be far easier for the user, and should be dramatically quicker to run (it is also likely to be less code).


    However, if you decide not to, the code you posted can be improved (whether or not you follow topshot's suggestion).

    There are places where you do unnecessary work (eg: you copy strLine to SearchFor, but could just use SearchFor instead), and several places where you access properties when you could use a variable (eg: DataGridView1.CurrentRow when you have R0w), and things you currently do inside a loop that could be outside it (eg: setting ColumnName), and you select items in the grid when you don't really need to.

    Here is a modified version that should be a bit faster:
    Code:
        For Each SearchFor As String In TextBox2.Text.Split(vbNewLine)
            If Not(SearchFor = vbLf & "AND" Or SearchFor = vbLf & "OR") Then
                If EquationVariable = "=" Then
                    ItemToFind = SearchFor.Substring(SearchFor.LastIndexOf("=") + 1).TrimStart
                    ColumnName = SearchFor.Substring(0, SearchFor.IndexOf("=") - 1)
                    For Each R0w as DataGridViewRow In DataGridView1.Rows
                        ProgressBar1.Value = R0w.index
                        If R0w.Cells(0).Value <> True Then
                            Dim cellValue = R0w.Cells(ColumnName).Value
                            If Not IsDBNull(cellValue) AndAlso cellValue = ItemToFind Then
                                R0w.DefaultCellStyle.ForeColor = Color.Red
                                R0w.Cells(0).Value = True
                            End If
                        End If
                    Next
                Else
                    MsgBox(ListBox1.SelectedItem.ToString & " Doest not = " & ItemToFind)
                End If
            End If
        Next

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

Survey posted by VBForums.