Lesson 19: Get data from database
In the previous lessons, we have learned to create a database in Access and to make a database connection. Now it is time to retrieve content from the database to our ASP pages.
This is really one of the most important lessons in this tutorial. Once you have read and understood this lesson, you will realize why database-driven web solutions are so powerful, and your views on web development will be expanded dramatically.
SQL queries
To retrieve data from a database, you use queries. An example of a query could be: "get all data from the table 'people' sorted alphabetically" or "get names from the table 'people'."
To write such queries, you use the language Structured Query Language (SQL).
Fortunately, SQL is very easy to learn. Try looking at this simple example:
Get all data from the table 'people'
SELECT * FROM people
The syntax is pretty self-explanatory. Just read on and see how SQL statements are used in the examples below.
Example 1: Retrieve data from a table
This example uses the database from lesson 17 and the database connection from lesson 18. Therefore, it is important that you read these lessons first.
The example shows how data in the table "people" is retrieved with a SQL query.
The SQL query returns a result in the form of a series of records. These records are stored in a so-called recordset. A recordset can be described as a kind of table in the server's memory, containing rows of data (records), and each record is subdivided into individual fields (or columns).
A recordset can be compared to a table, as you know it from the Datasheet View in Access:
Viewed this way, each record can be compared to a row in the table. You can move around in a recordset using the methods MoveNext (go to next record), MovePrevious (go to previous record), MoveFirst (go to the first record), and MoveLast (go to the last record).
The code below shows how to use a recordset:
<html> <head> <title>Retrieve data from database </title> </head> <body> <% ' Database connection - remember to specify the path to your database Set Conn = Server.CreateObject("ADODB.Connection") DSN = "DRIVER = {Microsoft Access Driver (*. mdb)}; " DSN = DSN & "DBQ=" & Server.MapPath ("/cgi-bin/database.mdb") Conn.Open DSN ' SQL Query strSQL = "SELECT * FROM people" ' Execute the query (the recordset rs contains the result) Set rs = Conn.Execute(strSQL) ' Loop the recordset rs Do ' Write the value of the column FirstName Response.Write rs("FirstName") & "<br />" ' Move to next record in rs rs.MoveNext ' Continue until end of recordset (EOF = End Of File) Loop While Not rs.EOF ' Close the database connection Conn.Close Set Conn = Nothing %> </body> </html>
Notice that for every record how we get the content of the column "FirstName" by typing rs("FirstName"). Similarly, we can get the content of, for example, the column "Phone" by writing rs("Phone").
The order of the recordset is exactly the same as in the table in the database. But in the next example, it will be shown how to sort recordset.
Example 2: Sort the data alphabetically, chronologically or numerically
Often it can be helpful if a list or table of data is presented alphabetically, chronologically or numerically. Such sorting is very easy to do with SQL, where the syntax Order By ColumnName is used to sort according to the column contents.
Look at the SQL statement from the example above:
strSQL = "SELECT * FROM people"
The records can, for example, be sorted alphabetically by the first name of the people this way:
strSQL = "SELECT * FROM people ORDER BY FirstName"
Or chronologically by date of birth like this:
strSQL = "SELECT * FROM people ORDER BY BirthDate"
The sorting can be charged from ascending to descending by adding DESC:
strSQL = "SELECT * FROM people ORDER BY BirthDate DESC"
In the following example the people are sorted by age:
<html> <head> <title>Retrieve data from database </title> </head> <body> <% ' Database connection - remember to specify path to your database Set Conn = Server.CreateObject("ADODB.Connection") DSN = "DRIVER={Microsoft Access Driver (*. mdb)}; " DSN = DSN & "DBQ=" & Server.MapPath("/cgi-bin/database.mdb") Conn.Open DSN ' SQL Query strSQL = "SELECT * FROM people ORDER BY BirthDate DESC" ' Execute the query (the recordset rs contains the result) Set rs = Conn.Execute (strSQL) ' Loop the recordset (rs) Do ' Write the contents of the columns FirstName and BirthDate Response.Write rs("FirstName") & " " & rs("BirthDate") & "<br />" ' Move to the next record in rs rs.MoveNext ' Continue until end of recordset (EOF = End Of File) Loop While Not rs.EOF ' Close the database connection Conn.Close Set Conn = Nothing %> </body> </html>
Try to change the SQL statement yourself and sort the records by first name, last name or phone number.
Retrieve selected data
Until now, our SQL statement retrieves all rows from the table. But often you need to set criteria in the SQL query for the data to be retrieved. For example, if we only want the rows for those who had a particular phone number or a certain last name.
Say, we only want to retrieve people from the database who hav the phone number "66554433". That could be done like this:
strSQL = "SELECT * FROM people WHERE Phone = '66554433 '"
There are six relational operators in SQL:
= Equals
< Less
> Greater Than
<= Less than or equal to
> = Greater than or equal to
<> Not equal to
In addition, there are some logical operators:
AND
OR
NOT
See lesson 6 for more information on how to set up conditions.
In the next example, we use conditions to set up a address book.
Example 3: Address book
In this example we will try to combine many of the things you have just learned. We will make a list of the names from the database where each name is a link to further details about the person.
For this, we need two files - liste.asp and people.asp - with the following code:
The code of list.asp
<html> <head> <title>Retrieve data from the database</title> </head> <body> <ul> <% ' Database connection - remember to specify path to your database Set Conn = Server.CreateObject("ADODB.Connection") DSN = "DRIVER={Microsoft Access Driver (*. mdb)}; " DSN = DSN & "DBQ=" & Server.MapPath("/cgi-bin/database.mdb") Conn.Open DSN strSQL = "SELECT * FROM people ORDER BY FirstName DESC" Set rs = Conn.Execute (strSQL) Do ' Name of the person strNavn = rs("FirstName") & " " & rs("LastName") ' Create a link to person.asp with the Id-value in the URL strLink = "<a href = 'person.asp?id = " & rs("Id") & "'>" & strNavn & "</a>" 'List link Response.Write "<li>" & strLink & "</li>" rs.MoveNext Loop While Not rs.EOF Conn.Close Set Conn = Nothing %> </ul> </body> </html>
The code for person.asp
<html> <head> <title>Retrieve data from database</title> </head> <body> <dl> <% ' Database connection - remember to specify the path to your database Set Conn = Server.CreateObject("ADODB.Connection") DSN = "DRIVER={Microsoft Access Driver (*. mdb)}; " DSN = DSN & "DBQ=" & Server.MapPath("/cgi-bin/database.mdb") Conn.Open DSN ' Get data from the database depending on the value of the id in the URL strSQL = "SELECT * FROM people WHERE Id =" & Request.QueryString("id") Set rs = Conn.Execute(strSQL) ' Write the data of the person Response.Write "<dt>Name:</dt><dd>" & rs("FirstName") & " " & rs("LastName") & "</dd>" Response.Write "<dt>Phone:</dt><dd>" & rs("Phone") & "</dd>" Response.Write "<dt>Birthdate:</dt><dd>" & FormatDateTime(rs("BirthDate"), vbLongDate) & "</dd>" Conn.Close Set Conn = Nothing %> </dl> <p><a href="list.asp">Return to list</a></p> </body> </html>
The address book example is rather simple, but it shows the potential of working with ASP and databases.
Imagine that the database had contained 10,000 products with detailed descriptions. By making a few changes in the above files, you could easily create a product catalogue with more than 10,000 pages with only one database and two ASP files.
Welcome to a world with extensive websites that are easy to develop and maintain. Once you've learned to work with databases, your web solutions will never be the same again.