Solved

Filling combo box with distinct values from a dataset

Posted on 2004-08-30
16
5,115 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:gleznov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 10

Expert Comment

by:Clif
ID: 11930979
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11931023
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
 

Author Comment

by:gleznov
ID: 11931057
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Clif
ID: 11931209
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
 

Author Comment

by:gleznov
ID: 11931315
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11931410
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
 
LVL 10

Expert Comment

by:Clif
ID: 11931420
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
 

Author Comment

by:gleznov
ID: 11931511

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
 

Author Comment

by:gleznov
ID: 11931520
Clif - any major differences between using SQL Server and just opening an Access .mdb database?

JP
0
 

Author Comment

by:gleznov
ID: 11931565
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
 
LVL 10

Expert Comment

by:Clif
ID: 11931613
 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
 

Author Comment

by:gleznov
ID: 11931778
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
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 75 total points
ID: 11931873
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
 

Author Comment

by:gleznov
ID: 11932181
Thanks guys!
0
 
LVL 8

Expert Comment

by:wguerram
ID: 11932207
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
 
LVL 8

Expert Comment

by:wguerram
ID: 11932224
Sorry, i forgot to refresh.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Generate Unique ID in VB.NET 21 137
ASP.NET Calendar Control 5 54
Import a excel sheet in a grid 2 45
Binding a GridView in ASP.NET(VB) using Using statements 6 19
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question