Creating an ASP Script

1. Open Notepad and enter the following ASP script:

' declare variables Dim accessDB Dim conn Dim rst Dim sql

Response.ContentType = "Application/vnd.ms-excel"

' name of the database accessDB="Northwind"

' establish connection to the database conn="DRIVER={Microsoft Access Driver (*.mdb)};" conn=conn & "DBQ=" & Server.Mappath(accessdb)

' Create a Recordset

Set rst = Server.CreateObject("ADODB.Recordset")

' select all records from Shippers table sql = "SELECT * FROM Shippers"

' Open Recordset (and execute SQL statement above)

rst.Open sql, conn

For Each fld in rst.Fields

Next rst.MoveFirst

Do While Not rst.EOF

For Each fld in rst.Fields

<% Response.Write fld.Value %> </TD> <% Next %>

Loop

rst.Close

Set rst=Nothing

2. Save the file as C:\AccessTbl.asp.

3. Close Notepad.

Notice that the above ASP script begins by declaring variables. In ASP scripts, all variables are of the Variant type. Therefore, you don't need to use the As keyword to specify the type of variable:

Dim accessDB Dim conn Dim rst Dim sql

You can also declare all your variables on one line, like this: Dim accessDB, conn, rst, sql

To tell the browser that the code that follows should be formatted for display in Excel, use the following directive:

Response.ContentType = "Application/vnd.ms-excel"

The ContentType property of the Response object specifies which format should be used for displaying data obtained from a web server. If you don't set this property, the data will default to text/HTML format.

To connect with the Access database, the ASP script shown above uses the following connection that accesses the Microsoft Access driver directly:

conn="DRIVER={Microsoft Access Driver (*.mdb)};"

conn=conn & "DBQ=" & Server.Mappath(accessDB)

Conn is an object variable that stores the string specifying how to connect to the database. The DRIVER parameter specifies the name of the driver that you are planning to use for this connection (Microsoft Access Driver (*.mdb")). The DBQ parameter indicates the database path. The exact path will be supplied by the Mappath method of the Server object:

Server.Mappath(accessDB)

You can also connect to your Access database by using the OLE DB data provider as follows:

Set conn = Server.CreateObject("OLEDB.Connection") conn.Open "Provider=Microsoft .Jet.0LEDB.4.0; Data Source=" & Server.MapPath(accessDB)

To connect to an SQL server database, use the following format:

Set conn = Server.CreateObject("OLEDB.Connection") conn.Open "Provider="SQLOLEDB;" &_ "Data Sojrce=YourServerName;" & _ "Initial Catalog=accessDB;" &_ "UID=yourId; Password=yourPassword;"

To gain access to database records, the ASP script creates the Recordset object using the CreateObject method of the Server object:

Set rst = Server.CreateObject("ADODB.Recordset")

After creating the Recordset, open it using the Open method, like this:

rst.Open sql, conn

The above statement opens a set of records. The sql variable indicates that you want to select all the records from the Shippers table (sql = "SELECT * FROM Shippers"). The conn variable indicates how you will connect with the database.

The next part of the ASP script contains HTML formatting tags that prepare a table. The table headings are read from the Fields collection of the Recordset object using the For...Each... Next loop. Notice that all instructions that need to be executed on the Server are enclosed by the <% and %> tags. To enter the data returned by the Server in the appropriate worksheet cells, use the Write method of the Response object:

Response.Write fld.Name

The above statement will return the name of a table field. Because this instruction appears between the <TH> and </TD> formatting tags, the names of the table fields will be written in the first worksheet row in bold type.

After reading the headings, the next loop reads the values of the fields in each record:

Response.Write fld.Value

Because this statement is located between the <TD> and </TD> formatting tags, the values retrieved from each field in a particular record will be written to table cells. The ASP script ends by closing the recordset and relasing the memory used by it:

rst.Close set rst = Nothing

To try out this ASP script on your own computer, you must first:

1. Install Microsoft Information Services (IIS) 5.0 or a newer version (for Windows NT/ 2000 Professional or Windows XP) or Personal Web Server 4.0 (if you are working on Windows 95/98 or NT Workstation 4.0). The installation instructions are presented below.

2. Create a virtual folder (see the section following installation instructions).

0 0

Post a comment