how can i distinct a dataset?

Ollie90 used Ask the Experts™
the query gives more items back for each product.

dropdownlist3 should be filled with the product names
dropdownlist4 should be filled with the item names

the problem is that product names are duplicated for each item, while it should be unique. How can i make product names DISTINCT without changing my query above

        Dim da As New OleDbDataAdapter(cmd)
        Dim ds As New DataSet()

        DropDownList3.DataSourceID = String.Empty
        DropDownList3.DataSource = ds
        DropDownList3.DataTextField = ds.Tables(0).Columns(3).ColumnName.ToString()
        DropDownList3.DataValueField = ds.Tables(0).Columns(2).ColumnName.ToString()

        DropDownList8.DataSourceID = String.Empty
        DropDownList8.DataSource = ds
        DropDownList8.DataTextField = ds.Tables(0).Columns(5).ColumnName.ToString()
        DropDownList8.DataValueField = ds.Tables(0).Columns(4).ColumnName.ToString()

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

I am using a function for that purpose


dim dtFundList as DataTable = SelectDistinct(myTableOrView, "col1", "col2",..., "coln")
Public Shared Function SelectDistinct(ByVal SourceTable As DataTable, ByVal ParamArray FieldNames() As String) As DataTable
		Dim lastValues() As Object
		Dim newTable As DataTable

		If FieldNames Is Nothing OrElse FieldNames.Length = 0 Then
			Throw New ArgumentNullException("FieldNames")
		End If

		lastValues = New Object(FieldNames.Length - 1) {}
		newTable = New DataTable

		For Each field As String In FieldNames
			newTable.Columns.Add(field, SourceTable.Columns(field).DataType)

		For Each Row As DataRow In SourceTable.Select("", String.Join(", ", FieldNames))
			If Not fieldValuesAreEqual(lastValues, Row, FieldNames) Then
				newTable.Rows.Add(createRowClone(Row, newTable.NewRow(), FieldNames))

				setLastValues(lastValues, Row, FieldNames)
			End If

		Return newTable
	End Function

	Public Shared Function SelectDistinct(ByVal SourceView As DataView, ByVal ParamArray FieldNames() As String) As DataTable
		Return SelectDistinct(SourceView.ToTable, FieldNames)
	End Function

	Private Shared Function fieldValuesAreEqual(ByVal lastValues() As Object, ByVal currentRow As DataRow, ByVal fieldNames() As String) As Boolean
		Dim areEqual As Boolean = True

		For i As Integer = 0 To fieldNames.Length - 1
			If lastValues(i) Is Nothing OrElse Not lastValues(i).Equals(currentRow(fieldNames(i))) Then
				areEqual = False
				Exit For
			End If

		Return areEqual
	End Function

	Private Shared Function createRowClone(ByVal sourceRow As DataRow, ByVal newRow As DataRow, ByVal fieldNames() As String) As DataRow
		For Each field As String In fieldNames
			newRow(field) = sourceRow(field)

		Return newRow
	End Function

	Private Shared Sub setLastValues(ByVal lastValues() As Object, ByVal sourceRow As DataRow, ByVal fieldNames() As String)
		For i As Integer = 0 To fieldNames.Length - 1
			lastValues(i) = sourceRow(fieldNames(i))
	End Sub

Open in new window

Senior .Net Consultant
Top Expert 2016
the ToTable method of the dataview can do it:
HainKurtSr. System Analyst

nice one emoreau :) I missed this function... I am using this since .Net 1.1 :)

usage is same

dim dtNew as DataTable = myOldView.ToTable(true, "col1", "col2",...,"coln")

I should get rid of my procs and use this instead :)

if you have table

dim dtNew as DataTable = myOldTable.defaultView.ToTable(true, "col1", "col2",...,"coln")

should work on tables...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial