how can i distinct a dataset?

Ollie90
Ollie90 used Ask the Experts™
on
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
cmd.ExecuteNonQuery()

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

        DropDownList3.Items.Clear()
        DropDownList3.Items.Add("")
        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()
        DropDownList3.DataBind()

        DropDownList8.Items.Clear()
        DropDownList8.Items.Add("")
        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()
        DropDownList8.DataBind()

Open in new window

Comment
Watch Question

Do more with

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

Commented:
I am using a function for that purpose

usage:

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)
		Next

		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
		Next

		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
		Next

		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)
		Next

		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))
		Next
	End Sub

Open in new window

Senior .Net Consultant
Top Expert 2016
Commented:
the ToTable method of the dataview can do it: http://msdn.microsoft.com/en-us/library/wec2b2e6.aspx
HainKurtSr. System Analyst

Commented:
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