Filling combo box with distinct values from a dataset

Hello,

     I'm attempting to fill a combo box with the values in one column of a dataset table.  Here's the code I'm using:

        Me.cmbItems.DataSource = DataSet1.Tables(0)
        Me.cmbItems.DisplayMember = "Items"

(Dataset = dataset1, "items" is the column in the dataset1 table, ds_table)

This works fine, but I need to cut out all redundancy - anybody have some code to do this same thing but to grab only distinct values from a dataset table column?

JP
gleznovAsked:
Who is Participating?
 
gregoryyoungConnect With a Mentor Commented:
thats because if you look at that code it is creating a table internally and returning it

 Dim dsHelper As DataSetHelper.DataSetHelper
        dsHelper = New DataSetHelper.DataSetHelper(DataSet1)
        dim dt as DataTable = dsHelper.SelectDistinct("DistinctItems", DataSet1.Tables!Custody_Audit, "Nomenclature") 'this returns a datatable

        Me.cmbItems.DataSource = dt
        Me.cmbItems.DisplayMember = "DistinctItems"
0
 
ClifCommented:
This should do it:

        Dim sOldVal As String
        Dim nCount As Integer
        For nCount = ListBox1.Items.Count - 1 To 0 Step -1
            If sOldVal = ListBox1.Items.Item(nCount) Then
                ListBox1.Items.RemoveAt(nCount)
            Else
                sOldVal = ListBox1.Items.Item(nCount)
            End If
        Next

Although the best idea would be to use a "SELECT DISTINCT" query to fill the dataset in the first place.
0
 
gregoryyoungCommented:
that has some issues ... MS dealt with this a while ago http://support.microsoft.com/default.aspx?scid=kb;EN-US;325684 this way you can use the helper class in a reusable fashion (with any dataset) and the code is already written to handle things such as nulls.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
gleznovAuthor Commented:
I took that code and substituted cmbItems for ListBox1, but got this error:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Operator is not valid for 'Nothing' and type 'DataRowView'.

from the line:

If sOldVal = cmbItems.Items.Item(nCount) Then

???
0
 
ClifCommented:
I couldn't get it to fail.  What types of values are you putting into your combo box?

You might try:

If sOldVal = CType(cmbItems.Items.Item(nCount), String) Then

Also, I forgot to mention (but would not cause this error), the combo box needs to be sorted for my code to work.
0
 
gleznovAuthor Commented:
OK, replaced the old line with the new one, got a new error:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from type 'DataRowView' to type 'String' is not valid.

I'm guessing maybe I need another way to read in the data?  I assumed it was reading in as a string?

JP
0
 
gregoryyoungCommented:
why do this very specific method when you can do it so you never need to do it again ? from the MS example (btw there are a bunch of other helpers listed in that area)

Private Function ColumnEqual(ByVal A As Object, ByVal B As Object) As Boolean
    '
    ' Compares two values to determine if they are equal. Also compares DBNULL.Value.
    '
    ' NOTE: If your DataTable contains object fields, you must extend this
    ' function to handle the fields in a meaningful way if you intend to group on them.
    '
    If A Is DBNull.Value And B Is DBNull.Value Then Return True ' Both are DBNull.Value.
    If A Is DBNull.Value Or B Is DBNull.Value Then Return False ' Only one is DBNull.Value.
    Return A = B                                                ' Value type standard comparison
End Function
                              
SelectDistinct(ByVal TableName As String, _
                               ByVal SourceTable As DataTable, _
                               ByVal FieldName As String) As DataTable
    Dim dt As New DataTable(TableName)
    dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)
    Dim dr As DataRow, LastValue As Object
    For Each dr In SourceTable.Select("", FieldName)
        If LastValue Is Nothing OrElse Not ColumnEqual(LastValue, dr(FieldName)) Then
            LastValue = dr(FieldName)
            dt.Rows.Add(New Object() {LastValue})
        End If
    Next
    If Not ds Is Nothing Then ds.Tables.Add(dt)
    Return dt
End Function



0
 
ClifCommented:
gleznov,
Apparently you are reading it as a datarow.

Try this (Presuming you're using SQLServer, if not I have other similar code):
  Dim connSQL As SqlClient.SqlConnection
  Dim commSQL As SqlClient.SqlCommand
  Dim drSQL As SqlClient.SqlDataReader
  Dim sSQL As String

  connSQL = New SqlClient.SqlConnection(sConnectionString)
  sSQL = "SELECT DISTINCT [Item] " & _
         "FROM TableName"
  commSQL = New SqlClient.SqlCommand(sSQL, connSQL)

  connSQL.Open()

  drSQL = commSQL.ExecuteReader(CommandBehavior.CloseConnection)
  If drSQL.HasRows Then
    Do While drSQL.Read
      cmbItems.Items.Add(drSQL.Item("Item"))
    Loop
  End If

Using the above code, you will select all the *unique* records of field "Item" from "TableName".  If you do this, you won't need my code above.
0
 
gleznovAuthor Commented:

gregoryYoung:
I added the class and used:

  Dim dsHelper As DataSetHelper.DataSetHelper
        dsHelper = New DataSetHelper.DataSetHelper(DataSet1)
        dsHelper.SelectDistinct("DistinctItems", DataSet1.Tables!Custody_Audit, "Nomenclature")

        Me.cmbItems.DataSource = DataSet1.Tables(0)
        Me.cmbItems.DisplayMember = "DistinctItems"


But the combo box only lists:

System.Data.Datarowview
System.Data.Datarowview
System.Data.Datarowview
System.Data.Datarowview
System.Data.Datarowview

etc.  

Clif - Trying your code now
0
 
gleznovAuthor Commented:
Clif - any major differences between using SQL Server and just opening an Access .mdb database?

JP
0
 
gleznovAuthor Commented:
Sorry for inconsistency - the actual column name is "nomenclature" rather than "items" and the table name is "custody_audit" in the dataset

JP

Clif - sorry for my ignorance, can you toss me the ole version of that code?
0
 
ClifCommented:
 Dim connOleDb As OleDb.OleDbConnection
  Dim commOleDb As OleDb.OleDbCommand
  Dim drOleDb As OleDb.OleDbDataReader
  Dim sSQL As String
  Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"
  'Fill in the above with the path and filename of your database

  connOleDb = New OleDb.OleDbConnection(sConnectionString)
  sSQL = "SELECT DISTINCT [Item] " & _
         "FROM TableName"
  commOleDb = New OleDb.OleDbCommand(sSQL, connOleDb)

  connOleDb.Open()

  drOleDb = commOleDb.ExecuteReader(CommandBehavior.CloseConnection)
  If drOleDb.HasRows Then
    Do While drOleDb.Read
      cmbItems.Items.Add(drOleDb.Item("Item"))
    Loop
  End If
0
 
gleznovAuthor Commented:
Still had problems with that code.  However I was able to pull some similar code out of a different project of mine and get it to pull the combo box from the database.  HOWEVER, the problem is that my dataset is a join of two tables, and some of the items listed in the database are not in the dataset because they didn't satisfy the join conditions.  Basically this program is creating a history for all items that have changed ownership in the organization.  Not all items have done this though.  So I still need to actually pull a distinct list from the dataset rather than the database.

JP
0
 
gleznovAuthor Commented:
Thanks guys!
0
 
wguerramCommented:
Can you post your Select distinct code?

Have you tried something like LEFT OUTER JOIN or something like that?
This would include all items even if the don't match the join condition.

How do you get all your items in the Dataset?
0
 
wguerramCommented:
Sorry, i forgot to refresh.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.