Select distinct 2 columns from a dataset or datatable with LINQ in VB.NET

Question

_{Sunday, January 27, 2013 7:43 PM}

Hi
I have a dataset that is filled from an SQL table. It has four columns (col_1, col_2, col_3, col_4)
I want to create another datatable/dataset wihch contains only two columns (col_1, and col_2) with distinct records from the first dataset with LINQ in VB.NET.

Somthing like SELECT DISTINCT col_1, col_2 FROM dataset.datatable p this has to be done in LINQ and than to populate a combobox (col_1 will be the valueMember and col_2 should be DisplayMember)

Is this possible?

Thanks in advance, Ciprian LUPU

All replies (2)

_{Sunday, January 27, 2013 9:15 PM ✅Answered}

Hi Ciprian;

The following code snippet will show you what you need to do. Please note data types where necessary.

' Create the new dataset object
Dim newDataset As New DataSet
' Create new table object with the name NewTable
Dim newTable As New DataTable("NewTable")
' Create and populate the columns of the table
Dim newColumn As New DataColumn("Col1", GetType(String))
newTable.Columns.Add(newColumn)
newColumn = New DataColumn("Col2", GetType(String))
newTable.Columns.Add(newColumn)
' Assign the table to the new data set
newDataset.Tables.Add(newTable)

' Query the original data table returnung the columns Col1 and Col2, distinct collection
Dim results = (From row In dataset.Tables(0).AsEnumerable()
               Select Col1 = row.Field(Of String)("Col1"), Col2 = row.Field(Of String)("Col2")
               ).Distinct().ToList()

' Because Linq to DataSet does not populate a data table automatically you need to do it manually
For Each dataRow In results
    Dim row As DataRow = newDataset.Tables("NewTable").NewRow()
    row("Col1") = dataRow.Col1
    row("Col2") = dataRow.Col2
    newDataset.Tables("NewTable").Rows.Add(row)
Next

' Populate ComboBox control from data table
ComboBox1.DataSource = newDataset.Tables("NewTable")
ComboBox1.DisplayMember = "Col2"
ComboBox1.ValueMember = "Col1"

  

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

---

_{Monday, January 28, 2013 8:34 AM}

But I still have a small problem.

col_1 type is Int32 and I get this error when I run the app:

Unable to cast object of type 'System.Int32' to type 'System.String'.

Thanks in advance, Ciprian LUPU