filtering duplicate records from dataset

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.
Who is Participating?
timmyt851Connect With a Mentor Author Commented:
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
Bob LearnedCommented:
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
All Courses

From novice to tech pro — start learning today.