?
Solved

Populate combobox from dataset

Posted on 2004-10-16
19
Medium Priority
?
2,056 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
[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
  • 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
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 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
 
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 1600 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

800 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