Previous Article
Request.Form (Allow your visitors to play YouTube Video's in your ASP Page)
ASP Classic Looping Select Statement
Go Back To List
SQL (Structured Query Language)
Coding Article #: 82 - Published On: July 07, 2017 @ 00:57:00 AM - Last Updated on: January 01, 1900
This article has been Favorited 0 times
Join today, and add this to your favorites.
Coding Source - Share on MySpace Coding Source - Share With Facebook Coding Source - Share on Twitter Coding Source - Share on Reddit Coding Source - Share on Digg It Coding Source - Share on Stumble Upon It Coding Source - Share on Delicious
Share With Friends (Updated 6-8-2010)

Supported Files
No Files for this Article.
No Screenshot Available

With this code, we are going to create a Parameter that out Select Statement with use to Query a value.

<%
        GetValue = request.QueryString("ID")
        Set sql= Server.CreateObject("ADODB.Command")
        sql.ActiveConnection=Conn
        sql.Prepared = true
        sql.commandtext="Select ColOne, ColThree, ColTen from TableOne where ColEight=?"
		sql.Parameters.Append sql.CreateParameter("@ColEight", adVarChar, adParamInput, 50, GetValue)
        set rsSQL = sql.execute
%>

First thing that you notice is we create a Variable called: GetValue
This Variable will collect the value sent for the ID, of our QueryString.
default.asp?id=1
So, with the value of "1", we will only get the information that is in the record that reports back with this ID of 1.

Next, we need to loop through and display our records to the page.

Part: 1

<ul>
<%
while not rsSQL.eof
if not rsSQL.eof then
One = rsSQL("ColOne")
Three = rsSQL("ColThree")
Ten = rsSQL("ColTen")
end if
%>

In Part 1, we do a WHILE LOOP, this will gather all the records and display them on the page.
So, every record that corresponds with the ID=1 will show on the page.
In the second section of Part 1, we use an IF STATEMENT, that checks to make sure there is a record before querying the database for the records.


Part: 2

<li><%=One%></li>
<li><%=Three%></li>
<li><%=Ten%></li>

In Part 2, we use the variables that we created in the second part of Part 1, to display our records to the page, instead of actually usually the RecordSet.


Part: 3

<%
rsSQL.movenext
wend
rsSQL.close
set rsSQL = Nothing
Conn.close
set Conn = Nothing
%>
</ul>

Part 3, we end our LOOP, with a Movenext Wend. The Movenext lets the LOOP know that it should only display the records that are within the LOOP, and not go outside of it.
In the second section of Part 3.
We close our RecordSet and our Database Connection.
Closing the connections and RS's are critical that you do this. Closing the connections frees up resources that would otherwise be tied up.
It is real easy to forget about this part. So make it a habit of closing your connections.
In ASP.NET, you will get an error is the connections and RecordSets are not close. Unfortunately, that is not the case with ASP Classic.
Post to Facebook about: ASP Classic Looping Select Statement