The ASP On Line Course:
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.
<!-- #INCLUDE VIRTUAL="/DataStore.inc" -->
<!-- #INCLUDE VIRTUAL="/adovbs.inc" -->
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")
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:
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.
ASP Course and Code | HTML Tips, Code & Help | Java Code & Help | Gaming Walkthroughs