• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2107
  • Last Modified:

Populate combobox from dataset

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
Sigh_Man
Asked:
Sigh_Man
  • 9
  • 8
1 Solution
 
addicktzCommented:
May I have the database to see if I can write something to make this work
0
 
Sigh_ManAuthor Commented:
Straight from the design table...

Columns          DataType      Length      AllowNulls

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

Thanks
0
 
addicktzCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Sigh_ManAuthor Commented:
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
 
addicktzCommented:
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
 
Sigh_ManAuthor Commented:
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
 
addicktzCommented:
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
 
Sigh_ManAuthor Commented:
OK, but please give me some sample code to populate a combobox with a column from a datatable.  Thanks.
0
 
Sigh_ManAuthor Commented:
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
 
addicktzCommented:
whats refresh data do?
0
 
addicktzCommented:
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
 
Sigh_ManAuthor Commented:
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
 
addicktzCommented:
thats exactly what it is
0
 
Sigh_ManAuthor Commented:
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
 
addicktzCommented:
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
 
Sigh_ManAuthor Commented:
Thanks for your help.
0
 
Sigh_ManAuthor Commented:
I thought I had accepted that -- sorry.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now