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



Display data from database here.


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:

<% Response.Write objRS("Company") %>

<% Response.Write objRS("Address") %>

<% Response.Write objRS("City") %>

<% Response.Write objRS("State") %>

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



<% DO WHILE NOT objRS.EOF %> <% objRS.MoveNext Loop %>

<% Response.Write objRS("Company") %>

<% Response.Write objRS("Address") %>

<% Response.Write objRS("City") %>

<% Response.Write objRS("State") %>

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

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!