Ready to move on to a more exciting example? We will now write a simple ASP script that will display all of the book titles that are in the Titles table of the Biblio.mdb file. The real benefit to us, the database programmer, is to be able to use ADO for retrieving data and making it available over the Internet. Here is what the result of our next program will look like in your browser:
1. OK, get out your trusty Notepad.exe program and enter the following code. When you are finished, save the file as Simple.asp in the \Chapter13 directory:
<HTML>
<HEAD>
<TITLE>Database Programming with Visual Basic 6.0</TITLE>
</HEAD>
<BODY>
<CENTER>
<H1><FONT size=4>
Using ADO in a Visual Basic Script Web Page
</FONT></H1>
<H2>Database Programming with Visual Basic 6.0</H2>
<HR>
<! Begin server side script here>
<%
dim myconnection
dim rsTitleList
set myConnection = Server.CreateObject("ADODB.Connection")
myconnection.open "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\begdb\biblio.mdb"
SQLQuery = "SELECT title FROM titles"
set rsTitleList = myConnection.Execute(SQLQuery)
do until rsTitleList.eof
Response.Write rsTitleList("Title") %>
<BR>
<%
rsTitleList.movenext
loop
rsTitleList.close
set rsTitleList = nothing
%>
<! end server side script>
<HR>
</CENTER>
</BODY>
</HTML>
2. After you enter the code in Notepad, ensure that you save the file as Simple.asp to the \Chapter13 directory. Now, in your browser, type in the name of the file in your address field, suing the alias for the Chapter13 directory. You will see the results in your browser, similar to this:
That's it. Now let's take a closer look at what is going on here. The first part of our file is pure HTML:
<HTML>
<HEAD>
<TITLE>Database Programming with Visual Basic 6.0</TITLE>
</HEAD>
<BODY>
<CENTER>
<H1><FONT size=4>
Using ADO in a Visual Basic Script Web Page
</FONT></H1>
<H2>Database Programming with Visual Basic 6.0</H2>
<HR>
This part of the file should be second nature to you now. We are just using standard HTML tags. We display two headers, H1 and H2, then delimit the headers with a horizontal rule command, <HR>. This command draws a horizontal line across the browser.
Next we have:
<! Begin server side script here>
This is just a comment. We enclose comments in <! >. However, since the comment is not embedded in the VBScript brackets, it gets sent to the client. Here the source is viewed as it was received at the client browser. Notice the comment:
So here we are just making a note in the source code that the server side scripting is starting next.
Here's the next part of the functional code:
<%
dim myconnection
dim rsTitleList
set myConnection = Server.CreateObject("ADODB.Connection")
myconnection.open "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\begdb\biblio.mdb"
SQLQuery = "SELECT title FROM titles"
set rsTitleList = myConnection.Execute(SQLQuery)
OK, look familiar? Well, it is the same ADO syntax we used earlier to open a connection and then a recordset. We are just using the .Execute method of the connection object to create the recordset. The same ADO commands you used earlier in the book can be used here. We build an SQL query requesting all titles form the Titles table. The result will be placed in our recordset rsTitleList. This part of the code is considered VBScript. In fact, anything between the <% and %> brackets is VBScript, and is treated specially by our PWS server, because it 'speaks ASP'. And the server must be 'Active Server Page ready' (for example, running PWS, as we are) to be able to understand VBScript. Otherwise, you would just see the VBScript right on the web page.
Now we loop through the recordset normally. Remember that the <% from earlier is in effect. So we are still executing VBScript here:
do until rsTitleList.eof
Response.Write rsTitleList("Title") %>
<BR>
<%
rsTitleList.movenext
loop
rsTitleList.close
set rsTitleList = nothing
%>
As in the earlier example, we just use the .Write method of the Response object to send the value of the field "Title" to the browser. When the current record is printed, we call the .movenext method of the rsTitleList recordset and print out the next record. When the recordset has been traversed, we just close the recordset and set it to nothing. With the exception of enclosing the VBScript commands in <% %>, there is not much difference between VBScript and VB syntax.
<! end server side script>
<hr>
</CENTER>
</BODY>
</HTML>We wrap things up by adding a comment and a horizontal rule line.
Let's now consider how ASP, ADO and HTML are interrelated.