Multiple ways to write Parameterized SQL Query for your Classic ASP / VBScript pages

A Parameterized SQL Query is used to do a few things.

1. Retrieve data from a database to present on the webpage.
2. Help protect your database from [SQL Injections].

Below, I am going to list the different methods I use for performing these very powerful actions to get the information from [SQL Server] to our [Classic ASP] / [VBScript] coded page.

To return a single record based on a [QueryString] value.
[Example 1]
<% getTID = Request.QueryString("TID") Set sqlTracks = CreateObject("ADODB.Command") sqlTracks.ActiveConnection=siteconn sqlTracks.Prepared = true sqlTrack = "Select TrackName from TRail where TID = ?" sqlTracks.commandtext = sqlTrack sqlTracks.Parameters.Append sqlTracks.CreateParameter("@TID", adInteger, adParamInput, , getTID) set rsTracks = sqlTracks.execute if rsTracks.eof then response.write "Sorry, there is no record for that Track in our Database." else strTrackName = rsTracks("TrackName") %> You are viewing information on the Train Track <%=strTrackName%> <% ' Close RecordSet rsTracks.close set rsTracks = nothing ' Close Database Connection siteconn.close set siteconn= nothing end if%>

To return a multile records based on a [QueryString] value.
[Example 2]
<% getTID = Request.QueryString("TID") Set sqlTracks = CreateObject("ADODB.Command") sqlTracks.ActiveConnection=siteconn sqlTracks.Prepared = true sqlTrack = "Select TrackName from TRail where TID = ?" sqlTracks.commandtext = sqlTrack sqlTracks.Parameters.Append sqlTracks.CreateParameter("@TID", adInteger, adParamInput, , getTID) set rsTracks = sqlTracks.execute if rsTracks.eof then response.write "Sorry, there is no record for that Track in our Database." else%> You are viewing information on the Train Track <%while not rsTracks.eof strTrackName = rsTracks("TrackName") %> <%=strTrackName%> <%rsTracks.movenext wend ' Close RecordSet rsTracks.close set rsTracks = nothing ' Close Database Connection siteconn.close set siteconn= nothing end if%>
Next is one that I've used in the past for a large [SQL Statement], but stopped using it due to a lack of knowledge of its use.
However, I just began the new redesign of our Radio site, and am going to implement this for all complex loop [Queries].
[Example 3]
<% getTID = Request.QueryString("TID") Set sqlTracks = CreateObject("ADODB.Command") sqlTracks.ActiveConnection=siteconn sqlTracks.Prepared = true sqlTrack = "Select TID, TrackName from TRail where TID = ?" sqlTracks.commandtext = sqlTrack sqlTracks.Parameters.Append sqlTracks.CreateParameter("@TID", adInteger, adParamInput, , getTID) set rsTracks = sqlTracks.execute If rsTracks.EOF Then response.write "Sorry, there is no record for that Track in our Database." else arrTracks = rsTracks.GetRows() ' Loads data into an array End If rsTracks.close set rsTracks = nothing If IsArray(arrTracks) Then For Track = 0 To UBound(arrTracks, 2) strTrackName = rsTracks("TrackName") %> You are viewing information on the Train Track <%while not rsTracks.eof strTrackName = arrTracks(0,Track) %> <%=strTrackName%> <%next end if ' Close Database Connection siteconn.close set siteconn= nothing end if%>
OK, let's look at the code above and how it works.

The array is presented by items from the Select statement.
I run a SQL Script that gives me a list, in order, of all the columns.
I then present them like this.
' ColOne - 0 ' ColOne - 1 ' Next, we create our Variables using the Array strTID = arrTracks(0,Track) strTrackName = arrTracks(1,Track) ' Then present it to the page as normal. <%=strID%> | <%=strTrackName %>

The bottom code will throw out the data to your page a lot faster than the one in Example 2