Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filling 20 list boxes from single table

Posted on 2004-10-22
10
Medium Priority
?
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

609 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