Solved

Populate combobox from dataset

Posted on 2004-10-16
19
2,032 Views
Last Modified: 2012-06-21
I am writing a WinForms application in VB.Net.  I have a combobox which I want to populate at runtime from my SQL database.  I need some code to do this, BUT...

My database contains the following columns:-
LetterGroupName          LetterName

LetterGroupName contains 4 different categories, but each of those categories might contain any number of letters.  Therefore, I cannot simply populate the combobox with the result of a dataset fill from this table, otherwise when the user tries to select a category of letters, the combobox might contain repetitions, eg...

Student
Student
Professional
Professional
Professional
Professional
Unemployed
Unemployed

etc etc - you know what I mean!!

Please give me some sample code for this.  At the moment, I am trying something on the following lines, but I can't seem to get it working:-

daMergeDocs.SelectCommand = New SqlCommand
        daMergeDocs.SelectCommand.Connection = MergeDocsSqlConnection
        daMergeDocs.SelectCommand.CommandText = "SELECT LetterGroupName " & "FROM dtLetters " & "ORDER BY LetterName"
        daMergeDocs.SelectCommand.CommandType = CommandType.Text

        daMergeDocs.Fill(dsCboFields, "dtCboFields")
        daMergeDocs.Fill(dt)

        cboMergeCategory.ValueMember = "id"
        cboMergeCategory.DisplayMember = "LetterGroupName"
        cboMergeCategory.DataSource = dt


I think I know the logic for this, but not the coding!! - Something like: For Each different entry in LetterGroupName add one item to the combobox items collection.

I could hard-code the combobox items collection at design time, but ultimately I might give the user the option to create new categories of letters, which would then require a corresponding item in the combobox.

Thanks in advance    :D
0
Comment
Question by:Sigh_Man
  • 9
  • 8
19 Comments
 
LVL 1

Expert Comment

by:addicktz
ID: 12330448
May I have the database to see if I can write something to make this work
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12330654
Straight from the design table...

Columns          DataType      Length      AllowNulls

LetterSelect      bit      1      1
LetterName      varchar      50      1
LetterGroupName      varchar      50      1

Thanks
0
 
LVL 1

Expert Comment

by:addicktz
ID: 12330858
What is the data under LetterGroupName in the database?

Student
Student
Professional
Professional
Professional
Professional
Unemployed
Unemployed


?

And what your trying to Accomplish to fill in the combobox is

Student
Professional
Unemployed

?
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12330926
Yes, that's correct.  Each letter in the database will belong to one of the 4 groups.

I want the combobox to read the groups from the database and display one item for each different group.
0
 
LVL 1

Expert Comment

by:addicktz
ID: 12331024
You can use this to take the duplicates out of the combobox...
something like ....
.......
cboMergeCategory.DataSource = dt
killdupes(cbomergecatergory)
......
    Public Function killdupes(ByVal cbo As ComboBox)
        Dim myarray(cbo.Items.Count) As String
        Dim didfind As Integer
        Dim xx As Integer
        For xx = 0 To cbo.Items.Count - 1
            myarray(xx) = cbo.Items(xx)
        Next
        cbo.Items.Clear()
        For xx = 0 To UBound(myarray) - 1
            didfind = cbo.FindStringExact(myarray(xx))
            If didfind > -1 Then
            Else
                cbo.Items.Add(myarray(xx))
            End If
        Next
    End Function
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12331541
I can't get that to work.  Please can you check my code for retrieval of the dBase info (shown in my initial question).  Thanks.
0
 
LVL 1

Expert Comment

by:addicktz
ID: 12333511
ok, the function works, my example code on how to use it was probably incorrect, basicly, just use
killdupes(cboMergeCategory) sometime after the combobox is filled with data, maybe at the end of formload? something like that should work fine, you can even use a button to call the function after everything is loaded just to try it and make sure it works on your code.
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12337054
OK, but please give me some sample code to populate a combobox with a column from a datatable.  Thanks.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:Sigh_Man
ID: 12337354
Forget that last part - I've managed to populate the combobox, however...

This is still retrieving duplicates from the database.  I tried running your code as follows, but there is some sort of Invalid Cast Exception - something about a datarowview to a string (or vice versa)...


Private Sub frmMergeDocs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim daCBO As SqlDataAdapter = New SqlDataAdapter("SELECT LetterGroupName FROM dtLetters", MergeDocsSqlConnection)
        daCBO.SelectCommand.CommandType = CommandType.Text
        daCBO.Fill(dsCboFields, "dtLetters")
        MergeDocsSqlConnection.Open()
        daCBO.SelectCommand.ExecuteNonQuery()
        MergeDocsSqlConnection.Close()

        cboMergeCategory.DataSource = dsCboFields.Tables(0)
        cboMergeCategory.ValueMember = "LetterGroupName"
        cboMergeCategory.DisplayMember = "LetterGroupName"
        cboMergeCategory.SelectedIndex = 0

        'KillDuplicates(cboMergeCategory)

        RefreshData()
        AdjustGridToSuit()

    End Sub

    Public Function KillDuplicates(ByVal cbo As ComboBox)

        Dim myarray(cbo.Items.Count) As String
        Dim didfind As Integer
        Dim xx As Integer
        For xx = 0 To cbo.Items.Count - 1
            myarray(xx) = cbo.Items(xx)
        Next
        cbo.Items.Clear()
        For xx = 0 To UBound(myarray) - 1
            didfind = cbo.FindStringExact(myarray(xx))
            If didfind > -1 Then
            Else
                cboMergeCategory.Items.Add(myarray(xx))
            End If
        Next

    End Function


Currently, I'm populating the combobox from the database then trying to remove duplicates from the combobox items.  Maybe instead I should create an array, then sift out duplicate entries from the array then populate the combobox from the array...???

Any ideas.  Please drop me some sample code.

And can anyone explain the difference between .displaymember and .valuemember in combobox terms?

Many thanks.
0
 
LVL 1

Expert Comment

by:addicktz
ID: 12338697
whats refresh data do?
0
 
LVL 1

Expert Comment

by:addicktz
ID: 12338706
assign killduplicates to a button, and use it after the form loads, and tell me if it still is leaving duplicates in the box....
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12352031
No it still contains duplicates.

Although, I did try a tiny routine of my own to remove just one item from the combobox and it produced an error saying something about not being able to do this once the combobox datasource had been set.  Is this because my control is now bound and I'm trying to override that binding?
0
 
LVL 1

Expert Comment

by:addicktz
ID: 12352047
thats exactly what it is
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12352198
So should I create an array which is populated by the datasource and then add items to the combobox based on that array?  THen execute my 'killdupes' routine?
0
 
LVL 1

Accepted Solution

by:
addicktz earned 400 total points
ID: 12352262
that would work, or you can use this:

ifnotdupeadd(cboMergeCategory, isdupetext) ' ifdupetext: is what you are trying to add to the combo-box

public function ifnotdupeadd(cbo as combobox, isdupetext as string)

Dim didfind As Integer

didfind = lstbots.FindStringExact(isdupetext)

If didfind > -1 Then
Else
      cbo.Items.Add(isdupetext)
End If

end function
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12573322
Thanks for your help.
0
 
LVL 1

Author Comment

by:Sigh_Man
ID: 12801423
I thought I had accepted that -- sorry.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

15 Experts available now in Live!

Get 1:1 Help Now