Solved

Populate combobox from dataset

Posted on 2004-10-16
19
2,033 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

12 Experts available now in Live!

Get 1:1 Help Now