I was answering a question in one of the Forums earlier this week and it reminded me of a little VB.NET gotcha I'd stumbled across previously and that I had almost forgotten. So while it's still at the front of my mind, I thought I'd include it in my blog.
The question asked about how to fetch one column of data from an underlying database and populate a set of 25 labels. Each label had to contain one item from the column - Label1 to contain the first item, Label2 the next and so on. The reading from the database part was easy enough, with various options available.
This is as good as any for our purposes:
' Using Northwind DB
Dim connstr As String
Dim dbConnection As OleDbConnection
Dim DA As OleDbDataAdapter
Dim strSQL As String
Try
connstr = "Provider=SQLOLEDB;Data Source=DESKTOP;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
dbConnection = New OleDbConnection(connstr)
strSQL = "SELECT CompanyName FROM Customers"
DA = New OleDbDataAdapter(strSQL, connstr)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString)
End Try
' Create a New DataTable object and populate it.
Dim DT As DataTable = New DataTable
DA.Fill(DT)
. . .
To avoid a long list of labels, each of which is assigned the next value from the DataTable:
Label1.Text = DT.Rows(0).Item(1).ToString
Label2.Text = DT.Rows(1).Item(1).ToString
Label3.Text = DT.Rows(2).Item(1).ToString
etc, I suggested putting the 25 labels inside a Panel control. This is useful in that you can then play around with those labels without having any effect on any other labels elsewhere on the form.
You can then do what you will with your panel-contained labels and in this case it's simply to enumerate through all of them and assign each one the next value from the DataTable.
' Display information from this DataTable in Labels
Dim i As Integer
Dim lbl As Label
For Each lbl In Me.Panel1.Controls
lbl.Text = DT.Rows(i).Item(0).ToString
i += 1
Next
. . .
Of course it isn't strictly necessary to use the Panel, because the Form itself is a container and the same enumeration technique can be used. Assuming that you wanted all the labels on that form to be assigned one of the values then the code could be changed to:
Dim lbl As Label
For Each lbl In Me.Controls
lbl.Text = DT.Rows(i).Item(0).ToString
i += 1
Next
. . .
And that's when I remembered the little gotcha I'd come upon previously. Even though it appears that you only want the code to deal with labels and no other control types, it insists on looking at all of them. The result is that if your form (or your panel, if that's the route you are taking) contains any other kind of control then you will probably get an Invalid Cast Exception .
Several people have helped with suggested fixes and here are some of them:
1a. Put the code in a Try/Catch block to catch the general exception:
Try
' Display information from this DataTable in Labels
Dim i As Integer
Dim lbl As Label
For Each lbl In Me.Controls
lbl.Text = DT.Rows(i).Item(0).ToString
i += 1
Next
Catch ex As Exception
' No action strictly necessary, but you want to log it when debugging
Debug.WriteLine("Exception thrown")
End Try
. . .
OK, so this stops your app from crashing, which is a plus I guess, but it also fails to populate any of the labels either. So it's probably not the best solution.
1b. Or the specific exception
Catch ex As InvalidCastException
which is technically better, but for practical purposes no improvement.
2. A "fix" of sorts would be to enumerate through all controls (not just labels). If all the controls have a Text property then this would work:
Dim ctrl As Control
Dim i As Integer
For Each ctrl In Me.Panel2.Controls
ctrl.Text = DT.Rows(i).Item(0).ToString
i += 1
Next
. . .
However, I can't think of many realistic scenarios where you would want the data assigned to just any old control in the panel or on the form. Not to mention that once again the app would come crashing down in you have included a control which doesn't have a Text property .
3. Time to stop messing about. The best way I know of to fix this is to enumerate through all the controls in the container and use the TypeOf operator to filter in only the controls that you want to handle (in this case, of course, labels).
Give this a go:
Dim ctrl As Control
For Each ctrl In Panel2.Controls
If (TypeOf ctrl Is Label) Then
ctrl.Text = DT.Rows(i).Item(0).ToString
i += 1
End If
Next
. . .
Sorted!
Just for interest, if you have lots of these labels and you want to put them each in groups, say five sets of five, each set inside a different Panel, then you can expand the above code so that identifies each panel on the form and enumerates through each in turn:
Dim ctrl, ctrl2
As Control
For Each ctrl
In Me.Controls
If TypeOf ctrl
Is Panel
Then For Each ctrl2
In ctrl.Controls
If ctrl2.Text = "Go"
Then
' Do Something
End If
Next End If Next
. . .
Hope this helps at some time. I know I was pleased when I finally find the answer.
----------------------------------------------------------------------------------
* I probably should mention that you also need to be aware that if there are more labels than data items available to fill them, you will get another exception. So you will need to code for this possibility too.