First tip is the best way to get data from SQL Server. Most people use ADO Recordset and loop through it. This means you have a connection to the SQL Server open while executing the script. A much better way is to use the GetRows() function in ADO:
<pre name="code" class="vb">
Dim db, dbrs, sql, ConnString, users, user
ConnString = “DSN=YourDSN;”
sql = “SELECT UserId, Username FROM Users ORDER BY Username;”
’ GET DATA
set db = server.createobject(“ADODB.Connection”)
set dbrs = server.createObject(“ADODB.Recordset”)
dbrs.open sql, db
if NOT dbrs.eof then
users = dbrs.GetRows()
set db = nothing
’ DISPLAY DATA
for user = lbound(users,2) to ubound(users,2)
response.write “UserId: “ & users(0,user)
response.write “Username: “ & users(1,user)
From the ADO Documentation:
Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data.
Since it’s a two-dimensional array we need to get the lower and upper bound of the array using: lbound(users,2). Using those values we loop from the first item to the last item.
The current record is the user variable. To get the field we want we must get it according to the index entered in the SQL Query:
SELECT UserId, Username..
and so on.
This can be confusing if you do changes to the SQL Query - then you must go through your code and update the array index for the fields! A solution to that is to create costants for each field name:
Const UserId = 0
<li>Use ADO GetRows() to get data.</li>
<li>Initiate objects as late as possible and release objects as early as possible.</li>
<li>Use variable for SQL query. Easier for debugging.</li>
<li>Declare all variables at the top of the script/function/class.</li>