Solved

Filling 20 list boxes from single table

Posted on 2004-10-22
182 Views
Last Modified: 2010-04-23
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
0
Question by:pharries
    8 Comments
     
    LVL 11

    Expert Comment

    by:Babycorn-Starfish
    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
     

    Author Comment

    by:pharries
    No I have not tried that, any code to share?
    0
     
    LVL 11

    Expert Comment

    by:Babycorn-Starfish

    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
     

    Author Comment

    by:pharries
    Babycorn-Starfish that looks like what I was looking for. If it works I will accept your answer
    Thanks
    Paul
    0
     

    Author Comment

    by:pharries
    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
     
    LVL 11

    Expert Comment

    by:Babycorn-Starfish
    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
     
    LVL 11

    Expert Comment

    by:Babycorn-Starfish
    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
     
    LVL 11

    Accepted Solution

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now