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") & "
"
BookList.MoveNext()
loop
BookList.Close()
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.
SQL Command Listing