Solved

Populate combobox from dataset

Posted on 2004-10-16
19
2,037 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 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