Accessing database from an ASP page

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'.

Table1 Table

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.

'names' table

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.

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'.

Table1 Table

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.

'names' table

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.