Selecting from a Database

You understand the basics of using ASP, including loops and requests. Now you want to use this knowledge to select information out of a database. Here's how you do it!

In essence, you create an "object" which is your communication with the database. You tell this object what database you want to connect to, what command you wish to give it, how you wish it to connect and so on. Then you tell that object to EXECUTE. That actually does the database work. When it is done, you can then get information from the object that contains the results.

Let's take a common application - a banner rotation. Let's say you have a database table called "ads" which has in it the various banner URLs and image URLs you want to rotate on your site. Let's say you want to show two on a page - you want to show the 2 that have so far been shown the least number of times on your site.

First, your code should always start with the basic database connection information, which you store elsewhere so you dont' clutter up all of your code with it. You normally get these files from whoever set up your ASP system for you.




Now your database connection information is defined, and your 'commonly used values' are defined as words so you can easily use them. You're ready to do your database query. Here is a select statement for ad rotation:

Set AdStr = Server.CreateObject ("ADODB.Recordset")
SQLText = "SELECT ad_code, ad_id from ads order by hit_count ASC"
AdStr.Open SQLText, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
if not AdStr.EOF then
AdCode = AdStr("ad_code")
AdID = AdStr("ad_id")
end if
AdStr.Close()
Set AdStr = Nothing

The sample only gets one ad, but you can get as many ads as you wish. Here is what the code is doing:

Defines the database object, AdStr. You always have to define something before you can use it!
Defines the SQL text that will be used by this object. Always test your SQL text for errors!
Opens the object. This initializes the connection and lets you start getting records. Note that you give the object the SQL text, the connect string (which comes from DataStore.inc), the command that this is a forward-only cursor, that you're only reading data.
The IF statement is just in case there is no data in that table. If you try to do operations on a table with no data, you'll get nasty errors in your code.
The next two lines get the values for the first row from the ad_code and ad_id columns. It stores them in variables for later use.
The IF statement ends. Note that you can easily loop through all rows with a DO WHILE NOT AdStr.EOF, or do the first 3 rows with a FOR LI_LOOP = 1 to 3 or so on. If you loop, be sure to use a AdStr.MoveNext() command to tell the database to move along to the next row at the end of each loop.
You always need to CLOSE your database connection when you're done with it, so the database doesn't just sit there thinking it's going to get more requests.
Finally, destroy the AdStr object so your server doesn't keep it in memory, which will eventually suck all memory out of the server's active area :)

So really, these 9 lines are the core for any select statement you'll ever need to do. Just change out the SQLText line to have different select statements. Any select statement that works in SQL should work in here. You can get one value out, or 10, or 100, or "all values" by looping through the records.

Note to make the above ad code functional, you'd then need to increment the hit_count by one, so that the table knows this ad has been shown. That lets another ad have its chance the next time around. Check out How to Update Rows for help with this.

Key Mistakes to Watch For
* Always Always test your SQL Statement!! Make sure it really works the way you think it does.
* Always use a MoveNext if you're moving through records! Otherwise you get the same record over and over.

To learn more about the basic syntax options for a select statement, read Syntax of a SQL Select Statement.

Also, to make sure you avoid important mistakes, read Handling Apostrophes in ASP and SQL

SQL Command Listing

ASP Is Fun

ASP Classic Code and Contracting

Welcome to my ASP Code Website

Selecting from a Database