How to populate a listbox with unique ID

Question

5.00/5 (1 vote)

See more:

Please, i have a table in a database where a user ID can appear more than once.
My problem is about populating a list box i.e. in the listbox, an ID should appear only once although it appeared more than once on the table in the database.

The code below works fine but and ID can appear more than once on the list box.

VB

Private Sub loadQueue()

     Try
         'open database
         conMod.openDB()

         Dim myadapter As New SqlDataAdapter("SELECT ID FROM patorder  ", conMod.Con)

         Dim dtset As New DataSet
         myadapter.Fill(dtset)
         myadapter.Dispose()

         Dim myQueue As DataTable = dtset.Tables(0)

         ListPatient.DataSource = myQueue
         ListPatient.DisplayMember = "ID"

         'close database
         conMod.closeDB()

     Catch ex As Exception


         Dim mymsg As New myMsgbox


         With mymsg

             .txtError.Text = "Error! Could not load Queue."
             .ShowDialog()

         End With


     End Try

 End Sub

Posted 23-Mar-13 17:41pm

tarhex

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sheikh Muhammad Haris · Accepted Answer · 2013-03-23T18:21:00

Solution 1

First of all its quite strange that you dont have unique values column (primary key) in your table. You should have one, otherwise its not a good system design.

Change your query to this

VB

Dim myadapter As New SqlDataAdapter("SELECT DISTINCT ID FROM PatOrder (NOLOCK)   ", conMod.Con)

and then bind appropriate column with your list box

VB

ListPatient.DisplayMember = "ID"

This will also solve your problem, and much better than using Reader.

Posted 23-Mar-13 18:21pm

Sheikh Muhammad Haris

Updated 23-Mar-13 19:17pm

v2

Comments

tarhex 24-Mar-13 0:49am

thanks big bro. I have a unique ID as the primary key for registered person.

[no name] 24-Mar-13 0:58am

then? why are you not binding it with listbox ?

tarhex 24-Mar-13 2:11am

i wanted to display the user ID. The user can have more than one entry in the table.

tarhex 24-Mar-13 2:19am

OK, i will try that out. Thanks big bro

tarhex 24-Mar-13 5:43am

it worked perfectly

[no name] 24-Mar-13 22:33pm

I am glad. Best of Luck :)

tarhex · Accepted Answer · 2013-03-23T18:51:00

I was able to solve this problem with the code below;

VB

Try

            'open connection to database
            conMod.openDB()

            'patient ID
            Dim memberID As String


            'check to see if patient ID has be given before


            Dim cmd As SqlCommand = New SqlCommand("SELECT ID FROM patorder ", conMod.Con)

            Dim reader As SqlDataReader = cmd.ExecuteReader()

            'loop through IDs' and only add unique ID
            Do While reader.Read


                memberID = reader(0)

                If Not ListPat.Items.Contains(memberID) Then

                    ListPat.Items.Add(memberID)

                End If


            Loop



            'close the connection to database
            conMod.closeDB()


        Catch ex As Exception


            Dim mymsg As New myMsgbox


            With mymsg

                .txtError.Text = "Error! Could not load Queue."
                .ShowDialog()

            End With


        End Try