g
Printer Friendly Version

editor  
BellaOnline's ASP Editor
 

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")
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:



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

Introduction to ASP Ebook

Download this ebook to get everything you need to know about learning ASP - from a step by step tutorial to function lists, sample code, common errors and solutions, and much more! 101 pages.

This site needs an editor - click to learn more!

ASP Site @ BellaOnline
View This Article in Regular Layout

Content copyright © 2013 by Lisa Shea. All rights reserved.
This content was written by Lisa Shea. If you wish to use this content in any manner, you need written permission. Contact Editor Wanted for details.



| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2013 Minerva WebWorks LLC. All rights reserved.


BellaOnline Editor