How to populate text in 45 buttons from database

[J Trahair]

Hi. I have 45 buttons on a form, Button1, Button2, Button3 to Button45. Each one has to have a Button?.Text value from a database eg. Button1.Text = "Beef", Button2.Text = "Cabbage", etc. In old VB6 I could use the Index value of each button to reference it, but in VB.Net there's no Index capability.

Is there a more elegant way to populate these buttons other than having a Select Case statement from 1 to 45 as below?

        gnumButtonNumber = 1
        Do Until gnumButtonNumber > 45
            mstrSQL = "SELECT * FROM Buttons WHERE ButtonNumber = " & gnumButtonNumber
            mstrThisTableName = Mid$(mstrSQL, InStr(mstrSQL, " FROM ") + 6) & " "   'space? see next line.
            mstrThisTableName = Mid$(mstrThisTableName, 1, InStr(mstrThisTableName, " ") - 1)
            objConn = New SQLiteConnection(mstrSQLiteConnectionString)
            objDs = New DataSet()
            objDa = New SQLiteDataAdapter(mstrSQL, mstrSQLiteConnectionString)
            objDa.Fill(objDs, mstrThisTableName)

            'Show data:
            For Each objRow In objDs.Tables(0).Rows
                Select Case gnumButtonNumber
                    Case 1
                        Button1.Text = objRow("ButtonCaption").ToString
                        Button1.BackColor = System.Drawing.Color.FromName(objRow("ButtonColour").ToString)
                    Case 2
                        Button2.Text = objRow("ButtonCaption").ToString
                        Button2.BackColor = System.Drawing.Color.FromName(objRow("ButtonColour").ToString)
                    Case 3
                        Button3.Text = objRow("ButtonCaption").ToString
                        Button3.BackColor = System.Drawing.Color.FromName(objRow("ButtonColour").ToString)
                    Case 4
                        Button4.Text = objRow("ButtonCaption").ToString
                        Button4.BackColor = System.Drawing.Color.FromName(objRow("ButtonColour").ToString)
                    Case 5
                        Button5.Text = objRow("ButtonCaption").ToString
                        Button5.BackColor = System.Drawing.Color.FromName(objRow("ButtonColour").ToString)

                        'And so on till Case 45!!

                End Select
            Next
            gnumButtonNumber = gnumButtonNumber + 1
        Loop

Thank you.

 

[JohnH]

Controls collection is indexable by position (Integer) and name (String). Each container control in a form, including form itself, has a Controls property exposing this collection.

 

[ss7thirty]

Like John said I would definitely reference the controls collection and loop through something like this:

        For Each ctl As Control In Me.Controls
            If TypeOf ctl Is Button Then
                Dim btn As Button = DirectCast(ctl, Button)
                Dim butNum As Integer = CInt(btn.Name.Replace("Button", ""))
                'now run your query to get the button data with your dynamic button number
                'you can even save the query text in the tag field of your button for later use should you have other properties in that table you want to load at another time
            End If
        Next

 

[jmcilhinney]

As your Button names are sequential, I'd go like this:

For suffix As Integer = 1 To 45
    Dim btn As Button = DirectCast(Me.Controls("Button" & suffix), Button)

    '...
Next

If you're setting the Text then you can probably do without the cast because that's a member of the Control class anyway.