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, 1900This article has been Favorited 0 times
Join today, and add this to your favorites.
Share With Friends (Updated 6-8-2010)
No Files for this Article.
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.
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.
<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.
<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.
<% 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