Accessing database from an ASP page

Step 2 : By double clicking the 'ODBC 32' or 'Data Sources (ODBC)' icon on Windows2000 a window 'ODBC Data Source Administrator' will appear. It will contain many tabs on the top e.g. User DSN, System DSN, File DSN and so on. As far as ASP ( Active Server Pages ) are concerned, we will use System DSN. Click the 'Add' button. A window will appear like below :

Create a new Data Source

Step 3 : Select the Microsoft Access Driver (*.mdb) from the list and hit the 'Finish' button.

Step 4 : You will now see another dialog window asking you the location and name of your new Microsoft Access database. In the 'Data Source Name' field type 'odbc_exmp' and then hit the 'Select' button. Now browse to the location where you have saved the 'odbc_exmp.mdb' database we created earlier. Once you find the location select the 'odbc_exmp.mdb' name and then hit ok. You will eventually see 'ODBC Microsoft Access Setup' dialog box like below :

ODBC Microsoft Access Setup

Click the 'OK' button in the window above. You will now get back to your System DSN windows. You will now see odbc_exmp added to your System DSN list. We have successfully created a System DSN for our 'odbc_exmp.mdb' database.

In this chapter you saw how easy it is to assigning DSN to a database. In the next chapter we will create a simple .asp page in which we will show the contents of our 'odbc_exmp.mdb' database.

We have created a database and assigned it a DSN, we will now show our database's contents on our web page using Microsoft's Active Server Pages technology. Active Server Pages or simply called ASP are pages which use a server side scripting language e.g. VBScript, to bring dynamic content to a web page. All of the processing is done on the server side and then output is generated like an ordinary HTML page which any browser can understand and view. We will not go into details of ASP in this tutorial but will only touch those parts of ASP which will help us understand how to access a database from ASP.

Step 1 : Open Notepad ( Start -> Program Files -> Accessories -> Notepad ). Copy the code below and paste it into your Notepad. Don't worry if you cannot understand what this code is doing. I'll explain that in a moment, for now just copy all the code below to your Notepad.

<% Response.Buffer = True %>

<html>
<head>
<title>Stardeveloper.com Database Tutorial</title>
</head>
<body>

<%
	Dim rs
		Set rs = Server.CreateObject ("ADODB.Recordset")

	rs.Open "names", "DSN=odbc_exmp"

	While Not rs.EOF
		Response.Write "ID : " & rs("id") & "<br>"
		Response.Write "First Name : " & rs("first_name") & "<br>"
		Response.Write "Last Name : " & rs("last_name") & "<br>"
		Response.Write "<br>"

		rs.MoveNext
	Wend

	rs.Close
	Set rs = Nothing
%>

</body>
</html>

Step 2 : After pasting the above code into your Notepad, save this page as 'odbc_exmp.asp' and give it any location where you can run .asp pages, usually in PWS/IIS that location is c:/Inetpub/wwwroot/ .After saving the file and giving it above location you can see it in your browser.

Step 3 : Start the PWS ( or IIS ) if it's not already running. Now open your favorite browser and type the following in your URL box of your browser :

http://127.0.0.1/odbc_exmp.asp

Note that above URL will only work if you have saved the 'odbc_exmp.asp' file at c:/Inetpub/wwwroot/ ( or where ever your wwwroot directory is present ). If you have put it in a 'temp' directory e.g. c:/Inetpub/wwwroot/temp/ then the URL to put in your browser URL box will be 'http://127.0.0.1/temp/odbc_exmp.asp'. Ok after putting the above URL in your browser URL box hit enter. If all id done right you will see a list of first and last name along with their IDs of all the entries ( five ) we made in our 'odbc_exmp.mdb' database. If you can see the following you are done.

ID : 1
First Name : Faisal
Last Name : Khan
ID : 2
First Name : John
Last Name : Lee
ID : 3
First Name : David
Last Name : Doshambey
ID : 4First Name : Marvin
Last Name : DeboyID : 5
First Name : MichaelLast Name : Chang

If you know some HTML then you would have guessed that in our code ( see above ) all of the tags are simple HTML tags except <% and %> tags. Every thing inside the <% and %> tags is the ASP code. We will now simply touch the ASP code we wrote to help you understand how we were able to output database content on our web page.

Explanation of above Active Server Pages - ASP code

<% Response.buffer = true %>

Above line indicates to the server that only when the execution of the whole page is complete then sent the content to the client browser. Setting this value to 'false' will mean that the content will be send to the client browser as soon as the page execution starts. This tag actually plays no part in our database tutorial so just ignore it.

<%  Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")

We create a variable 'rs' and then initilize it to point it to 'ADODB.Recordset' component. It is this 'ADODB.Recordset' component which we are using to display the contents of our database.

rs.Open "names", "DSN=odbc_exmp"

We then use the 'Open' method of our 'rs' object ( which points to ADODB.Recordset component ) to open our database. We give it two arguments, "names" for the name of the table in our database and "DSN=odbc_exmp" for the name of DSN to use. As you can see you can create your own databases and then use the 'Open' method to open any table in that database. Note that 'Open' method can take a lot more arguments but we will not discuss that here as my aim is just to show you how easy it is to incorporate databases into your web site.

While Not rs.EOF .... Wend