filtering duplicate records from dataset

Posted on 2003-03-26
Medium Priority
Last Modified: 2008-01-16
I have a datagrid filled with a dataset, and some drop-down lists that I use to filter the rows based on certain columns. It's easy enough to fill the drop-downs with all of the possible values for a column with its own select statement, but we really want to only list items that are available in the dataset - so I just set the dropdown list's datasource to the datagrid's dataset with DataTextField and DataValueField set to the appropriate columns in the dataset. But now I get a lot of duplicates since several rows in the dataset contain the same values for that particular column - how do you remove the dups from a list? I tried duplicating the datatable and removing all columns except the 2 I need for the drop-down, then adding a RowFilter of "DISTINCT" to its DefaultView but that didn't work. I sure wish you could just query a dataset or datatable - "SELECT DISTINCT column1,column2 FROM mydataset"... I would use SQL to do this but the dropdown contents will change when users filter the datagrid so most of the filtering is done in the master dataset with RowFilter, the entire contents are loaded on page load and after that it's all in ViewState.
Question by:timmyt851
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

timmyt851 earned 0 total points
ID: 8310689
Well, since no one has come up with a solution I went and wrote my own function. It's not pretty but it does the job. Does anyone have a better solution? It just seems like this does an awful lot of table manipulation just to do a "SELECT DISTINCT" from a subset of columns of a dataset. The function takes a dataset and the id and text columns as parameters, copies the table, removes all columns except the two we want, then creates another table to copy the rows one by one as long as they don't already exist in the new table, then returns the defaultview of the new table. Of course there is code to actually filter the datagrid based on the dropdown selection but that's easy so I'm not including it.

Dim liAll As ListItem = New ListItem("All", -1)
lstVendors.DataSource = ExtractDropDown(dsVendors, "tblVendors_ID", "VendorName")
lstVendors.DataTextField = "VendorName"
lstVendors.DataValueField = "tblVendors_ID"
lstVendors.Items.Insert(0, liAll)

Function ExtractDropDown(ByVal ds As DataSet, ByVal id As String, ByVal text As String) As DataView
     Dim i As Integer
     Dim dt1 As DataTable = ds.Tables(0).Copy()
     For i = 0 To dt1.Columns.Count - 1
          If i >= dt1.Columns.Count Then Exit For
          If dt1.Columns(i).ColumnName <> id And dt1.Columns(i).ColumnName <> text Then
               i = i - 1
          End If
     Dim dt2 As DataTable = dt1.Clone
     Dim pk(1) As DataColumn
     pk(0) = dt2.Columns(id)
     dt2.PrimaryKey = pk
     For i = 0 To dt1.Rows.Count - 1
          Dim dr1 As DataRow = dt1.Rows(i)
          Dim iID(1) As Integer
          iID(0) = dr1(id)
          Dim dr2 As DataRow = dt2.NewRow
          If IsNothing(dt2.Rows.Find(iID(0))) Then
               dr2(id) = dr1(id)
               dr2(text) = dr1(text)
          End If
     Dim dv As DataView = dt2.DefaultView
     Return dv
End Function
LVL 96

Expert Comment

by:Bob Learned
ID: 9725404
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:


Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

743 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