How do I condense this code??

[combs]

A newbie question... just getting my feet wet....

I know there is an elegant way to do this, but I'm not seeing it right now.

I have a form with several labels (N1...N27) and I would like to programatically fill the text of each label with names out of a database.

I can do one or several, but I'd like to put the code in a loop and have it all done....

Here's my existing code:

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.mdb"
        sql = "SELECT Employees.[First Name], Employees.[Last Name] FROM Employees ORDER BY Employees.[Last Name]"
        da = New OleDb.OleDbDataAdapter(sql, con)

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


        N1.Text = ds.Tables("Names").Rows(0).Item(0) & " " & ds.Tables("Names").Rows(0).Item(1)
        N2.Text = ds.Tables("Names").Rows(1).Item(0) & " " & ds.Tables("Names").Rows(1).Item(1)
        N3.Text = ds.Tables("Names").Rows(2).Item(0) & " " & ds.Tables("Names").Rows(2).Item(1)
        N4.Text = ds.Tables("Names").Rows(3).Item(0) & " " & ds.Tables("Names").Rows(3).Item(1)
        N5.Text = ds.Tables("Names").Rows(4).Item(0) & " " & ds.Tables("Names").Rows(4).Item(1)
        N6.Text = ds.Tables("Names").Rows(5).Item(0) & " " & ds.Tables("Names").Rows(5).Item(1)

I would greatly appreciate any suggestions anyone would be able to offer...

Is the connection to the database being done the way it should be?? Don't I need to have something to sever the connection at the end of the sub??



Thanks!!

 

[Sorwen]

This should work.

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.mdb"
        sql = "SELECT Employees.[First Name], Employees.[Last Name] FROM Employees ORDER BY Employees.[Last Name]"
        da = New OleDb.OleDbDataAdapter(sql, con)

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

        for count as integer = 0 to ds.tables("Names").Rows.Count - 1
            Dim clabel As Label = me.controls.item("N" & (count + 1))
            clabel.Text = ds.Tables("Names").Rows(count).Item(0) & " " & ds.Tables("Names").Rows(count).Item(1)
        next

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[combs]

Sorwen,

Thanks for your prompt reply.
I inserted the code snippet you suggested and get the following error message:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from string "N1" to type 'Integer' is not valid.

 

[Andrzejek]

Not really the answer to your question, but....

Consider the concatination of your FirstName and LastName in your sql:

sql = "SELECT Employees.[First Name] [blue]+ ' ' + [/blue]Employees.[Last Name] [blue]As MyName[/blue] FROM Employees ORDER BY Employees.[Last Name]"

So you can just

N1.Text = ds.Tables([blue]"MyName"[/blue]).Rows(0).Item(0)

Have fun.

---- Andy

 

[Sorwen]

1) Are you creating the Labels on the fly or are they pre created on the sheet?

2) Are you sure you will always have enought Labels to cover the number of records or are you checking to make sure else where?

3) Just to be sure post the code as it looks now.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[combs]

Sorwen,

Thanks again for your prompt reply.

1) Labels are pre-created on the sheet.
2) Right now I'm working with a fixed number of people, so am not checking to make sure I don't overflow.
3) Code Below:

    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.mdb"
        sql = "SELECT Employees.[First Name], Employees.[Last Name] FROM Employees ORDER BY Employees.[Last Name]"
        da = New OleDb.OleDbDataAdapter(sql, con)

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

''COMMENTED OUT -- NOT WORKING RIGHT NOW

        'For count As Integer = 0 To ds.Tables("Names").Rows.Count - 1
        'Dim clabel As Label = Int(Me.Controls.Item("N" & (count + 1)))
        'clabel.Text = ds.Tables("Names").Rows(count).Item(0) & " " & ds.Tables("Names").Rows(count).Item(1)
        'Next

''THIS IS THE LONG WAY AROUND...


        N1.Text = ds.Tables("Names").Rows(0).Item(0) & " " & ds.Tables("Names").Rows(0).Item(1)
        N2.Text = ds.Tables("Names").Rows(1).Item(0) & " " & ds.Tables("Names").Rows(1).Item(1)
        N3.Text = ds.Tables("Names").Rows(2).Item(0) & " " & ds.Tables("Names").Rows(2).Item(1)
        N4.Text = ds.Tables("Names").Rows(3).Item(0) & " " & ds.Tables("Names").Rows(3).Item(1)
        N5.Text = ds.Tables("Names").Rows(4).Item(0) & " " & ds.Tables("Names").Rows(4).Item(1)
        N6.Text = ds.Tables("Names").Rows(5).Item(0) & " " & ds.Tables("Names").Rows(5).Item(1)
        N7.Text = ds.Tables("Names").Rows(6).Item(0) & " " & ds.Tables("Names").Rows(6).Item(1)
        N8.Text = ds.Tables("Names").Rows(7).Item(0) & " " & ds.Tables("Names").Rows(7).Item(1)
        N9.Text = ds.Tables("Names").Rows(8).Item(0) & " " & ds.Tables("Names").Rows(8).Item(1)
        N10.Text = ds.Tables("Names").Rows(9).Item(0) & " " & ds.Tables("Names").Rows(9).Item(1)
        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()

    End Sub

Andrzejek - I appreciate your comment and may use that in future releases... Any other ideas?? Is the connection to the database done correctly?

Thanks for your help!

 

[Andrzejek]

I know you have your heart set on Labels, but did you consider a grid instead?

Have fun.

---- Andy

 

[combs]

Andrzejek -

A grid? I didn't consider it because I don't know what it is.... It may be better, can you provide a link to some documentation or how-to?

Thanks,
Damon

 

[Andrzejek]

I don't know if you are just pulling my leg, but just Google "grid in VB.NET" and you end up with about 986,000 hits, the very first one looks very prommising

http://www.vbdotnetheaven.com/UploadFile/mahesh/DataGridSamp04232005050133AM/DataGridSamp.aspx

"datagridview in VB.NET" gives you 256,000 hits


Have fun.

---- Andy

 

[Sorwen]

Dim clabel As Label = Int(Me.Controls.Item("N" & (count + 1)))

That is your problem. The line should be.

Dim clabel As Label = Me.Controls.Item("N" & (count + 1))

Me.Controls.Item is going out and finding the control with a name. So for instance Me.Controls.Item("N" & (count +1)) when count is 1 says I want the control named N1. So really what that whole line does is say that for right now I want clabel to be the control (the label) named N1. Since clabel is N1 when you are setting the text you set it for N1. What your line did was try to change that all to an Integer and the program said basically "I don't know how to change all of that into a number". I hope I didn't make that confusing or over simplify it.

Depending on what you doing there could be a lot of ways that are better including a grid. It just all depends on what your end result should be.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

Sorry mistyped. When count is one it gets N2 when count is zero it gets N1.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[combs]

Yes, I was putting in that to try and correct the error that I was receiving.... When I run the code as you have suggested above (and initally suggested) I get the typecast error... I was casting it to see if the error would go away.

Any other ideas? I still don't know why I was receiving the typecast error... Thanks again for your time!

 

[Sorwen]

That is really strange....What version of visual studio are you using? That should work for 2005 and 2008 I'm not sure on before that.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jebenson]

In case you are using VS 2003, give this FAQ a shot: faq796-5698



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[combs]

Thanks to all who've helped diagnose my problems... I'm using 2003.