ASP ScriptingJava ScriptingCGI ScriptingHTMLJavaLanguage CERP Education Links

Making an ODBC connection
Now we are going to make the connection that our ASP scripts are going to use when talking to the database we have created. Start by going to the Control Panel (Start -> Settings -> Control Panel). In the control panel you should find an icon called "32bit ODBC", it looks like this: Double click it and this window will pop up:  

Click the Add button. You will now see a list of different ODBC drivers, select "Microsoft Access Driver (*.mdb)" and click next. Now it asks for where to save the dsn file and what to call it. Click browse, go to c:\ and create a new directory called dsn. Save it as MyTable_dsn.dsn and click next (the path to the dsn file should now be c:\dsn\MyTable_dsn.dsn). Click finish. It now wants you to select which database to connect to. Click Select and find the mdb file you created with Access. It should be in the My Documents folder. Click OK twice. That's it!

Connecting to MyDatabase
To get information stored in a database with ASP you need to make a connection to the database through ODBC. You can look at it like phoning in to the database. To "dial up" our Access database with VBScript we use this piece of code:
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=c:\dsn\MyTable_dsn.dsn"
%>
We can now search the database with standard SQL query. SQL is a language used to send queries to databases (SQL=Structured Query Language). To retrieve the information from our database we can use this SQL query: SELECT * FROM Friends. Here's how we do this with VBScript:
<%
SQL_query = "SELECT * FROM Friends"
Set RS = MyConn.Execute(SQL_query)
%>
Now we have retrieved everything from the Friends table into a recordset called RS. A recordset is like an array holding all the data we selected from the table. To display it we will use a loop to display one friend at the time from the recordset. We want it to keep looping until there are no more friends left in the recordset. To check if there are any records left in the recordeset we use RS.EOF (EOF = End Of Stream), it is false if there are records left, and true if there is not. The code for displaying the recordset is:
<%
WHILE NOT RS.EOF
%>
<LI><%=RS("Name")%>: <A HREF="<%=RS("Link")%>">Homepage</A>
<%
RS.MoveNext WEND
%>
As you can see in the code above we use RS("Name of Field") to select the fields in the current record. Now that we are finished with the database and the recordset we have to close them. First we close the recordset with RS.Close and then we "hang up" the "phone call" to the database with MyConn.Close.