8.2 Use a Single Windows Form to Update Multiple Lookup Tables
Just about every database application uses lookup tables of some sort or another, such as
categories, regions, and territories in the Northwind database. Normally, each of these
lookup tables would get its own form for viewing or updating the information in the
tables. This example will provide the means for you to create a single form to maintain
any of your simple lookup tables, again using a Windows Form.
You have a number of simple lookup tables in your application, and it is a pain to have to
create a form for each table. How do you create a Windows Form that can be used to
update most if not all of your lookup tables that are contained in your database?
Technique
Using two main controls, the ListBox control and DataGrid control, you can create a
form that will take care of most of your simple lookup tables. When you get into those
tables that contain lookups or graphics, you will have to come up with a more complete
method to modify the data.
For this example, you will be using some familiar friends: DataAdapter,
CommandBuilder, and DataTable objects.
Steps
Open and run the VB.NET -Chapter 8 solution. From the main Windows Form, click on
the command button with the caption How-To 8.2. You will then see the form displayed
in Figure 8.3.
Figure 8.3. The DataGrid control is filled with different data every time a new item
is chosen in the ListBox control.

When you choose a new item from the list of tables on the left, the data grid on the right
becomes filled with the data from the chosen table. You can then modify the data in the
data grid and click on the Update button to update the data back to the server. This
includes modifying existing records, as well as adding and deleting records in the
DataGrid control.
1. Create a Windows Form. Then place the controls shown in Figure 8.3 with the
properties set forth in Table 8.3.
Table 8.3. Label, ListBox, DataGrid, and Command Button Controls

Listing 8.9 frmHowTo8_2.vb: Establishing the Connection String and
Pointing to the First Item in lstLookupTables
Private Sub frmHowTo8_2_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

'-- Initialize the connection string.
mcnn.ConnectionString = BuildCnnStr("(local)", "Northwind")

'-- Point to the first lookup table; this fires
' the SelectedIndexChanged event off the list box.

Me.lstLookupTables.SelectedIndex = 0

End Sub
7. On the lstLookupTables list box, add the code in Listing 8.10 to the
SelectedIndexChanged event. This routine assigns the new table chosen in
lstLookupTables as the Select command for the modaLookupData data adapter.
The data table called dtData is then filled and set as the data source for
dgTableData.
Listing 8.10 frmHowTo8_2.vb: Populating the DataGrid Control
Private Sub lstLookupTables_SelectedIndexChanged(ByVal sender As
System.Object,
ByVal e As System.EventArgs) Handles
lstLookupTables.SelectedIndexChanged

Dim dtData As New DataTable()

Try

'-- Update the data adapter and data table to reflect the new data,

New OleDb.OleDbCommandBuilder(modaLookupData)

Try

'-- Have to open the connection.
mcnn.Open()

'-- Grabbing the data table from the DataSource
' property of the data grid
' saves a bunch of hassles trying to track the data table directly.

dtFromGrid = CType(dgTableData.DataSource, DataTable)

'-- Commands necessary to actually post back to server.
modaLookupData.Update(dtFromGrid)
dtFromGrid.AcceptChanges()

'-- Don't forget to close the connection.
mcnn.Close()

Catch excp As Exception