Home Web Programming ASP Reading a Database

Reading a Database

Author: Wilfried Schobeiri More by this author

Added: Apr 14, 2005 Rating:

Level: Beginner Software: n/a

Learn how to display data from a Database.

Displaying data from a database is one of the most common uses of ASP.
Here's some sample code:

<%
Dim Conn
Dim SQLTemp

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.connectionstring = 
	"Provider=Microsoft.Jet.OLEDB.4.0;Data 
	Source=" & Server.MapPath("\yourdb.mdb")& ";" 
Conn.Open 

SQLTemp = "SELECT * FROM yourTable"
set rstemp=Conn.execute(SQLTemp)

Do While Not rstemp.EOF
%>
<%= rstemp.Fields("pID").Value %>: 
<%= rstemp.Fields("pSubj").Value %>, 
<%= rstemp.Fields("pDate").Value %>, 
<%= rstemp.Fields("pText").Value %>
<br>
<%
rstemp.MoveNext
Loop

Conn.Close
Set Conn = Nothing
%>

That was a simple select query.
Here it is broken down: The first two lines declare the variables Conn and SQLTemp. The next six lines open a connection to your database, and get all the rows from the table. The following Do While Not loop lets u display the data inside the row. The next four lines print out the value of each column in that row. The last bit moves to the next row, ends the loop, and closes the connection to the database.

If you decide you want the data ordered by date, you can use the following code in place of the original SQLTemp:

SQLTemp = "SELECT * FROM yourTable ORDER BY pDate"

Adding DESC or ASC to the end of the query as such:

SQLTemp = "SELECT * FROM yourTable ORDER BY pDate DESC"

will change the order the data is sorted in.

Not too hard, was it? The last bit we'll cover is how to limit the results, and here we'll limit it to showing only the first 30 results:

SQLTemp = "SELECT * FROM yourTable LIMIT 30"

Any combination of these queries can be mixed and match to achieve results you want.

NOTE: Had trouble connecting to the database? Click here.

Author's URL: Wilfried Schobeiri