Filling 20 list boxes from single table

I have 20 list boxes on one form
I want the values filled from a single table (each column containing the values for one list box)
All the list boxes are different options that need to be selected independantly.
Problem is if I use 1 dataset then when I pick a value in one box all the others change to that row source.
I think that  using 20 different datasets looks messy.
Any elegant answerrs?
Thank you
pharriesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Babycorn-StarfishCommented:
Hi there,

have you tried using a DataReader to quickly get each column, looping through each row and adding it to the list box?

Hope this helps

BC
0
pharriesAuthor Commented:
No I have not tried that, any code to share?
0
Babycorn-StarfishCommented:

Have a look at this,

http://www.dotnetjohn.com/articles.aspx?articleid=23

ignore the first bit.

I'm not at a PC with VS on so i'm struggling to throw something together quickly - hope the link is of some use,

All the best

BC
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

pharriesAuthor Commented:
Babycorn-Starfish that looks like what I was looking for. If it works I will accept your answer
Thanks
Paul
0
pharriesAuthor Commented:
Babycorn Starfish
I spent 10 hours trying to get that to work for me, I could not
Eventually I came up with it on my own and am posting it here due to the lack of code examples anywhere.
I am using the Advantage Database system (ADS), should work with SQL also though.
As your answer did not rrally help and I solved it myself i do not know what to do r/e points!
_______________________________________________________________________________________________________


   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'note declarations thjat appear on next 2 lines  at top of page
        'Imports Advantage.Data.Provider
        'Imports Advantage.Data.Provider.AdsDataReader

        Try
            'connect to Advantage database
            AdsConnection1.Open()

            'declare ADS data reader
            Dim datareader1 As AdsDataReader = adsSelectCommand1.ExecuteReader()

            'Clear list boxes
            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
 ListBox3.Items.Clear()
            ListBox4.Items.Clear()

            'Cycle datareader
            Do While datareader1.Read()
                ListBox1.Items.Add(datareader1.Item("FirstName"))
                ListBox2.Items.Add(datareader1.Item("LastName"))
                ListBox3.Items.Add(datareader1.Item("DOB"))
                ListBox4.Items.Add(datareader1.Item("SSno"))


            Loop


            'Error catch
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)

        End Try


        'Close connection
        AdsConnection1.Close()

____________________________________________________________________


0
Babycorn-StarfishCommented:
Hi pharries,

sorry for the delay but i only have internet access mon-fri, anyway . . . .

Hacked this together on paper, can't get VS to build on the networked machine i'm on which is pain.

I know you've arrived at a solution yourself but if you use the following you don't need to know how many Listboxes are on the form, i've used combo's in my example. All you need to do is name them appropriately such that they can be recogised. For example if you were to name them DBField suffixed by a number to identify them such as DBField1, DBField2 .....DBFieldn you could try:


        Dim dr As Data.OleDb.OleDbDataReader 'or equivalent
        Dim ctl As ComboBox                            'the type of control i look for, change to listbox
        Dim i As Integer = 0                              'simple counter
        For Each ctl In Me.Controls                    'looks in the form's control collection for controls of type
                                                                   'ComboBox
            If ctl.Name.IndexOf("DBField") Then    'if the control's name contains DBField
                ctl.Items.Add(dr.Item(i))                 'add the dr.Item(current value of i as an index) to the control
            End If
        Next

Not as elegant as i would have liked but the best i could cobble together on paper without a working VS
You could make it more specific by looking for IndexOf("DBField" & i) as long as you numbered your listbox in accordance with the colum you wish to add.

All the best,

BC
0
Babycorn-StarfishCommented:
Hi,

Only problem is, you need to add your listboxes to the form in the same order as the columns. Controls are encountered when iterating through them, in the same order in which they were added, so although it may be called DBField9 it may be encountered second time round in the loop and get assigned column 2's data!!!

So either add your list boxes in the order you wish them to be found - simple but easy to f%&* up

or

1) get the name of the control
2) strip the prefix element of the control's name so you are left with a number - use the substring function for this
3) assign the resulting substring to an integer variable using the ToInteger function ( i think) or maybe CInt
4) use this to access the correct column for the control - you know for sure by virtue of the way you named and numbered your control that you'll get the right data columm

I'll try to check later on if you've made any comments and try to respond.

All the best

Jay
0
Babycorn-StarfishCommented:
Hi there,

finally got a PC that works and threw these two together:

Try

            conn.Open()

            dr = cmd.ExecuteReader

            While dr.Read
                'whatever you called all your ListBox controls
                Dim Name2LookFor As String = "DBField"
                'control object to iterate through the windows form controls collection
                Dim ctl As Control

                'iterate over controls
                For Each ctl In Me.Controls
                    'a listbox object which may get assigned a reference to a listbox
                    Dim lb As System.Windows.Forms.ListBox

                    'check current control type
                    If ctl.GetType.ToString.Equals("System.Windows.Forms.ListBox") Then
                        'if its what we want assign lb a reference to it
                        lb = CType(ctl, System.Windows.Forms.ListBox)

                        'check that it's one of those we want by seeing whether it contains our
                        'predefined naming convention i.e. it contain DBField
                        If lb.Name.IndexOf(Name2LookFor) > -1 Then
                            'get the remainer of the name string to get the ID number
                            Dim indexer As String = lb.Name.Substring(Name2LookFor.Length)
                            'add the item by casting index to integer and deducting 1
                            lb.Items.Add(dr.Item(CInt(indexer) - 1))
                        End If

                    End If

                Next

            End While

        Catch ex As OleDb.OleDbException
            'catch stuff here
        Finally
            'always close connection
            conn.Close()
        End Try


or

 Dim Name2LookFor As String = "DBField"
        Dim ctl As Control
        For Each ctl In Me.Controls
            Dim lb As System.Windows.Forms.ListBox
            If ctl.GetType.ToString.Equals("System.Windows.Forms.ListBox") Then
                lb = CType(ctl, System.Windows.Forms.ListBox)
                If lb.Name.IndexOf(Name2LookFor) > -1 Then
                    Try
                        conn.Open()
                        dr = cmd.ExecuteReader
                        While dr.Read
                            Dim indexer As String = lb.Name.Substring(Name2LookFor.Length)
                            lb.Items.Add(dr.Item(CInt(indexer) - 1))
                        End While
                    Catch ex As OleDb.OleDbException
                    Finally
                        conn.Close()
                    End Try
                End If
            End If

I did two because the first one creates more string objects in proportion to how many columns, so potentially many, which i've seen other people try to address. The second one uses less strings but uses the datareader object repeatedly to achieve the same end - you will notice the while loop right in the centre.

All the best

JW
       
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.