Databases are a way of organizing and keeping your data. The data stored in databases can be anything from user email addresses to binary files. Databases have become so popular in the past decade that it is almost unimaginable to not to use them on the web.
In this tutorial I will guide through the creation of a simple Microsoft Access database to incorporating it in to your ASP web pages. Creating and making use of a database on the web is so very much easy that it will be only after reading this article you will realize the same and will then hopefully start creating databases according to your own needs and then playing with them from the web pages.
Requirements
You are required to have Microsoft Access database ( any version will do the trick, 97, 98 2000 ), MDAC 2.0 or above ( latest is MDAC 2.5 ), either PWS 4 or IIS 4.0, Windows platform and a web browser. Don't worry if you don't know about MDAC ( Microsoft Data Access Components ), you can check if you have already got them by going to Start -> Settings -> Control Panel. There you will find a small icon 'ODBC 32'. If you can see the icon then you have got MDAC but if you cannot find the icon then you will most probably have to download them from Microsoft's site. If you are running Windows2000 Professional then you can find the "Data Sources ODBC" icon in the "Administrative Tools" section of the control panel. For displaying our database contents on the web browser we will be using Microsoft's Active Server Pages technology. For that either PWS 4 or IIS 4 ( or above ) will be required. Both of them are free and can be downloaded from www.microsoft.com.
As you would have most probably guessed by now, I am assuming that you are a newbie and don't know much about this stuff. So if you have got what it required ( above ) then we are ready to move on to the tutorial.
In the next few pages we will create a simple Access database and add some content to it, then create a ODBC System DSN for it and show the contents of that database on our web page. The tutorial is pretty much simple and you will learn a lot from it, so I advise you to go through the next pages one by one and complete each page's tasks. Good luck!
Ok, we begin by creating a simple Access database.
Step 1 : Start Microsoft Access by clicking 'Microsoft Access' icon in the Program Files menu. Start -> Program Files -> Microsoft Access.
Step 2 : Microsoft Access will start with default windows opening up at start up. Click 'cancel' to exit any windows that appear.
Step 3 : Click the File -> New button at the top left main windows of Access. This will bring up a 'New' dialog box window. Of the two tabs click the 'General' tab and then the 'Database' icon. This will select 'Database', then hit the 'OK' button.
Step 4 : This will bring up 'File New Database' dialog box. It will ask for the database name and location to store that database to. Type 'odbc_exmp' in the 'File Name' input box and give it any location to store that database to. For this tutorial we will assume that our database 'odbc_exmp.mdb' was saved at c:/stardeveloper/db/odbc_exmp.mdb . Then hit the 'Create' button.
Step 5 : Our database 'odbc_exmp.mdb' is now created. But it is empty and we will need to populate it a bit so that we can later use it. In the Microsoft Access, you will now be seeing a 'odbc_exmp : Database' dialog box showing quite a lot of options on the left column and three options in the right column. Double click the 'Create table in Design view' option in the right column.
Step 6 : This will bring up 'Tabe1 : Table' dialog box. Just in case if you don't know, data is stored in tables in a database. There can be many tables within one database. Tables in turn consist of Fields ( columns ) and rows ( records ). Fields ( columns ) do not accept accept data of all type. We have to specify the data type that a Field ( column ) will hold and then we can add records for that data type in the rows. It is this 'Design View' in Microsoft Access that is used to specify the number of columns our table will have and what data type that Fields ( columns ) will hold. Ok now type the 'Field Names' and 'Data Types' exactly as shown below. Note that you can select 'AutoNumber' and 'Text' from the drop down options in the 'Data Type' column as required. There is no need to edit any values in the 'General' and 'Lookup' tabs in the 'Field Properties' section of the 'Table Design View'. Now click the File -> Save button. A 'Save As' dialog box will prompt you to enter the name for this table, type 'names' in that dialog box and hit 'OK'.
Step 7 : Close the 'names' table design view window. Now you will see 'names' table being added to the right column of the 'odbc_exmp : Database' window. Double click the 'names' table. This will bring up the 'names : Table' window showing an empty row and three columns with 'Field Names' which we specified earlier. It is used to add data to the table. We will add five names to our 'names' table. There is no need to add anything to the 'id' Field as it will autoincrement one number upon the addition of records to the rows one by one. If you don't understand what I mean by autoincrementing then just leave this field for a moment and you will come to know what it does later when we add records. Ok now add five names ( first, last ) in the empty row under their respective Field Names as shown below.
See the numbers in the 'id' Field. Thats what autoincrement does. It adds the numbers in a sequential way. Now hit the 'save' button to save the records which we have added in our 'odbc_exmp.mdb' database. This completes our task of creating a simple Access database.
You have just seen that how easy it is easy to create a database. You have also learned what are tables, rows and columns. You have also learned what 'Data Types' are and how to specify a 'Data Type' in the table column. You have also added records to the database. Now we will move forward and will register our database in the System registry by assigning it a Data Source Name ( DSN ). Well done, now continue to the next page.
DSN stands for Data Source Name. Data source can be a database, spreadsheet, text file etc. We assign DSN to a data source so that irrespective of the data source details and location, we can use that data source; add, modify or delete records, just by knowing it's DSN.
To assign DSN to our 'odbc_exmp.mdb' database, follow the steps below :
Step 1 : Open 'Control Panel' ( Start -> Settings -> Control Panel ). Double click the 'ODBC 32' icon. If you are running Windows2000 then double click the 'Administrative Tools' icon in the 'Control Panel' and then double click the 'Data Sources (ODBC)' icon. If you cannot find the 'ODBC 32' or 'Data Sources (ODBC)' icon then please see the discussion at the start of this tutorial.
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 :
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 :
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.
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 tags. Every thing inside the 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.
