Let's say that we wanted to show the user all of the books published by Wrox Press. We will create a page called wrox.asp. The .asp extension tells the browser that this is an Active Server Page. We also might want to make the output a bit more fancy. So we will have to use HTML to do that. We can use simple VBScript and ADO to create the recordset, but will need HTML to send it to the browser in a formatted way. So take a look at this screen:
This screen is the result of the ASP page we will create next. Ready? We will create an ASP page by using a combination of VBScript and HTML for accessing and displaying an ADO recordset! How's that for alphabet soup of technologies? And the best part of all is that you understand what the acronyms mean and how they are used.
Now lets look at the code that generated the page in the screenshot above. Open your trusty Notepad.exe and get to work!
1. Create a new .asp file called Wrox.asp and save it in the \Chapter 13 directory. Key the following code into this file:
<HTML>
<TITLE>Database Programming with Visual Basic 6.0</TITLE></HEAD>
<CENTER>
<H1><font size=4>Using ADO in an Active Server Page</H1></font>
<H2>Database Programming with Visual Basic 6.0</H2><br>
<%
dim myConnection
dim connectString
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" & "Data Source=C:\begdb\biblio.mdb"
Set myConnection = Server.CreateObject("ADODB.Connection")
Set RSTitleList = Server.CreateObject("ADODB.Recordset")
myConnection.Open connectString
Set RSTitleList = myConnection.Execute( "Select * From titles WHERE PubID = 42") %>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- Begin our column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Publisher
ID</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Title
</FONT>
</TD>
</TR>
<!-- Ok, let's get our data now -->
<% do while not RStitleList.EOF %> <TR>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RStitleList("PubID")%></font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Title") %> </font> </TD> </TR>
<% RSTitleList.MoveNext%>
<%loop %>
</TABLE>
</CENTER>
</BODY>
</HTML>Be sure to save the file. Now open the file in your browser and view the results.
We start by setting up our HTML page as before. Notice that the <BR> is the last HTML tag here. We have placed it on the same line to show you that white space is not important. You can place the tags anywhere - it is the sequence of tags that is important to the browser:
<HTML>
<TITLE>Database Programming with Visual Basic 6.0</TITLE></HEAD>
<CENTER>
<H1><font size=4>Using ADO in an Active Server Page</H1></font>
<H2>Database Programming with Visual Basic 6.0</H2><BR>
Next, we start our VBScript to get the data from the database. Since the block of code below is all VBScript, we enclose the entire thing in <% %> brackets:
<%
dim myConnection
dim connectString
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\begdb\biblio.mdb"
Set myConnection = Server.CreateObject("ADODB.Connection")
Set RSTitleList = Server.CreateObject("ADODB.Recordset")
myConnection.Open connectString
Set RSTitleList = myConnection.Execute( "Select * From Titles WHERE PubID = 42") %>
There is nothing special here. We are just building a connection string and then setting reference variables to a new connection and a recordset. The connection is then opened and we execute the SQL statement that retrieves all of the titles from the Titles table where the PubID = 42. In our database, the PubID for Wrox Press is 42. The result of the query is placed in the rsTitleList recordset.
Now, on to the scary HTML
Well, this stuff does look a bit scary. But we are just creating a table in HTML and displaying the information in various colors. It's interesting how the ADO code is second nature to us, but if you have not seen HTML before, it might look just a bit strange. Well, here we are just creating a table, which contains tabular data. We want the items centered in the table. The border attribute tells the browser to draw a border, but our output looks good without one:
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
Next we set up our headers to display the string literal "Publisher ID" in the first column and "Title" in the second. The Table Row <TR> tag defines the end of a row in the table and the start of a new one. Once we have a new row, we add data using the Table Data <TD> tag. Within the tag, we give it background color attributes, font size, and font color. Then we provide the data, such as "Publisher ID" to be displayed. Next, the Table Data is delimited by </TD> for both the first and second cells that make up the header:
<!-- Begin our column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Publisher ID
</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Title
</FONT>
</TD>
</TR>
And we finish off the row of two header cells above with the </TR> tag, telling the browser that this row is complete. So now that we have our fancy header displayed, let's grab the data from the recordset and output it to the browser:
<!-- Ok, let's get our data now -->
<% do while not RStitleList.EOF %> <TR>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RStitleList("PubID")%></font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Title") %> </font> </TD> </TR>
<% RSTitleList.MoveNext%>
<%loop %>
</TABLE>
The do while looks familiar. Then we start another table row using the HTML tag <TR>. As above, we add table data using the <TD> tag. We simply provide a background color and tell the browser to center the data field within the cell. Once we format the cell the way we want, we output the recordset field. For the first cell in this row, we output the "PubID" field. And notice the tag <%= below. This is a special variation of the <%, which tells the browser to output the value immediately:
<%=RStitleList("PubID")%>
Then we do the same with the second row. Once we output the Title field, we delimit the row with a </TR>. We then use the VBScript command to move to the next record and then loop to display that record. Finally, when the end of the recordset is reached, we end the table with the </TABLE> tag. We then finish off the script file in the usual way, by ending the Center, Body, and HTML commands tags:
</CENTER>
</BODY>
</HTML>So I think you will agree that with this example the ADO part was easy. It's getting the HTML right that causes us the headache here. But I think you can now see how Active Server Pages work and how elegant a solution it is. By being able to blend standard HTML tags with VBScript commands, the final output is straight HTML. As we discussed, any and every browser on the planet can render HTML. No plug-ins or latest versions required. A very nice solution.