Selecting All Rows from a Table

Selecting All Rows from a Table
There are many times that you will want to select all rows from a given database table. How do you move through the records, and display the data?

Let's say you have a table with BookName as a field and you want to write to the screen all BookNames in your library. You would start with -

Set BookList = Server.CreateObject ("ADODB.Recordset")
SQLText = "select BookName from Books order by BookName"
BookList.Open SQLText, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
do while not BookList.EOF
response.write BookList("BookName") & "
Set BookList = Nothing

The critical part of this code is the MoveNext. That tells the database to move on to the next record. If you did NOT include the MoveNext command, the system would just keep printing out the first book's title - over and over again - until it maxed out your system's resources! This is something to pay really close attention to.

Note that your select statement can of course have a "WHERE" clause in it. You can select all books that are over 19.99 in price, or all books added in the past week. This do loop will then loop through every row in the result set, and show them to the end user.

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

Content copyright © 2023 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.