Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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.
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 18:41pm
tarhex407
Rate this: bad
good
Please Sign up or sign in to vote.

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
 
Dim myadapter As New SqlDataAdapter("SELECT DISTINCT ID FROM PatOrder (NOLOCK)   ", conMod.Con)
 
and then bind appropriate column with your list box
 
ListPatient.DisplayMember = "ID"
 

This will also solve your problem, and much better than using Reader.
  Permalink  
v2
Comments
tarhex at 24-Mar-13 0:49am
   
thanks big bro. I have a unique ID as the primary key for registered person.
Sheikh Muhammad Haris at 24-Mar-13 0:58am
   
then? why are you not binding it with listbox ?
tarhex at 24-Mar-13 2:11am
   
i wanted to display the user ID. The user can have more than one entry in the table.
tarhex at 24-Mar-13 2:19am
   
OK, i will try that out. Thanks big bro
tarhex at 24-Mar-13 5:43am
   
it worked perfectly
Sheikh Muhammad Haris at 24-Mar-13 22:33pm
   
I am glad. Best of Luck :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I was able to solve this problem with the code below;
 
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
  Permalink  
Comments
Sheikh Muhammad Haris at 24-Mar-13 1:16am
   
ok. so you want the same IDs but only unique ones.
Sheikh Muhammad Haris at 24-Mar-13 1:18am
   
Check my updated solution. Data Reader is not a good approach for this scenario
Dave Kreskowiak at 24-Mar-13 11:40am
   
THis is an inefficient approach. You were given a better solution to the problem in "Solution 1". Look at the SQL SELECT statement in there. It filters out all the duplicates without you having to check for them when adding to the List.
Sheikh Muhammad Haris at 24-Mar-13 22:33pm
   
Thanks Dave

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

  Print Answers RSS
0 OriginalGriff 410
1 Jochen Arndt 200
2 Richard MacCutchan 135
3 DamithSL 105
4 PIEBALDconsult 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 24 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100