Display Data from Database

Alright, you have your database connection established and now you want to display data from your database on your web 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 tblCustomers.

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

Next, create a page called /Display.asp and begin with 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 tblCustomers"

DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn
%>

Display data from database here.

 

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

<% Response.Write objRS("Company") %><br>
<% Response.Write objRS("Address") %><br>
<% Response.Write objRS("City") %><br>
<% Response.Write objRS("State") %><br>
<% Response.Write objRS("Zipcode") %>

<%
' 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.

 

Now, ideally you will have more than one customer.  If you have multiple customers in your database to display, you only need to add a little more code.

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

' 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, loop back, and continue to display the next record until there are no more records found.

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.

< Back to ASP Tutorials