The Table List Page

To start things off, we define a number of variables that will be used throughout this page:

Dim oServer
Dim sServerName
Dim sUid
Dim sDatabase
Dim sPassword

Of interest, as you will soon see, is the oServer variable. It will be used to hold a reference to the SQL Server OLE Object—in this first part of our sample, we have to take a step beyond ADO. Why? Well, we want catalog information concerning what structures are actually maintained by the SQL Server, and typically this information would be gathered from the ODBC provider.

However, at the time of this writing, the ODBC/ADO provider supplied for SQL Server does not include catalog level functionality. As such, we need to find an alternative method. The alternative is to create a reference to the SQL Server OLE Object directly. SQL Server 6.5 client utilities provide a number of OLE Objects that can be used to access SQL Server services. These OLE objects take advantage of the underlying SQL Server network libraries in order to communicate with the server.

First off, we use the same CreateObject method to create an instance of the SQL Server Object, sqlole.sqlserver, within ADO:

Set OServer = Server.CreateObject("sqlole.sqlserver")

With this reference created, we now have an access mechanism we can use to talk to a SQL Server. Before we can establish a connection, we will need the information supplied by the user on the main page. To do this we use:

sServerName = Trim(Request.Form("ServerName"))
sDatabase   = Trim(Request.Form("DatabaseName")) 
sUid        = Trim(Request.Form("Login")) 
sPassword   = Trim(Request.Form("Password"))

Then, with this information, we call the OLE Object's Connect method to establish a link to the server specified by the user:

OServer.Connect sServerName,sUid,sPassword

An important point to note here is that, because we are accessing the SQL Server via its OLE Object libraries, we are bypassing ODBC completely. Any previous ODBC definitions have no impact on this connection.

With a connection established, we are ready to retrieve the catalog data needed. In this case, we want a list of all the tables that exist for the selected database. This can be done using the catalog functions that are provided via the SQL OLE interface. First we display a confirmation of the login details, then retrieve the information we want by querying the Database object's Tables object. The name of each table found is used to populate a drop down list box:

<SELECT NAME="TableNames" SIZE="1">
<% Set oSQLdb = oServer.Databases(sDatabase)
   For Each oSQLTable In oSQLdb.Tables
      Response.Write "<option value=""" & oSQLTable.Name & """ > " _
                     & oSQLTable.Name & "</option>"
   Next %>
</SELECT>

And here's the result:

Now we can select a table on which to gather additional information, and click the Submit button to bring up our final ASP page Map.asp. This will display all of the fields in the selected table, along with their type, size, precision etc..

To do this, ADO does provide the functionality we need. To begin, we create a read-only, forward-scrolling Recordset:

Set oRS = Server.CreateObject("ADODB.recordset")
oRS.Open "SELECT * FROM " & sDatabase & ".dbo." _
         & Request.Form("TableNames"), "Driver={SQL Server};Server=" _
         & sServerName & ";uid=" & sUid & ";pwd=" & sPassword _
         & ";Database=" & sDatabase & ";DSN=;"

Note that in this instance, the use of a forward-scrolling Recordset is very important. If you remember earlier, we indicated that the default CacheSize setting for this type of cursor is one. As such, even though we are doing a SELECT * SQL command; only a single record will actually be retrieved. You may also notice that this recordset is created without using a DSN. This is because we do not know what the connection will be defined as until the user of the application provides us with a server name. We define the connection 'on the fly'.

With a cursor in place, we are ready to retrieve information about the fields in our table:

iRow = RS.Fields.Count
For iCount = 0 to (iRow - 1)
  Set Fld = RS.Fields(iCount)
  response.write "<TR>"
  response.write "<TD> " & Fld.Name & "</TD>"
  response.write "<TD> " & Fld.Type & "</TD>"
  response.write "<TD> " & Fld.ActualSize & "</TD>"
  If Int(Fld.Precision) >= 255 Then
    response.write "<TD> 0 </TD>" 
  Else
    response.write "<TD> " & Int(Fld.Precision) & "</TD>"
  End If
  If Int(Fld.NumericScale) >= 255 Then
    response.write "<TD> 0 </TD>"
  Else
    response.write "<TD> " & Int(Fld.NumericScale) & "</TD>"
  End If
  response.write "</TR>"
Next

Here we use the number of Field objects in the data source's Fields collection to control how many cells are created in the output table. We then populate each cell with data from each Field object. The end result is a presentation of each Field, along with its attributes: