Solved

Filling combo box with distinct values from a dataset

Posted on 2004-08-30
16
5,095 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
  • 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now