Accessing Individual Data Items

How do you access the individual values in a database table? To see how this works, take a look at the ReadData example on the CD-ROM, which you can see at work in Figure 22.3. In this example, I'm reading data from the authors table in the pubs database directly, and displaying that data in a text box.

Figure 22.3: The ReadData example.

To make this example work, I've dragged a data adapter onto the main form, connected it to the authors table, and created a dataset, DataSet11, from that data adapter. The first step in using this dataset to create the display you see in Figure 22.3 is to determine the name of each column to create the headers you see in that figure. To do that, I'll loop over the authors table as DataSet11.Tables("authors") (you also can use DataSet11.Tables(0), because the authors table is the only table in this collection), and access each column with the Columns collection of the table. Each column's name is stored in the ColumnName property, so here's how I create the header you see in Figure 22.3 (I'm using double tabs to make sure the data is displayed in straight columns):

Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
    Dim RowLoopIndex, ColLoopIndex As Integer
    DataSet11.Clear()
    OleDbDataAdapter1.Fill(DataSet11)

    For ColLoopIndex = 0 To (DataSet11.Tables("authors").Columns.Count - 1)
        TextBox1.Text &= _
            DataSet11.Tables( _
            "authors").Columns(ColLoopIndex).ColumnName & _
            ControlChars.Tab & ControlChars.Tab
        If ColLoopIndex = 0 Then 'Handle wide au_id field
            TextBox1.Text &= ControlChars.Tab
        End If
    Next ColLoopIndex

    TextBox1.Text &= ControlChars.CrLf

    For ColLoopIndex = 0 To (DataSet11.Tables("authors").Columns.Count - 1)
        TextBox1.Text &= "-------" _
            & ControlChars.Tab & ControlChars.Tab
        If ColLoopIndex = 0 Then 'Handle wide au_id field
            TextBox1.Text &= ControlChars.Tab
        End If
    Next ColLoopIndex

    TextBox1.Text &= ControlChars.CrLf
        ⋮

Now I can get the actual data in each row of the table using the table's Rows collection, and the Item property of each DataRow object to get the data in each field. For example, to get the data in the first field of the first row in the Rows collection, I can use the expression Rows(0).Item(0). Here's how I get all the data in the rows of the authors table:

Private Sub Form1_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
    Dim RowLoopIndex, ColLoopIndex As Integer
    DataSet11.Clear()
    OleDbDataAdapter1.Fill(DataSet11)

    For ColLoopIndex = 0 To (DataSet11.Tables("authors").Columns.Count - 1)
        ⋮
    Next ColLoopIndex

    TextBox1.Text &= ControlChars.CrLf

    For ColLoopIndex = 0 To (DataSet11.Tables("authors").Columns.Count - 1)
        ⋮
    Next ColLoopIndex

    TextBox1.Text &= ControlChars.CrLf

    For RowLoopIndex = 0 To (DataSet11.Tables("authors").Rows.Count - 1)
        For ColLoopIndex = 0 To _
            (DataSet11.Tables("authors").Columns.Count - 1)
            TextBox1.Text &= _
        DataSet11.Tables("authors").Rows(RowLoopIndex).Item(ColLoopIndex) _
        & ControlChars.Tab & ControlChars.Tab
        Next ColLoopIndex
        TextBox1.Text &= ControlChars.CrLf
    Next RowLoopIndex
End Sub

If you prefer, you can loop over all data with For Each loops instead, which makes the code easier:

Dim CurrentRow As DataRow
Dim CurrentColumn As DataColumn

For Each CurrentRow In DataSet11.Tables("authors").Rows
    For Each CurrentColumn In DataSet11.Tables("authors").Columns
        TextBox1.Text &= CurrentRow(CurrentColumn) & ControlChars.Tab _
            & ControlChars.Tab
        Next CurrentColumn
    TextBox1.Text &= ControlChars.CrLf
Next CurrentRow

And that's all it takes to create the display you see in Figure 22.3.

Note that you can access the data in the various fields of a data row by numeric index or by name, and you can abbreviate expressions by eliminating the keyword Item. For example, if you're working with a row of data named CurrentRow, and the "First Name" field is the first field in the row, all these statements are equivalent:

FirstName = CurrentRow.Item("First Name")
FirstName = CurrentRow("First Name")
FirstName = CurrentRow.Item(0)
FirstName = CurrentRow(0)