Solved

Filling combo box with distinct values from a dataset

Posted on 2004-08-30
16
5,120 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

615 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