?
Solved

Filling 20 list boxes from single table

Posted on 2004-10-22
10
Medium Priority
?
231 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
Comment
Question by:pharries
  • 5
  • 3
8 Comments
 
LVL 11

Expert Comment

by:Babycorn-Starfish
ID: 12380253
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
ID: 12380329
No I have not tried that, any code to share?
0
 
LVL 11

Expert Comment

by:Babycorn-Starfish
ID: 12380433

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

 

Author Comment

by:pharries
ID: 12383257
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
ID: 12391123
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
ID: 12400601
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
ID: 12400849
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:
Babycorn-Starfish earned 1200 total points
ID: 12409789
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

Article by: Jorge
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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 …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…

593 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