Results 1 to 29 of 29

Thread: Filtering Dataset / MS Database and returning Results to ListView.

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Filtering Dataset / MS Database and returning Results to ListView.

    Hi Team

    Iv taken a look over the forum and I cant quiet find what I am after, I also apologize as what I am about to ask assistance for is for something I have little to no previous experience with (Its been like 17 years since I have worked in VB). I have tried to find a good basic tutorial online as well but they are not what I want. So this might be a bit long winded as I will try to explain all the things I'm trying to do, and if anyone is willing to assist then I would be very grateful for your time.

    To give a little background on what I am trying to do: I have a program that I made built on VBA in Excel it works 100% the way I want and expect it to. I would now like to migrate this over to VB.NET so that the computers it can be used on wont require Excel to be installed on them.

    The program is an inventory program that reads from a MS Access Database, it shows Stock On Hand, Product Codes etc... the program then goes a step further and preforms maths, like Retail Sell and Cost to work out Profit margins etc. In Excel this is easy to do, just use formulas and return the sheet into a listview.

    In VB.net this is all a bit new to me, I have so far managed to import the entire Inventory File into the Listview as I understand it I have moved the MS DB into a DATASET and from the DATASET it is now in the ListView.

    this is the code im so far using:

    Code:
    Imports System.Data.OleDb
    Public Class Form1
    
    
        'this will serve as an open connection to our data source. 
        Public con As OleDb.OleDbConnection = jokendb()
        'this is some set of commands and a database connection that are use to fill later our datatable
        Dim da As New OleDb.OleDbDataAdapter
        'this will represent as our table in memory
        Dim dt As New DataTable
        'this holds our SQL Statements
        Dim sql As String
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            Try
                sql = "SELECT * FROM INVXLS"
                con.Open()
                da = New OleDb.OleDbDataAdapter(sql, Con)
                'fill the data to datatable
                da.Fill(dt)
                'declare a variable as DataRow which is represent a row of data
                Dim newrow As DataRow
                'loop from each row based on the number of rows stored in dt
                For Each newrow In dt.Rows
                    'display the results in listview
                    ListView1.Items.Add(newrow.Item(18)) 'Brand
                    ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(2)) 'Model Name
                    ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(3)) 'Description
                    ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(0)) 'Product Code
                    ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(29)) 'Stock On Hand QTY
    'this is not every column that i will need but just trying to work with this small amount for now to get it working. 
    
    
                Next
    
            Catch ex As Exception
                MsgBox(ex.Message)
    
            End Try
        End Sub
    
        Public Function jokendb() As OleDb.OleDbConnection
            Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MyName\Desktop\INVXLS_8860.mdb")
        End Function
    
    
    End Class
    i now need to build on top of this so that i can preform the following functions,

    • Filter the list by a search term that someone types into a textbox, searching will search a particular column of the database. For example search Product Codes, and return results that match, Search Model Numbers and return results that match. (preferably with LIKE matches)
    • While the listview is being populated, it would need the preform maths on some columns specifically the Cost, and Retail Sell, and then add a column to the list view that will show the Profit based on the maths done. I have a vague idea how this would be done but would like to confirm that such a thing is possible?


    • For better Understanding, the MS DATABASE is called INVXLS_8862.mdb, the TABLE in it is called INVXLS. it has over 10,000 rows.
    • While the TABLE has lots of columns (about 58) to it i don't need to show all of them in the ListView, just the important ones.
    • I don't ever need to Write To or Modify the Database in anyway, the program only reads the data in it and then displays it in a more useful way. The end result also must not leave the MS .mdb open and in use.


    If your wondering why I want to use a ListView and not a DataGridView is that I would like to use the formatting options that a listview has, like setting some rows to Red Text, some to Green, etc depending on its values.

    Once i get a few of these basics coded, then i would be able to work out the rest of the programming to complete the program. So if anyone has experience with this sort of coding your help would be greatly appreciated. Or if you know of some great tutorials that are aimed at this sort of thing again that would be helpful as i understand what i have just asked is not a simple question to answer and would require some time to fully develop.

    Thank you in Advance.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by AndyAndromeda View Post
    If your wondering why I want to use a ListView and not a DataGridView is that I would like to use the formatting options that a listview has, like setting some rows to Red Text, some to Green, etc depending on its values.
    I'd still use a DGV .... I think you can do all that with a DGV and cell formatting of some kind....
    See this gogle search

    Then you can use a BindingSource, attach it to your DataSet/DataTable, set the DataSource of your DGV to the BS, and use the filtering capabilitis of the BS to, well, filter your data as you want. Then all you need to do is format/color the data after a filter (because I'm not sure it retains the colorization on a refresh, you'll nee to play around with that bit), and... WHOOOP! there it is

    -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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by AndyAndromeda View Post
    Filter the list by a search term that someone types into a textbox, searching will search a particular column of the database.
    Populate a DataTable using a data adapter or data reader, bind it to a BindingSource and then filter by simply setting the Filter property of the BindingSource. If the BindingSource is bound to a DataGridView, everything else happens automatically.
    Quote Originally Posted by AndyAndromeda View Post
    While the listview is being populated, it would need the preform maths on some columns specifically the Cost, and Retail Sell, and then add a column to the list view that will show the Profit based on the maths done.
    After populating the DataTable, add an extra column and set its Expression property to perform the maths. Each field in that column will be populated automatically based on the expression and the values in the same row. You don;t need to do any actual calculations. If you bind the DataTable to a DataGridView via a BindingSource after that, a column will be added to the grid automatically.
    Quote Originally Posted by AndyAndromeda View Post
    If your wondering why I want to use a ListView and not a DataGridView is that I would like to use the formatting options that a listview has, like setting some rows to Red Text, some to Green, etc depending on its values.
    That is not a reason not to use a DataGridView as it can do that far better than a ListView.

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    as I understand it I have moved the MS DB into a DATASET and from the DATASET it is now in the ListView.
    Just to clarify, you are not using a DataSet. You are using a DataTable, as you should. You would only create a DataSet if you had multiple Datatables. A Dataset contains a group of datatables.

  5. #5

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    thank you for your info, I have tried to find some YouTube Videos on how to use the DataGridView, and populate it, but I haven't really found anything that was easy enough to understand, so that I could adapt and use it the way I wanted, but I will keep at it.

  6. #6

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    thanks everyone for your suggestions, I need to learn more about BindingSources as I really don't understand how this works.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Hi Andy,

    this will give you a start, you need the Northwind Database for the Data

    Code:
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim sDB As String = "E:\Northwind.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
    
            Dim sSQL As String = "Select ProductName, UnitPrice From Products "
    
            Dim sWhere As String = Nothing
    
            If TextBox1.Text <> Nothing Then
                sWhere &= "And (ProductName Like '" & TextBox1.Text & "%') "
            End If
            'add more search Params
            'If TextBox2.Text <> Nothing Then
            '    sWhere &= "And (Fieldname Like '" & TextBox2.Text & "%') "
            'End If
            If sWhere <> Nothing Then
                sWhere = "Where " & sWhere.Substring(4)
            End If
            sSQL &= sWhere & "Order by [ProductName]"
    
            MsgBox(sSQL)
            Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
            Dim Dr As OleDb.OleDbDataReader
            Dr = Cmd.ExecuteReader
    
            With ListView1
                If .Columns.Count = 0 Then
                    .View = View.Details
                    .Columns.Add("ProductName", 150, HorizontalAlignment.Left)
                    .Columns.Add("UnitPrice", 150, HorizontalAlignment.Right)
                    .FullRowSelect = True
                    .ShowItemToolTips = False
                End If
                .Items.Clear()
                .BeginUpdate()
    
                Do While Dr.Read
                    Dim Li As New ListViewItem
                    Li.UseItemStyleForSubItems = False
                    Li.Text = Dr(0).ToString
                    Li.SubItems.Add(FormatNumber(Dr("UnitPrice"), 2, TriState.False, TriState.True, TriState.True))
    
    'color some Values
                    If CDbl(Dr("UnitPrice").ToString) >= 2 AndAlso CDbl(Dr("UnitPrice").ToString) <= 12 Then
                        Li.SubItems(1).ForeColor = Color.AntiqueWhite
                        Li.SubItems(1).BackColor = Color.SaddleBrown
    'add 2 Images to the Imagelist
    'Names of Images IsOK and NotOk
                        Li.ImageIndex = ImageList1.Images.IndexOfKey("isOk")
    
                    ElseIf CDbl(Dr("UnitPrice").ToString) >= 13 AndAlso CDbl(Dr("UnitPrice").ToString) <= 22 Then
                        Li.SubItems(1).ForeColor = Color.Blue
                        Li.ImageIndex = ImageList1.Images.IndexOfKey("notOk")
    
                    ElseIf CDbl(Dr("UnitPrice").ToString) >= 50 Then
                        Li.SubItems(1).ForeColor = Color.Tomato
                    End If
                    .Items.Add(Li)
                Loop
                .EndUpdate()
            End With
            Dr.Close()
        End Sub
    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by ChrisE View Post
    Hi Andy,

    this will give you a start, you need the Northwind Database for the Data

    Code:
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim sDB As String = "E:\Northwind.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
    
            Dim sSQL As String = "Select ProductName, UnitPrice From Products "
    
            Dim sWhere As String = Nothing
    
            If TextBox1.Text <> Nothing Then
                sWhere &= "And (ProductName Like '" & TextBox1.Text & "%') "
            End If
            'add more search Params
            'If TextBox2.Text <> Nothing Then
            '    sWhere &= "And (Fieldname Like '" & TextBox2.Text & "%') "
            'End If
            If sWhere <> Nothing Then
                sWhere = "Where " & sWhere.Substring(4)
            End If
            sSQL &= sWhere & "Order by [ProductName]"
    
            MsgBox(sSQL)
            Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
            Dim Dr As OleDb.OleDbDataReader
            Dr = Cmd.ExecuteReader
    
            With ListView1
                If .Columns.Count = 0 Then
                    .View = View.Details
                    .Columns.Add("ProductName", 150, HorizontalAlignment.Left)
                    .Columns.Add("UnitPrice", 150, HorizontalAlignment.Right)
                    .FullRowSelect = True
                    .ShowItemToolTips = False
                End If
                .Items.Clear()
                .BeginUpdate()
    
                Do While Dr.Read
                    Dim Li As New ListViewItem
                    Li.UseItemStyleForSubItems = False
                    Li.Text = Dr(0).ToString
                    Li.SubItems.Add(FormatNumber(Dr("UnitPrice"), 2, TriState.False, TriState.True, TriState.True))
    
    'color some Values
                    If CDbl(Dr("UnitPrice").ToString) >= 2 AndAlso CDbl(Dr("UnitPrice").ToString) <= 12 Then
                        Li.SubItems(1).ForeColor = Color.AntiqueWhite
                        Li.SubItems(1).BackColor = Color.SaddleBrown
    'add 2 Images to the Imagelist
    'Names of Images IsOK and NotOk
                        Li.ImageIndex = ImageList1.Images.IndexOfKey("isOk")
    
                    ElseIf CDbl(Dr("UnitPrice").ToString) >= 13 AndAlso CDbl(Dr("UnitPrice").ToString) <= 22 Then
                        Li.SubItems(1).ForeColor = Color.Blue
                        Li.ImageIndex = ImageList1.Images.IndexOfKey("notOk")
    
                    ElseIf CDbl(Dr("UnitPrice").ToString) >= 50 Then
                        Li.SubItems(1).ForeColor = Color.Tomato
                    End If
                    .Items.Add(Li)
                Loop
                .EndUpdate()
            End With
            Dr.Close()
        End Sub
    hth
    thank you


    I will give this a try, I will see if I can mod this to my database rather then using the northwind database.

    I appreciate your help

  9. #9

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Thank you to everyone that commented on this to help me with some direction i have now got this all working with the assistance of a colleague at work.

    i do have one question, one of the functions i have created highlights certain cells if thier value is over $0. the cells turn red with white bold text. the thing is this seems to be a bit slow to do and there is a considerable pause with the application if the data gridview has a lot of items in it. My question is, could i off load this function to a background process? and if so what would be the correct way to implement it. i would only want to off load the function to a background process if it would mean i could continue to click on item on the userform. if it will remain in a paused state then there won't be any point implementing it.

    here is my code.
    Code:
        Private Sub Highlight_OnSale()
    
            If Home_Highlight_OnSale.Checked = True Then
                Query_Status.Text = "FORMATTING ON SALE HIGHLIGHTS"
                Application.DoEvents()
                Dim rowIndex As Integer = 0
                'Loop through each cell in DataGridView column "Online" 
                For rowIndex = 0 To DataGridView1.RowCount - 1 '-1 is to skip the NewRow at the end.
    
                    If DataGridView1.Rows(rowIndex).Cells("Online").Value > 0 Then
                        With DataGridView1.Rows(rowIndex).Cells("Online").Style
                            .BackColor = Color.Red
                            .ForeColor = Color.White
                            .SelectionForeColor = Color.White
                            .SelectionBackColor = Color.Black
                            .Font = New Font(DataGridView1.Font, FontStyle.Bold)
    
                        End With
    
                    Else
                        With DataGridView1.Rows(rowIndex).Cells("Online").Style
                            .ForeColor = Color.White
                            .SelectionForeColor = SystemColors.Highlight
    
                        End With
    
                    End If
    
                Next
                Query_Status.Text = "HIGHLIGHT COMPLETE"
            End If
        End Sub

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    try it this way

    Load the DGV first
    at the very end of your Code add the ApplyFormatting



    Code:
     Private Sub ApplyFormatting()
       
            Dim CurrentCell As DataGridViewCell
    
            For Each Row As DataGridViewRow In DataGridView1.Rows
                CurrentCell = Row.Cells("UnitsInStock")
                If IsNumeric(CurrentCell.Value) Then
                    Select Case Convert.ToInt32(CurrentCell.Value)
                        'here you put your conditions
                        Case 10
                            CurrentCell.Style.BackColor = Color.Green
                        Case Is > 2
                            CurrentCell.Style.BackColor = Color.Yellow
                        Case 3
                            CurrentCell.Style.BackColor = Color.Orange
                        Case Else
    
                    End Select
                End If
                'check for Null values
                If IsDBNull(Row.Cells("UnitsInStock").Value) Then
                    Row.Cells("UnitsInStock").Style.BackColor = Color.BlueViolet
                End If
    
            Next
        End Sub
    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    This is how I'm calling the subroutine. The Datagridview is first populated and then the "formatting" subroutine is called to apply the colours to the cells that require it.

    As you can see from my code if a checkbox is not set to true it will exit and not apply the formatting to the cells. I added this option in the event that a person is knowingly going to have a large search result they can turn off the formatting for it.

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Generally speaking, you should be handling the CellFormatting event of the grid to format each cell every time it is required. It may not be as important if you're not editing but doing so allows you to change formatting for individual cells as they change without caring about the rest of the grid.

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    The time it takes to format the dgv is dictated by the number of rows and the number of column. I notice the title of this thread mentions filtering. You might consider filtering the dgv before applying the format changes. How many columns per row and how many rows are you talking about?

  14. #14

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Hi Wes

    I have a few columns, most are kept hidden in total there are 28 Columns, depending on the search there can any number of results. but i found a filter that resulted in 217 rows, this is the slowest to format with the colours of the cells. hench why i was thinking i could off load this to a background process.

    regards

  15. #15

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by jmcilhinney View Post
    Generally speaking, you should be handling the CellFormatting event of the grid to format each cell every time it is required. It may not be as important if you're not editing but doing so allows you to change formatting for individual cells as they change without caring about the rest of the grid.
    I'm not really sure what you mean, each time a user performs a search this results in a new filtered list in the datagridview, only after that list is displayed is it formatted. i would think that's the same as what you just suggested unless i'm missing something?

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by AndyAndromeda View Post
    Hi Wes

    I have a few columns, most are kept hidden in total there are 28 Columns, depending on the search there can any number of results. but i found a filter that resulted in 217 rows, this is the slowest to format with the colours of the cells. hench why i was thinking i could off load this to a background process.

    regards
    217 isn't very many, why do you have a bunch of hidden columns. If you don't need them then don't use them.

  17. #17

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by wes4dbt View Post
    217 isn't very many, why do you have a bunch of hidden columns. If you don't need them then don't use them.
    because they contain values that are used elsewhere in the program, for example a hidden column contains the cost of goods. so else where there is a calculation done when the item is selected to work out how much margin and how much profit is made from that particular item.

    of the 51 columns the database contains i only need 21 hence i have already cut it down to as little as possible.

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Just because you need those fields in other places in the program doesn't mean you have to add them to the dgv. But since you don't iterate through the columns, they are probably not the problem.

    I ran this test,

    Code:
            MessageBox.Show("ready")
    
            For rowIndex = 0 To DataGridView1.RowCount - 1
    
                With DataGridView1.Rows(rowIndex).Cells("GroupId").Style
                    .BackColor = Color.Red
                    .ForeColor = Color.White
                    .SelectionForeColor = Color.White
                    .SelectionBackColor = Color.Black
                    .Font = New Font(DataGridView1.Font, FontStyle.Bold)
    
                End With
    
    
            Next
    
            MessageBox.Show("Done  " & dt.Rows.Count.ToString)
    There were 444 row and the result was almost instant. So are you sure this is what's slowing down your program. Have you ran it without the formatting?

  19. #19
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Hi wes,
    in Post#17 the OP talks about
    so else where there is a calculation done when the item is selected
    so where and when does this take place ? when Loading the DGV?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  20. #20

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by ChrisE View Post
    Hi wes,
    in Post#17 the OP talks about
    so where and when does this take place ? when Loading the DGV?
    When the user selects a line in the datagridview.

    to better explain, the Datagridview contains a list of products available for sale. the datagridview can be sorted to show only a certain group of items of similar "class" if you will. then the user selects an item and it shows them more detailed information such as its cost, its profit, etc etc.

    The formatting is to show which items are now Clearance, which items are on sale etc.

  21. #21

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by wes4dbt View Post
    Just because you need those fields in other places in the program doesn't mean you have to add them to the dgv. But since you don't iterate through the columns, they are probably not the problem.

    I ran this test,

    Code:
            MessageBox.Show("ready")
    
            For rowIndex = 0 To DataGridView1.RowCount - 1
    
                With DataGridView1.Rows(rowIndex).Cells("GroupId").Style
                    .BackColor = Color.Red
                    .ForeColor = Color.White
                    .SelectionForeColor = Color.White
                    .SelectionBackColor = Color.Black
                    .Font = New Font(DataGridView1.Font, FontStyle.Bold)
    
                End With
    
    
            Next
    
            MessageBox.Show("Done  " & dt.Rows.Count.ToString)
    There were 444 row and the result was almost instant. So are you sure this is what's slowing down your program. Have you ran it without the formatting?
    Yes, as i have a checkbox that can turn on or off the formatting, if the formatting is turned off the DGV will load instantly. Technically the DGV always loads instantly even with formatting turned on, its once the formatting subroutine has started that the user can no longer use the mouse and click on items and must wait for the subroutine to finish before they can click on an item in the DGV.

  22. #22
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by AndyAndromeda View Post
    Yes, as i have a checkbox that can turn on or off the formatting, if the formatting is turned off the DGV will load instantly. Technically the DGV always loads instantly even with formatting turned on, its once the formatting subroutine has started that the user can no longer use the mouse and click on items and must wait for the subroutine to finish before they can click on an item in the DGV.
    sorry, don't understand what your saying.

    You say the dgv formats instantly, then you say "its once the formatting subroutine has started that the user can no longer use the mouse and click on items and must wait for the subroutine to finish", which is it? Are you saying when you filter the grid is when the lag happens. If so, then again I'll ask Have you ran it without the formatting? The problem could be the filter.

  23. #23

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by wes4dbt View Post
    sorry, don't understand what your saying.

    You say the dgv formats instantly, then you say "its once the formatting subroutine has started that the user can no longer use the mouse and click on items and must wait for the subroutine to finish", which is it? Are you saying when you filter the grid is when the lag happens. If so, then again I'll ask Have you ran it without the formatting? The problem could be the filter.
    The DGV "filters" instantly that not the problem, filtering is not what i'm asking about. its solely for the formatting of cells that slows down a lot. the DGV is filtered first the results are displayed, then the formatting subroutine is run, to colour cells in. they are two entirely separate tasks and done independent of each other.

  24. #24
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Technically the DGV always loads instantly even with formatting turned on
    What does that mean then


    Edit - Are the cells formatted or not at this point. Also, do you call the formatting routine from any of the dgv events, you may be getting multiple calls to the procedure each time you call it.
    Last edited by wes4dbt; Jan 12th, 2020 at 09:32 PM.

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    This is what I'm talking about, if you call the formatting routine from the Cellformatting Event, it will be called multiple times as the formatting procedure reformats the cells.

    Here's an example,
    Code:
        Private doFmt As Boolean = False
        Private cnt As Integer
    
        Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
            If doFmt Then
                fmt()
            End If
    
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            doFmt = True
        End Sub
    
        Private Sub fmt()
            cnt += 1
            Debug.Print("sub called  " & cnt.ToString)
            If doFmt Then
                For rowIndex = 0 To DataGridView1.RowCount - 1
    
                    With DataGridView1.Rows(rowIndex).Cells("GroupId").Style
                        .BackColor = Color.Red
                        .ForeColor = Color.White
                        .SelectionForeColor = Color.White
                        .SelectionBackColor = Color.Black
                        .Font = New Font(DataGridView1.Font, FontStyle.Bold)
    
                    End With
    
    
                Next
            End If
        End Sub
    I load the dgv while Dofmt = False
    I Click the Button and Dofmt = True

    Now when I move my mouse onto the dgv (without even clicking anything) the fmt procedure starts getting called, over and over.

    I have 444 row in the dgv. First time I tested, fmt was called 71 times, the really strange thing is the number changes each time I run the test, I got 54, 47, 45, 63.

    Honestly though, even with the fmt being called over and over there still wasn't any significant lag.

  26. #26
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by AndyAndromeda View Post
    Hi Wes

    I have a few columns, most are kept hidden in total there are 28 Columns, depending on the search there can any number of results. but i found a filter that resulted in 217 rows, this is the slowest to format with the colours of the cells. hench why i was thinking i could off load this to a background process.

    regards
    I jsut want to answer this real quick since it didn't seem to be addressed anywhere that I could see... off loading the formatting to a BGW won't do any good... all UI is handled by the main UI thread. You can do calculation on alternate threads, but the moment you need to update something on the screen, you have to move back to the main thread. In this case, there would be so much switching back and forth to/from the main thread, it would be even worse than it is now. I'd just keep it where it is now, and not worry about the BGW for any formatting problems.

    -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??? *

  27. #27

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by techgnome View Post
    I jsut want to answer this real quick since it didn't seem to be addressed anywhere that I could see... off loading the formatting to a BGW won't do any good... all UI is handled by the main UI thread. You can do calculation on alternate threads, but the moment you need to update something on the screen, you have to move back to the main thread. In this case, there would be so much switching back and forth to/from the main thread, it would be even worse than it is now. I'd just keep it where it is now, and not worry about the BGW for any formatting problems.

    -tg
    Thank you this is all I needed to know.

    Oddly enough I tested the program at work on like 10 year old machines the code works rather quick but at home on my big gaming rig it really slows down for some reason... So yeah... Haha.

  28. #28

    Thread Starter
    Member
    Join Date
    Nov 2019
    Posts
    55

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    Quote Originally Posted by wes4dbt View Post
    What does that mean then


    Edit - Are the cells formatted or not at this point. Also, do you call the formatting routine from any of the dgv events, you may be getting multiple calls to the procedure each time you call it.
    No I'm not using the formatting cell event handler to call the formatting or any event handler of the DGV for that fact.

    It's a sub routine I made my self that once the code reaches that point it goes to that subroutine that I posted to do the formatting. It only gets called once.

    In regards to your quote what did I mean by when formatting is turned on. I ment when I have my check box ticked for formatting (aka turned on) the dGV loads instantly. Formatting happens after it loads / populates / filters. There was clearly some confusion further up as to what each other ment.

    But it's all good cause techgnome answered with what I needed to know.

  29. #29
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Filtering Dataset / MS Database and returning Results to ListView.

    I am glad you got the answer you needed. If your happy with the program performance than that's fine. But there really shouldn't be a noticeable lag formatting one cell in 217 rows. I haven't seen how or where you call the formatting procedure but that would be a good place to start if you want to research this problem any further. I did just notice you have Application.DoEvents in the procedure. Doesn't seem necessary.

    Good luck

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
  •  



Click Here to Expand Forum to Full Width