g Text Version
Beauty & Self
Books & Music
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
News & Politics
Religion & Spirituality
Travel & Culture
TV & Movies

Bored? Games!
Take a Quiz
Rate My Photo

Natural Living
Folklore and Mythology
Distance Learning

All times in EST

Full Schedule
g ASP Site

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

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.
Add Selecting+from+a+Database to Twitter Add Selecting+from+a+Database to Facebook Add Selecting+from+a+Database to MySpace Add Selecting+from+a+Database to Del.icio.us Digg Selecting+from+a+Database Add Selecting+from+a+Database to Yahoo My Web Add Selecting+from+a+Database to Google Bookmarks Add Selecting+from+a+Database to Stumbleupon Add Selecting+from+a+Database to Reddit

RSS | Related Articles | Editor's Picks Articles | Top Ten Articles | Previous Features | Site Map

For FREE email updates, subscribe to the ASP Newsletter

Past Issues

Printer Friendly
tell friend
Tell a Friend
Email Editor

Content copyright © 2015 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 Lisa Shea for details.


g features
Regular Expression Pattern Matching

Split ASP String Function

Saving Changes is Not Permitted error

Archives | Site Map


Past Issues

Less than Monthly

BellaOnline on Facebook

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

BellaOnline Editor