Display Data from Database ASP Tutorial - ASP Web Pro
  Active Server Pages Programming by ASP Web Pro

ASP Tutorials

DISPLAY DATA FROM DATABASE

Alright, you have your database connection established and now you want to display data from your database on your page. Easy enough. We'll take the same code that we left off with from our Open Database Connection Tutorial except we will change the database table name to myCustomers and build from there.

First, create a database with a table called myCustomers with the following fields and add some data:
Company - text field
Address - text field
City - text field
State - text field
Zipcode - number field

As a reminder, here is our open database connection code:

<%
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=myCONNECTION.dsn"
objConn.Open

DIM mySQL
mySQL = "SELECT * FROM myCustomers"

DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn
%>
<HTML>
<HEAD><TITLE>ASP Web Pro</TITLE></HEAD>
<BODY>
Display data from database here.
</BODY>

Now, if you know that you only have one customer in your database to display, it is very simple. Just paste the below code into the body section of your page:

<BODY>

<table>
<tr><td><% Response.Write objRS("Company") %></td></tr>
<tr><td><% Response.Write objRS("Address") %></td></tr>
<tr><td><% Response.Write objRS("City") %></td></tr>
<tr><td><% Response.Write objRS("State") %></td></tr>
<tr><td><% Response.Write objRS("Zipcode") %></td></tr>
</table>

</BODY>

<%
' Don't forget to close your connection after you display your data.
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

This will neatly display your customer's data on your page.

When displaying data from a database on your pages, it is a good idea to use tables to organize how the data will be displayed.

Now, ideally you will have more than one customer. If have multiple customers in your database, you need to do a little bit more

<BODY>

<table>
<% DO WHILE NOT objRS.EOF %>
<tr><td><% Response.Write objRS("Company") %></td></tr>
<tr><td><% Response.Write objRS("Address") %></td></tr>
<tr><td><% Response.Write objRS("City") %></td></tr>
<tr><td><% Response.Write objRS("State") %></td></tr>
<tr><td><% Response.Write objRS("Zipcode") %></td></tr>
<tr><td><hr></td></tr>
<%
objRS.MoveNext
Loop
%>
</table>

</BODY>

<%
' Don't forget to close your connection after you display your data.
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

In the first example, our page displays the first and only record returned by our mySQL statement. In this example, the <% DO WHILE NOT objRS.EOF %> and <% objRS.MoveNext...Loop %> code instructs our page to display the first record returned by our mySQL statement, move to the next record, and loop back and continue the table and display the next record until there are no more records left.

So what happens if you have 100 customers and it is just too much information to display on one page? You would probably just want to display the first 10 or 20 records on your page and include a next and previous link at the bottom so that you can scroll through your records more easily. We will get into that later, but for now we will let you absorb this. Congrats!

< Back to Tutorials

 

 

Main Menu
Home
ASP Scripts
ASP Tutorials
JavaScripts
Awards
Contact Us

Email This Page

Other Resources
ASP Web Hosting
Search Engine Submission
Programming Help

 

 

 

Other ASP Web Sites

Site Map

 

Last Updated:
04 February 2012

 

© 1997-2012 ASP Web Pro, Inc. | Terms of Use