(c) 2002 Visual Studio Magazine
Fawcette Technical Publications

Issue: February 2002
Section: Database Design
Author: Andy Clark

VB.NET	Fill the List of Database Tables
Listing 1	LoadTableList fills a CheckedListBox control  with all tables in the database. It uses sp_tables and an OleDbDataReader object to retrieve the list from all tables in the database.  LoadTableList adds each table name to a CheckedListBox control that allows the user to choose the search's tables.

Private Sub LoadTableList()
Dim adoTables As New OleDb.OleDbCommand _
	("EXEC sp_tables", ADOConnection1)
Dim adoReader As OleDb.OleDbDataReader
Dim strTable As String

	Me.clbxTables.Items.Clear()
	Try
		adoReader = adoTables.ExecuteReader()
		While (adoReader.Read)
			strTable = adoReader.Item( _
				"TABLE_NAME").ToString()
			Me.clbxTables.Items.Add(strTable, _
				False)
		End While
	Catch myException As Exception
		MsgBox(myException.ToString())
	Finally
		adoReader.Close()
		adoReader = Nothing
		adoTables = Nothing
	End Try
End Sub

VB.NET, SQL Server 2000	Load the List of Columns
Listing 2	The RefreshColumnList method builds the list of columns using the tables the user chooses. It first clears the current list, then works its way through the table list searching for all checked tables.  RefreshColumnList uses sp_columns and the OleDbDataReader to show all the columns in the checked tables.

Private Sub RefreshColumnList()
Dim ndx As Integer
Dim adoReader As OleDb.OleDbDataReader
Dim strTable As String
Dim strOut As String

Me.clbxColumns.Items.Clear()
For ndx = 0 To Me.clbxTables.Items.Count - 1
	If (Me.clbxTables.GetItemChecked(ndx)) Then
		strTable = Me.clbxTables.Items( _
			ndx).ToString()
		Dim adoColumns As New OleDb.OleDbCommand _
			("exec sp_columns '" & strTable & _
			"'", ADOConnection1)
		adoReader = adoColumns.ExecuteReader()
		Try
			While (adoReader.Read())
				strOut = strTable + ":" + _
					adoReader.Item( _
					"COLUMN_NAME").ToString() _
					+ ":" + adoReader.Item( _
					"DATA_TYPE").ToString
				Me.clbxColumns.Items.Add(strOut, _
					False)
			End While
		Catch myException As Exception
			MsgBox(myException.ToString())
		Finally
			adoReader.Close()
			adoReader = Nothing
			adoColumns = Nothing
		End Try
	End If
Next
End Sub
