?
Solved

Help with filtering datatable using VB.net

Posted on 2014-08-20
10
Medium Priority
?
356 Views
Last Modified: 2015-12-20
Hi,

I'm loading data on a grid from a datatable using the code below, How do you filter rows in the Grid to only display records if two or more values for a particular column are identical?

  Dim DV As New DataView(dta, "Receiver like " & "'" & C1Receiver.Columns(0).Value & "'" & "", Nothing, DataViewRowState.CurrentRows)

        Dim FilteredDT As DataTable
        FilteredDT = DV.ToTable
        C1TrueDBGrid45.DataSource = FilteredDT

For example if the NSC column in the grid contains
SN            NSC
10411      TOYOTA
10411       HONDA
10411       TOYOTA
I want to press on a button and only display

SN           NSC
10411     TOYOTA
10411       TOYOTA

Thanks,
Victor
0
Comment
Question by:vcharles
  • 5
  • 5
10 Comments
 
LVL 40
ID: 40275266
I would do it at the database level, by adding a Count column that would be returned when you build the DataTable, but hidden in the grid. You could then filter your DataView on that column.
0
 

Author Comment

by:vcharles
ID: 40280648
Hi,

I'm sorry for the late reply. How would you do it from the DataGridview after loading the table in the Grid?

Thanks,

Victor
0
 
LVL 40
ID: 40280662
I do not work with TrueDBGrid. It might have a feature to do that. I would first look for something like "group" or "aggregation" in the documentation.

In the standard DataGridView, I would use the following algorithm.

Add an extra invisible Boolean column in your table, with a value of False by default.

Either retrieve the data from the database or use a sort or DataView in the application to sort the table on the field(s) on which you want to group.

Loop through the rows. For each one, compare the value in the specified field(s) to the value of those fields in the previous and next row. If they are the same, set you extra column to True to mark it as identical to another one.

Set your filter so that it returns only the rows that have True in the extra column.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:vcharles
ID: 40280771
Can you please send me the code using the DatagridView.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 40280828
I do not have the exact details and I do not have time to setup a test environment and test and debug my code, but it should look something like the following:

                Dim FilteredDT As DataTable  = New DataView(dta, "Receiver like " & "'" & C1Receiver.Columns(0).Value & "'" & "",  "SN,NSC", DataViewRowState.CurrentRows).ToTable

		With FilteredDT

			.Columns.Add("Flag", GetType(Boolean))
			.Columns("Flag").DefaultValue = False

			For x As Integer = 0 To .Rows.Count - 1
				If x < .Rows.Count - 1 Then	'Compare with next row
					If CInt(.Rows(x).Item("SN")) = CInt(.Rows(x + 1).Item("SN")) AndAlso CStr(.Rows(x).Item("SN")) = CStr(.Rows(x + 1).Item("SN")) Then
						.Rows(x).Item("Flag") = True
						.Rows(x).Item("Flag") = True
					End If
				End If
			Next

		End With

		FilteredDT = New DataView(FilteredDT, "Flag=True", "", DataViewRowState.Added).ToTable

                C1TrueDBGrid45.DataSource = FilteredDT

Open in new window

0
 

Author Comment

by:vcharles
ID: 40280888
Thank You.
0
 
LVL 40
ID: 40280952
A small mistake:

.Rows(x).Item("Flag") = True
.Rows(x+1).Item("Flag") = True

I forgot the +1 on the second line
0
 

Author Comment

by:vcharles
ID: 40281231
Thank You.
0
 

Author Comment

by:vcharles
ID: 41378617
Hi Jacques,

How do you modify the code below to work with a DataGridView?

 Dim FilteredDT As DataTable  = New DataView(dta, "Receiver like " & "'" & C1Receiver.Columns(0).Value & "'" & "",  "SN,NSC", DataViewRowState.CurrentRows).ToTable

.Value is a not a property of the DataGridView?

Thanks,

Victor
0
 
LVL 40
ID: 41378757
In a DataGridView, Columns does not represent the data, it represents the caracteristics of the columns: header, width, font to use in the cells and the likes.

To get a specific piece of data, you should go C1Receiver.Item(0,x).Value, where 0 is the column and x the row that contains the value that you want to retrieve.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question