This following tutorial will show you how to connect, read, insert and update an database from an ASP page. The database created in this tutorial was done using MS Access 2000, however, it is possible to setup valid database using other applications. If you have any further queries setting up a database on our Windows NT/2000 servers please email support@ukhosts.com.
Creating a database in Access
Connecting/Disconnecting to a database in ASP
Reading data from a table
Updating columns in a table
Inserting rows into a table
Deleting rows from a table
Download tutorial database & test asp page
Creating a new database in MS Access
- Launch MS Access.
- Select the radio button "Blank Access Database".
- Type in the name you would like to call your database and click "Create". In this tutorial we
have named the database "MyDatabase.mdb".
You now need to create the tables you wish to use within your database.
- Double click on "Create table in Design view", this will open the design view for your table where you can enter field names and data types for the columns of your table.
Enter the "Field Name" and "Data Type" for each column of your table. The screenshot on the right is a copy of
the structure of the table used in this tutorial:
- Select the field you wish to set as the primary key (the "ID" field in this example) and click on the "Primary Key" icon
located in the toolbar.
- Select the "UpdateDate" field and enter "Date()" as it's default value.
- Click on the "Save" icon and type in your table name. In this tutorial we have named the table "MyTable".
- Optional: If you wish to increase the security of your database you can setup a password by going to Tools > Security > Set Database Password... in the toolbar, then entering your password. In this tutorial we have opted not to set a database password. *Please note, to set/change password you'll have to close and open the database as 'exclusive read only'.
- Finally close down Access.
Your database has now been created.
Connecting/Disconnecting to a database in ASP
- The following piece of code delcares 'Conn' as the connection object, this is used to connect to the database you wish to access. This
tends to be placed at the top of the page before connections to the database are made.
<%
set Conn = server.createobject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);PWD=DBPassword;DBQ="&Server.MapPath("MyDatabase.mdb")
%>
Code highlighted in bold are the elements that you need to edit in order to connect to your particular database. The first highlighted element "DBPassword" delcares the password you set for your database, if you have not set a password leave this blank. The second element "MyDatabase.mdb" declares where your database is held relative to the directory you execute this ASP page. - The next piece of code closes all connections to the database, freeing up the resources on the server. This is
generally placed at the bottom of the page after all connections to the database have been made.
<%
Conn.close
Conn=null
%>
Reading data from a table
- Returning all records from a table
The following piece of code reads in the columns specified and displays all records from that column.
<%
set rs=Conn.execute ("Select ID,Firstname,LastName From MyTable")
while not rs.eof
response.write("ID: "& rs("ID") &"<br>")
response.write("Firstname: "& rs("Firstname") &"<br>")
response.write("Lastname: "& rs("Lastname") &"<br>")
rs.movenext
wend
%>
The code sets "rs" as the record set that holds the executed query shown in italics. The query executed in this example selects the columns "ID", "Firstname" and "LastName" from the table "MyTable". The next few lines runs a while loop that writes out the results of the data held in the table to the page. This loop will run until the it reaches the end of the record set, once this occurs "rs.eof" will become "true" and the loop will stop executing. It is of vital importance that "rs.movenext" is present within your loop, otherwise the loop will carring on executing until the page eventually times out. The command "rs.movenext" is used to move the record set to the next record or row in the table. - Expanding on the "Select" query
It is possible to expand on the "Select" query used above to further define the records you wish to select from a table.
The following line of code expands on the statement by adding a "Where" clause.
"Select ID,Firstname,LastName From MyTable Where ID>=2"
This "Select" statement when executed will return a record set that contains all records that have an ID of 2 or greater.
To add further clauses to the "Where" statement you can use the "or" and "and" parameters.
"Select ID,Firstname,LastName From MyTable Where ID>=2 and ID<=4"
This above query will now returns all records that have an ID between 2 and 4.
When records are returned, they are usually returned in order of when they were written into the database unless an "Index" has been specifed. A further command can be added to your "Select" query to allow you to order he records returned in the record set. This can be done by adding the "Order By" command to your query.
"Select ID,Firstname,LastName From MyTable Order By Firstname"
This "Select" query will now return all records order alphabetically by "Firstname".
By default the order command will return the records ascendingly, however this can be reversed to decending by adding "Desc" to the "Select" query.
"Select ID,Firstname,LastName From MyTable Order By Firstname Desc"
This query will now return all records set in descending order relative to Firstname.
If you wish to combine both the "Where" and "Order By" commands in the same "Select" query you must make sure the "Where" command appears before the "Order By" command.
"Select ID,Firstname,LastName From MyTable Where ID>=2 Order By Firstname Desc"
This final "Select" query returns all records with an "ID" of 2 greater in order of "Firstname".
Updating columns in a table
- Executing an update querystring
The following peice of code executes an 'update' query in an ASP page.
<%
Conn.execute (" [update querystring goes here] ")
%>
Unlike the code used to execute a 'select' querystring, the 'update' querystring will not return a record set. - Updating individual records
Setting up a "Primary Key" field within your table that makes updating individual records easier, as each record can be identified by a unique number.
"Update MyTable set Firstname='Pascale', LastName='Clark' Where ID=2"
This query updates the table "MyTable" setting "FirstName" to "Pascale" and "Lastname" to "Clark" where "ID" is equal to 2. - Updating multiple records
This works much like the "Select" query when reading in multiple records into a record set. When updating multiple records it is important to make sure the query doesn't overwrite information you don't want updated, as mistakes can be easily made and cannot be undone.
"Update MyTable set Age=24 Where Age>25"
This query will update all rows that contain an "Age" of over 25 to 24.
Inserting rows into a table
- Executing an insert querystring
The following peice of code executes an 'insert' query in an ASP page.
<%
Conn.execute (" [insert querystring goes here] ")
%>
As with the 'update' query, the 'insert' query will also not return a record set. - Inserting a record
The following query will insert a single row with column values specified into the 'MyTable' table.
"Insert Into MyTable (Firstname, Lastname, Age) Values ('Joe','Bloggs',43)"
When inserting row into a table, columns that aren't specified within the query will either be set to 'null' or the default value for that column if specified. An example of this would be the ID column which doesn't need to be set, as the value set auto increments with every record inserted.
Deleting rows from a table
- Executing an delete querystring
The following peice of code executes an 'delete' query in an ASP page.
<%
Conn.execute (" [delete querystring goes here] ")
%>
As with the 'update' query, the 'delete' query will also not return a record set. - Deleting individual records
Setting up a "Primary Key" field within your table that makes deleting individual records easier, as each record can be identified by a unique number.
"Delete * From MyTable Where ID=2"
This query deletes the record that has "ID" equal to 2. - Deleting multiple records
This works much like the "Select" query when reading in multiple records into a record set. When deleting multiple records it is important to make sure the query doesn't delete rows you don't want deleted, as mistakes can be easily made and cannot be undone.
"Delete * From MyTable Where Age>25"
This query will delete all rows that contain an "Age" greater than 25.
