There has GOT to be a better way... 1

[combs]

This is a newbie question so beware and forgive my ignorance...

I have a form that has severl labels, comboBoxes and DateTimePickers on it -- (it's an In/Out board for our office). I had it working with VB6 and am now trying to create the same thing in VB.Net 2003 (the latest edition we have...) As you can imagine, I'm really missing the control arrays....

I'd like to be able to loop through the rows on the form, but can't seem to access the individual controls for some reason (Code Below). I'd appreciate any help anyone can offer.

Imports System.Data
Imports System.Windows.Forms
Imports System.Drawing.Font

Public Class Form1
    Inherits System.Windows.Forms.Form


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \\4fl-w2k01\public\OutOfOffice\OutOfOffice_Depot.mdb"
        sql = "SELECT Employees.[First Name], Employees.[Last Name], Employees.[Status], Employees.[DateBack], Employees.[TimeBack], " _
        & "Employees.[Office], Employees.[Cell], Employees.[UserName] FROM Employees ORDER BY Employees.[Last Name]"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Names")



        N1.Text = ds.Tables("Names").Rows(0).Item(0) & " " & ds.Tables("Names").Rows(0).Item(1)
        S1.Text = ds.Tables("Names").Rows(0).Item(2)
        If ds.Tables("Names").Rows(0).Item(2) = "IN" Then
            D1.Visible = False
            T1.Visible = False
        Else
            D1.Visible = True
            T1.Visible = True
            D1.Value = ds.Tables("Names").Rows(0).Item(3)
            T1.Text = ds.Tables("Names").Rows(0).Item(4)
        End If
        O1.Text = ds.Tables("Names").Rows(0).Item(5)
        C1.Text = ds.Tables("Names").Rows(0).Item(6)
        'Row 2 -- (row 1 in the Data Adapter)
        N2.Text = ds.Tables("Names").Rows(1).Item(0) & " " & ds.Tables("Names").Rows(1).Item(1)
        S2.Text = ds.Tables("Names").Rows(1).Item(2)
        If ds.Tables("Names").Rows(1).Item(2) = "IN" Then
            D2.Visible = False
            T2.Visible = False
        Else
            D2.Visible = True
            T2.Visible = True
            D2.Value = ds.Tables("Names").Rows(1).Item(3)
            T2.Text = ds.Tables("Names").Rows(1).Item(4)
        End If
        O2.Text = ds.Tables("Names").Rows(1).Item(5)
        C2.Text = ds.Tables("Names").Rows(1).Item(6)
        'Row 3 -- (row 2 in the Data Adapter)
        N3.Text = ds.Tables("Names").Rows(2).Item(0) & " " & ds.Tables("Names").Rows(2).Item(1)
        S3.Text = ds.Tables("Names").Rows(2).Item(2)
        If ds.Tables("Names").Rows(2).Item(2) = "IN" Then
            D3.Visible = False
            T3.Visible = False
        Else
            D3.Visible = True
            T3.Visible = True
            D3.Value = ds.Tables("Names").Rows(2).Item(3)
            T3.Text = ds.Tables("Names").Rows(2).Item(4)
        End If
        O3.Text = ds.Tables("Names").Rows(2).Item(5)
        C3.Text = ds.Tables("Names").Rows(2).Item(6)
        'Row 4 -- (row 3 in the Data Adapter)
        N4.Text = ds.Tables("Names").Rows(3).Item(0) & " " & ds.Tables("Names").Rows(3).Item(1)
        S4.Text = ds.Tables("Names").Rows(3).Item(2)
        If ds.Tables("Names").Rows(3).Item(2) = "IN" Then
            D4.Visible = False
            T4.Visible = False
        Else
            D4.Visible = True
            T4.Visible = True
            D4.Value = ds.Tables("Names").Rows(3).Item(3)
            T4.Text = ds.Tables("Names").Rows(3).Item(4)
        End If
        O4.Text = ds.Tables("Names").Rows(3).Item(5)
        C4.Text = ds.Tables("Names").Rows(3).Item(6)
        'Row 5 -- (row 4 in the Data Adapter)
        N5.Text = ds.Tables("Names").Rows(4).Item(0) & " " & ds.Tables("Names").Rows(4).Item(1)
        S5.Text = ds.Tables("Names").Rows(4).Item(2)
        If ds.Tables("Names").Rows(4).Item(2) = "IN" Then
            D5.Visible = False
            T5.Visible = False
        Else
            D5.Visible = True
            T5.Visible = True
            D5.Value = ds.Tables("Names").Rows(4).Item(3)
            T5.Text = ds.Tables("Names").Rows(4).Item(4)
        End If
        O5.Text = ds.Tables("Names").Rows(4).Item(5)
        C5.Text = ds.Tables("Names").Rows(4).Item(6)
        'Row 6 -- (row 5 in the Data Adapter)
        If ds.Tables("Names").Rows(5).Item(2) = "IN" Then
            N6.Text = ds.Tables("Names").Rows(5).Item(0) & " " & ds.Tables("Names").Rows(5).Item(1)
            S6.Text = ds.Tables("Names").Rows(5).Item(2)
            S6.Tag = ds.Tables("Names").Rows(5).Item(7)
            S6.Items.Add("OUT")
            D6.Visible = False
            T6.Visible = False
            O6.Text = ds.Tables("Names").Rows(5).Item(5)
            C6.Text = ds.Tables("Names").Rows(5).Item(6)
        Else
            N6.Text = ds.Tables("Names").Rows(5).Item(0) & " " & ds.Tables("Names").Rows(5).Item(1)
            N6.Font = New System.Drawing.Font(N6.Font, FontStyle.Bold)
            N6.ForeColor = System.Drawing.Color.Red
            S6.Text = ds.Tables("Names").Rows(5).Item(2)
            S6.Tag = ds.Tables("Names").Rows(5).Item(7)
            S6.ForeColor = System.Drawing.Color.Red
            S6.Items.Add("IN")
            D6.Visible = True
            T6.Visible = True
            D6.Value = ds.Tables("Names").Rows(5).Item(3)
            T6.Text = ds.Tables("Names").Rows(5).Item(4)
            T6.ForeColor = System.Drawing.Color.Red
            O6.Text = ds.Tables("Names").Rows(5).Item(5)
            O6.ForeColor = System.Drawing.Color.Red
            C6.Text = ds.Tables("Names").Rows(5).Item(6)
            C6.Font = New System.Drawing.Font(C6.Font, FontStyle.Bold)
            C6.ForeColor = System.Drawing.Color.Red
        End If
        'Row 7 -- (row 6 in the Data Adapter)
        If ds.Tables("Names").Rows(6).Item(2) = "IN" Then
            N7.Text = ds.Tables("Names").Rows(6).Item(0) & " " & ds.Tables("Names").Rows(6).Item(1)
            S7.Text = ds.Tables("Names").Rows(6).Item(2)
            S7.Tag = ds.Tables("Names").Rows(6).Item(7)
            S7.Items.Add("OUT")
            D7.Visible = False
            T7.Visible = False
            O7.Text = ds.Tables("Names").Rows(6).Item(5)
            C7.Text = ds.Tables("Names").Rows(6).Item(6)
        Else
            N7.Text = ds.Tables("Names").Rows(6).Item(0) & " " & ds.Tables("Names").Rows(6).Item(1)
            N7.Font = New System.Drawing.Font(N7.Font, FontStyle.Bold)
            N7.ForeColor = System.Drawing.Color.Red
            S7.Text = ds.Tables("Names").Rows(6).Item(2)
            S7.Tag = ds.Tables("Names").Rows(6).Item(7)
            S7.ForeColor = System.Drawing.Color.Red
            S7.Items.Add("IN")
            D7.Visible = True
            T7.Visible = True
            D7.Value = ds.Tables("Names").Rows(6).Item(3)
            T7.Text = ds.Tables("Names").Rows(6).Item(4)
            T7.ForeColor = System.Drawing.Color.Red
            O7.Text = ds.Tables("Names").Rows(6).Item(5)
            O7.ForeColor = System.Drawing.Color.Red
            C7.Text = ds.Tables("Names").Rows(6).Item(6)
            C7.Font = New System.Drawing.Font(C7.Font, FontStyle.Bold)
            C7.ForeColor = System.Drawing.Color.Red
        End If
        'Row 8 -- (row 7 in the Data Adapter)
        If ds.Tables("Names").Rows(7).Item(2) = "IN" Then
            N8.Text = ds.Tables("Names").Rows(7).Item(0) & " " & ds.Tables("Names").Rows(7).Item(1)
            S8.Text = ds.Tables("Names").Rows(7).Item(2)
            S8.Tag = ds.Tables("Names").Rows(7).Item(7)
            S8.Items.Add("OUT")
            D8.Visible = False
            T8.Visible = False
            O8.Text = ds.Tables("Names").Rows(7).Item(5)
            C8.Text = ds.Tables("Names").Rows(7).Item(6)
        Else
            N8.Text = ds.Tables("Names").Rows(7).Item(0) & " " & ds.Tables("Names").Rows(7).Item(1)
            N8.Font = New System.Drawing.Font(N8.Font, FontStyle.Bold)
            N8.ForeColor = System.Drawing.Color.Red
            S8.Text = ds.Tables("Names").Rows(7).Item(2)
            S8.Tag = ds.Tables("Names").Rows(7).Item(7)
            S8.ForeColor = System.Drawing.Color.Red
            S8.Items.Add("IN")
            D8.Visible = True
            T8.Visible = True
            D8.Value = ds.Tables("Names").Rows(7).Item(3)
            T8.Text = ds.Tables("Names").Rows(7).Item(4)
            T8.ForeColor = System.Drawing.Color.Red
            O8.Text = ds.Tables("Names").Rows(7).Item(5)
            O8.ForeColor = System.Drawing.Color.Red
            C8.Text = ds.Tables("Names").Rows(7).Item(6)
            C8.Font = New System.Drawing.Font(C8.Font, FontStyle.Bold)
            C8.ForeColor = System.Drawing.Color.Red
        End If
        'Row 9 -- (row 8 in the Data Adapter)
        If ds.Tables("Names").Rows(8).Item(2) = "IN" Then
            N9.Text = ds.Tables("Names").Rows(8).Item(0) & " " & ds.Tables("Names").Rows(8).Item(1)
            S9.Text = ds.Tables("Names").Rows(8).Item(2)
            S9.Tag = ds.Tables("Names").Rows(8).Item(7)
            S9.Items.Add("OUT")
            D9.Visible = False
            T9.Visible = False
            O9.Text = ds.Tables("Names").Rows(8).Item(5)
            C9.Text = ds.Tables("Names").Rows(8).Item(6)
        Else
            N9.Text = ds.Tables("Names").Rows(8).Item(0) & " " & ds.Tables("Names").Rows(8).Item(1)
            N9.Font = New System.Drawing.Font(N9.Font, FontStyle.Bold)
            N9.ForeColor = System.Drawing.Color.Red
            S9.Text = ds.Tables("Names").Rows(8).Item(2)
            S9.Tag = ds.Tables("Names").Rows(8).Item(7)
            S9.ForeColor = System.Drawing.Color.Red
            S9.Items.Add("IN")
            D9.Visible = True
            T9.Visible = True
            D9.Value = ds.Tables("Names").Rows(8).Item(3)
            T9.Text = ds.Tables("Names").Rows(8).Item(4)
            T9.ForeColor = System.Drawing.Color.Red
            O9.Text = ds.Tables("Names").Rows(8).Item(5)
            O9.ForeColor = System.Drawing.Color.Red
            C9.Text = ds.Tables("Names").Rows(8).Item(6)
            C9.Font = New System.Drawing.Font(C9.Font, FontStyle.Bold)
            C9.ForeColor = System.Drawing.Color.Red
        End If
        'Row 10 -- (row 9 in the Data Adapter)
        If ds.Tables("Names").Rows(9).Item(2) = "IN" Then
            N10.Text = ds.Tables("Names").Rows(9).Item(0) & " " & ds.Tables("Names").Rows(9).Item(1)
            S10.Text = ds.Tables("Names").Rows(9).Item(2)
            S10.Tag = ds.Tables("Names").Rows(9).Item(7)
            S10.Items.Add("OUT")
            D10.Visible = False
            T10.Visible = False
            O10.Text = ds.Tables("Names").Rows(9).Item(5)
            C10.Text = ds.Tables("Names").Rows(9).Item(6)
        Else
            N10.Text = ds.Tables("Names").Rows(9).Item(0) & " " & ds.Tables("Names").Rows(9).Item(1)
            N10.Font = New System.Drawing.Font(N10.Font, FontStyle.Bold)
            N10.ForeColor = System.Drawing.Color.Red
            S10.Text = ds.Tables("Names").Rows(9).Item(2)
            S10.Tag = ds.Tables("Names").Rows(9).Item(7)
            S10.ForeColor = System.Drawing.Color.Red
            S10.Items.Add("IN")
            D10.Visible = True
            T10.Visible = True
            D10.Value = ds.Tables("Names").Rows(9).Item(3)
            T10.Text = ds.Tables("Names").Rows(9).Item(4)
            T10.ForeColor = System.Drawing.Color.Red
            O10.Text = ds.Tables("Names").Rows(9).Item(5)
            O10.ForeColor = System.Drawing.Color.Red
            C10.Text = ds.Tables("Names").Rows(9).Item(6)
            C10.Font = New System.Drawing.Font(C10.Font, FontStyle.Bold)
            C10.ForeColor = System.Drawing.Color.Red
        End If
        'N11.Text = ds.Tables("Names").Rows(10).Item(0) & " " & ds.Tables("Names").Rows(10).Item(1)
        'N12.Text = ds.Tables("Names").Rows(11).Item(0) & " " & ds.Tables("Names").Rows(11).Item(1)
        'N13.Text = ds.Tables("Names").Rows(12).Item(0) & " " & ds.Tables("Names").Rows(12).Item(1)
        'N14.Text = ds.Tables("Names").Rows(13).Item(0) & " " & ds.Tables("Names").Rows(13).Item(1)
        'N15.Text = ds.Tables("Names").Rows(14).Item(0) & " " & ds.Tables("Names").Rows(14).Item(1)
        'N16.Text = ds.Tables("Names").Rows(15).Item(0) & " " & ds.Tables("Names").Rows(15).Item(1)
        'N17.Text = ds.Tables("Names").Rows(16).Item(0) & " " & ds.Tables("Names").Rows(16).Item(1)
        'N18.Text = ds.Tables("Names").Rows(17).Item(0) & " " & ds.Tables("Names").Rows(17).Item(1)
        'N19.Text = ds.Tables("Names").Rows(18).Item(0) & " " & ds.Tables("Names").Rows(18).Item(1)
        'N20.Text = ds.Tables("Names").Rows(19).Item(0) & " " & ds.Tables("Names").Rows(19).Item(1)
        'N21.Text = ds.Tables("Names").Rows(20).Item(0) & " " & ds.Tables("Names").Rows(20).Item(1)
        'N22.Text = ds.Tables("Names").Rows(21).Item(0) & " " & ds.Tables("Names").Rows(21).Item(1)
        'N23.Text = ds.Tables("Names").Rows(22).Item(0) & " " & ds.Tables("Names").Rows(22).Item(1)
        'N24.Text = ds.Tables("Names").Rows(23).Item(0) & " " & ds.Tables("Names").Rows(23).Item(1)
        'N25.Text = ds.Tables("Names").Rows(24).Item(0) & " " & ds.Tables("Names").Rows(24).Item(1)
        'N26.Text = ds.Tables("Names").Rows(25).Item(0) & " " & ds.Tables("Names").Rows(25).Item(1)
        'N27.Text = ds.Tables("Names").Rows(26).Item(0) & " " & ds.Tables("Names").Rows(26).Item(1)

        da.Dispose()
        ds.Dispose()
        con.Close()
        con.Dispose()

    End Sub

End Class

 

[SgtJarrow]

Why not something like this:

Dim myLabels() As Label = {Me.Label1, Me.Label2, Me.Label3, ...}
Dim myTextBoxes() As TextBox = Me.TextBox1, Me.TextBox2, ...}
Dim myDatePickers() As TextBox = Me.DatePicker1, Me.DatePicker2, Me.DatePicker3, ...}

