ASP Tutorial
How do I select data from a database?
Selecting data is almost identical to inserting data, except that rather than a
form and a script to add the data to the database, you're using a single script
to select and print data from the database. The select statement is below.
Remember that when you select information from a database, you will need to
think logically before displaying the page. If you database has 1,000 records,
it would probably be a good idea not to use paging from page to page, or not to
display all the records at one time. In this example, we will only show how to
display the entire contents of the database, and not worry about paging.
Please review the terms below. These terms will be used in this section.
| select * from table |
Example of a select statement
without a conditional. |
| |
|
| select * from table where
column='something' |
Example of a select statement
with a conditional. |
| |
|
| select * from table where
column LIKE '%something%' |
Example of a select statement
with a conditional, which uses the "LIKE" statement. You'll see that the
percent signs are like wild cards. It will find anything in the column that
has the word something in it, even if there is text before it or after it.
The percent signs can be used on either side of the data being selected. |
| |
|
Do while not rs.eof
...
...
rs.MoveNext |
Loop This is an example of a
conditional loop. In this example, as long as the record set is not at the
end of the file (eof), it will continue to process the instructions.
|
To execute the following example script below, you
will need to create either a MS Access, or MS Excel database, and create a DSN.
Here is a short description of what the database should look like. In this
example, we will use MS Access.
Table name: example
| Field |
Data Type |
| ID |
AutoNumber |
| Name |
Text
|
| Email |
Text
|
| Phone |
Text
|
Input a few lines of data into the table, if you
haven't already. We'll say that 5 or 6 entries are sufficient. If you've gone
through each chapter, then you've probably already created this DSN, and
database. Again, if you haven't already done so, create a DSN named 'example'
using your Windows Control Panel. Create the script with the name
select_data.asp. The code to create the script is below.
------------- Copy the following into select_data.asp -------------
<HTML>
<HEAD>
<TITLE>Selecting Data Script</TITLE>
</HEAD>
<BODY>
<%
set rs=Server.CreateObject("adodb.Recordset")
' Set the DSN
connect="DSN=example"
' Designate your SQL statement
sqlstmt = "SELECT * from example"
' Connect the record set with the DSN and the SQL statement
rs.open sqlstmt, connect
' This area is a logical error routine. If there
' is no data in the database (the rs.eof or record set end of file),
' then display that there are no records in the database.
' Else, you're going to display the script as it's written
If rs.eof then
response.write "There are no records in the database<br><br>"
response.end
Else
' This is an example of a loop. The "Do" statement
' matches up with the "Loop" at the end of this script. It is associated
' with the do as it is a conditional loop. While the file is not
' at the end, continue to do the following.
Do while not rs.eof
Name = rs("Name")
Email = rs("Email")
Phone = rs("Phone")
%>
The person's name is: <%=Name %> <BR>
The person's email address is: <%=Email %> <BR>
The person's phone number is: <%=Phone %> <BR>
<HR>
<%
rs.MoveNext
loop
End If
%>
<%
' This is the same error routine used in the last chapter.
If err.number>0 then
response.write "VBScript Errors Occured:" & "<P>"
response.write "Error Number=" & err.number & "<P>"
response.write "Error Descr.=" & err.description & "<P>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"
End If
If conn.errors.count> 0 then
response.write "Database Errors Occured" & "<P>"
response.write SQLstmt & "<P>"
for counter= 0 to conn.errors.count
response.write "Error #" & conn.errors(counter).number & "<P>"
response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
Next
Else
End If
%>
</BODY>
</HTML>
