none
Load DatGriedView On search RRS feed

  • Question

  • I am loading a datagridview and i am successfully loading other records from the database as the user scrolls down in the gridview . I added a textbox and  want to filter and search for records that meet the criteria but still mantain the situation where I gradually load records from the database and load other records as the user scrolls down the grid. I am doing this because the table in question has 15000 + records. The search part of it has been a problem since I am not getting the records gradually and then it ends up slowing down my app. I have posted my code below. How do I do the search in such a way that I can stil gradually load my gridview and not slow down my app 
    Imports System.Data.OleDb
    Public Class Form4
    
        Dim myGetItemSalesSummaryDataTable As DataTable = New DataTable
    
        Private bi As BindingSource = New BindingSource()
        Private pageIndex As Integer = 1
        Private PageSize As Integer = 200
        Dim sqlDatabaseConnection As New OleDb.OleDbConnection(myConnectionString)
        Dim da As OleDb.OleDbDataAdapter
        Dim dt As DataTable = New DataTable
        Private ds As DataSet
    
    
        Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Call btnGetAllProducts_Click(sender, e)
    
            ComboBoxSearchBy.SelectedItem = ComboBoxSearchBy.Items.Item(0)
    
            txtSearch.Select()
    
        End Sub
    
        Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
    
    
            If txtSearch.Text = "" Then
                Call btnGetAllProducts_Click(sender, e)
                Exit Sub
            End If
    
            Dim searchField As String = ""
    
            If ComboBoxSearchBy.Text = "Code" Then
                searchField = "OurItemCode"
            ElseIf ComboBoxSearchBy.Text = "Description" Then
                searchField = "OurItemDescription"
            End If
    
            Dim mySearchOperator As String = ""
            If ComboSearchConditions.Text = "Starts With" Then
                mySearchOperator = " LIKE  '" & txtSearch.Text & "%'    "
            ElseIf ComboSearchConditions.Text = "Ends With" Then
                mySearchOperator = " LIKE  '%" & txtSearch.Text & "'    "
            ElseIf ComboSearchConditions.Text = "Contains" Then
                mySearchOperator = " LIKE  '%" & txtSearch.Text & "%'    "
            ElseIf ComboSearchConditions.Text = "Greater Than" Then
                mySearchOperator = " >  '" & txtSearch.Text & "'    "
            ElseIf ComboSearchConditions.Text = "Less Than" Then
                mySearchOperator = " <  '" & txtSearch.Text & "'    "
            ElseIf ComboSearchConditions.Text = "Equals" Then
                mySearchOperator = " =  '" & txtSearch.Text & "'    "
            End If
    
            Dim mySQlQuery As String = ""
            mySQlQuery = "SELECT [ITSItemID]
          ,[SupplierAccountCode]
          ,[SupplierItemCode]
          ,[OurItemCode]
          ,[SupplierItemDescription]
          ,[OurItemDescription]
        ,[SupplierListPrice]                                
                FROM ITSItems  WHERE " & searchField & mySearchOperator & "                            
                ORDER BY OurItemCode ASC"
    
            da = New OleDbDataAdapter(mySQlQuery, sqlDatabaseConnection)
            ds = New DataSet()
            da.Fill(ds)
            dt = ds.Tables(0)
            bi.DataSource = dt
            DataGridViewSales.DataSource = bi
            DataGridViewSales.ClearSelection()
    
        End Sub
    
        Private Sub btnGetAllProducts_Click(sender As Object, e As EventArgs) Handles btnGetAllProducts.Click
    
            Dim mySQlQuery As String = ""
            mySQlQuery = "SELECT [ITSItemID]
          ,[SupplierAccountCode]
          ,[SupplierItemCode]
          ,[OurItemCode]
          ,[SupplierItemDescription]
          ,[OurItemDescription]
        ,[SupplierListPrice]                                
                FROM ITSItems  where ITSItemID between " & pageIndex & " and " & PageSize & "                               
                ORDER BY OurItemCode ASC"
    
            da = New OleDbDataAdapter(mySQlQuery, sqlDatabaseConnection)
            ds = New DataSet()
            da.Fill(ds)
            dt = ds.Tables(0)
            bi.DataSource = dt
            DataGridViewSales.DataSource = bi
            DataGridViewSales.ClearSelection()
    
        End Sub
    
        Private Function GetDisplayedRowsCount() As Integer
            Dim count As Integer = DataGridViewSales.Rows(DataGridViewSales.FirstDisplayedScrollingRowIndex).Height
            count = DataGridViewSales.Height / count
            Return count
        End Function
    
    
        Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
            Me.Close()
        End Sub
    
        Private Sub DataGridViewSalesSummary_Scroll(sender As Object, e As ScrollEventArgs) Handles DataGridViewSales.Scroll
            Dim display As Integer = DataGridViewSales.Rows.Count - DataGridViewSales.DisplayedRowCount(False)
    
            If e.Type = ScrollEventType.SmallIncrement OrElse e.Type = ScrollEventType.LargeIncrement Then
    
                If e.NewValue >= DataGridViewSales.Rows.Count - GetDisplayedRowsCount() Then
    
                    Dim mySQlQuery As String = ""
                    mySQlQuery = "SELECT [ITSItemID]
                                                ,[SupplierAccountCode]
                                                ,[SupplierItemCode]
                                                ,[OurItemCode]
                                                ,[SupplierItemDescription]
                                                ,[OurItemDescription]
                                                ,[SupplierListPrice]                                
                                                FROM ITSItems  where ITSItemID between " & ((pageIndex * PageSize) + 1) & " and " & ((pageIndex + 1) * PageSize) & "                       
                                                ORDER BY OurItemCode ASC"
    
                    da = New OleDbDataAdapter(mySQlQuery, sqlDatabaseConnection)
                    ds = New DataSet()
                    da.Fill(ds)
                    dt.Merge(ds.Tables(0))
                    bi.DataSource = dt
                    DataGridViewSales.ClearSelection()
                    DataGridViewSales.FirstDisplayedScrollingRowIndex = display
                    pageIndex += 1
                End If
            End If
        End Sub
        Private Sub DataGridViewSalesSummary_SelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
            DataGridViewSales.ClearSelection()
        End Sub
    
    End Class


    If you think it you can achieve it

    Thursday, June 7, 2018 3:51 PM

All replies