For i as Int32 = 0 to 26
    If ds.Tables("Names").Rows(i).Item(2) = "IN" Then
        myTextBox(i).Text = ds.Tables("Names").Rows(i).Item(0) & " " & ds.Tables("Names").Rows(i).Item(1)
        myDatePicker(i).Visible = False
    End If
Next

Shoudl get you there or help you find the solution.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[RiverGuy]

Without reading through all of your code, here are some pointers:

'Loop through form's controls
For i As Integer = 0 To Me.Controls.Count - 1
  Messagebox.Show(Me.Controls(i).Name)
Next

This gets you the collection of controls directly on the form. If you need to get all controls, even controls contained within other controls, you will have to use the container control instead of Me, or call a recursive method.

In .Net, you can create arrays of any object, even controls. The only thing is that VB.Net does not have a built in GUI similar to how you create control arrays in VB6. However, it is very common to create arrays of controls in code, and add those controls to the form or another container at runtime.

 

[combs]

Beautiful!!

mstrmage1768 -- Thank you for your post! I was able to condense the code into what's below.

I gave you a star!

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        'Dim intCount As Integer
        Dim Currentlbl As Integer

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \\4fl-w2k01\public\OutOfOffice\OutOfOffice_Depot.mdb"
        sql = "SELECT Employees.[First Name], Employees.[Last Name], Employees.[Status], Employees.[DateBack], Employees.[TimeBack], " _
        & "Employees.[Office], Employees.[Cell], Employees.[UserName] FROM Employees ORDER BY Employees.[Last Name]"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Names")
        'intCount = 0

        Dim myNames() As Label = {Me.N1, Me.N2, Me.N3, Me.N4, Me.N5, Me.N6, Me.N7, Me.N8, Me.N9, Me.N10, Me.N11, Me.N12, Me.N13, Me.N14, Me.N15, Me.N16, Me.N17, Me.N18, Me.N19, Me.N20, Me.N21, Me.N22, Me.N23, Me.N24, Me.N25, Me.N26, Me.N27}
        Dim myStatus() As ComboBox = {Me.S1, Me.S2, Me.S3, Me.S4, Me.S5, Me.S6, Me.S7, Me.S8, Me.S9, Me.S10, Me.S11, Me.S12, Me.S13, Me.S14, Me.S15, Me.S16, Me.S17, Me.S18, Me.S19, Me.S20, Me.S21, Me.S22, Me.S23, Me.S24, Me.S25, Me.S26, Me.S27}
        Dim myDate() As DateTimePicker = {Me.D1, Me.D2, Me.D3, Me.D4, Me.D5, Me.D6, Me.D7, Me.D8, Me.D9, Me.D10, Me.D11, Me.D12, Me.D13, Me.D14, Me.D15, Me.D16, Me.D17, Me.D18, Me.D19, Me.D20, Me.D21, Me.D22, Me.D23, Me.D24, Me.D25, Me.D26, Me.D27}
        Dim myTime() As ComboBox = {Me.T1, Me.T2, Me.T3, Me.T4, Me.T5, Me.T6, Me.T7, Me.T8, Me.T9, Me.T10, Me.T11, Me.T12, Me.T13, Me.T14, Me.T15, Me.T16, Me.T17, Me.T18, Me.T19, Me.T20, Me.T21, Me.T22, Me.T23, Me.T24, Me.T25, Me.T26, Me.T27}
        Dim myOffice() As Label = {Me.O1, Me.O2, Me.O3, Me.O4, Me.O5, Me.O6, Me.O7, Me.O8, Me.O9, Me.O10, Me.O11, Me.O12, Me.O13, Me.O14, Me.O15, Me.O16, Me.O17, Me.O18, Me.O19, Me.O20, Me.O21, Me.O22, Me.O23, Me.O24, Me.O25, Me.O26, Me.O27}
        Dim myCell() As Label = {Me.C1, Me.C2, Me.C3, Me.C4, Me.C5, Me.C6, Me.C7, Me.C8, Me.C9, Me.C10, Me.C11, Me.C12, Me.C13, Me.C14, Me.C15, Me.C16, Me.C17, Me.C18, Me.C19, Me.C20, Me.C21, Me.C22, Me.C23, Me.C24, Me.C25, Me.C26, Me.C27}

        For i As Int16 = 0 To 26
            If ds.Tables("Names").Rows(i).Item(2) = "IN" Then
                myNames(i).Text = ds.Tables("Names").Rows(i).Item(0) & " " & ds.Tables("Names").Rows(i).Item(1)
                myStatus(i).Text = ds.Tables("Names").Rows(i).Item(2)
                myStatus(i).Tag = ds.Tables("Names").Rows(i).Item(7)
                myStatus(i).Items.Add("OUT")
                myDate(i).Visible = False
                myTime(i).Visible = False
                myOffice(i).Text = ds.Tables("Names").Rows(i).Item(5)
                myCell(i).Text = ds.Tables("Names").Rows(i).Item(6)
            Else
                myNames(i).Text = ds.Tables("Names").Rows(i).Item(0) & " " & ds.Tables("Names").Rows(i).Item(1)
                myNames(i).Font = New System.Drawing.Font(myNames(i).Font, FontStyle.Bold)
                myNames(i).ForeColor = System.Drawing.Color.Red
                myStatus(i).Text = ds.Tables("Names").Rows(i).Item(2)
                myStatus(i).Tag = ds.Tables("Names").Rows(i).Item(7)
                myStatus(i).ForeColor = System.Drawing.Color.Red
                myStatus(i).Items.Add("IN")
                myDate(i).Visible = True
                myTime(i).Visible = True
                myDate(i).Value = ds.Tables("Names").Rows(i).Item(3)
                myTime(i).Text = ds.Tables("Names").Rows(i).Item(4)
                myTime(i).ForeColor = System.Drawing.Color.Red
                myOffice(i).Text = ds.Tables("Names").Rows(i).Item(5)
                myOffice(i).ForeColor = System.Drawing.Color.Red
                myCell(i).Text = ds.Tables("Names").Rows(i).Item(6)
                myCell(i).Font = New System.Drawing.Font(myCell(i).Font, FontStyle.Bold)
                myCell(i).ForeColor = System.Drawing.Color.Red
            End If
        Next

        da.Dispose()
        ds.Dispose()
        con.Close()
        con.Dispose()

    End Sub