Put a Record Set into an Array. Assign keys to the records in
Sometimes, you may want to pull a record out of a recordset by its order number
to use elsewhere because you don't know the primary key of the
record or anything else about it. This is especially useful to know when your
primary keys have gaps in the sequences where records have been deleted, or if
your primary keys aren't numerical. (Keywords: Assign Array Keys to Record
Sets recordsets ASP active server pages Access database)
I tried to figure out how to do this for a long time. I searched all over the
web trying to find it, but it must have seemed too obvious to everyone for
anyone to bother posting it phrased in just this way. One day, while driving in the truck, the solution
came to me. If you like it, please rate.
Actual demonstration from database (note that the Primary Key numbers have a
gap in their sequence):
rsKey=1 for file id 19 rsKey=2 for file id 20 Your record id for rsKey number 3 is 22 rsKey=4 for file id 23 rsKey=5 for file id 24 rsKey=6 for file id 25 rsKey=7 for file id 26 rsKey=8 for file id 27 rsKey=9 for file id 28 rsKey=10 for file id 29 rsKey=11 for file id 30 rsKey=12 for file id 31 rsKey=13 for file id 34 rsKey=14 for file id 35 rsKey=15 for file id 44 rsKey=16 for file id 47 rsKey=17 for file id 48 rsKey=18 for file id 49 rsKey=19 for file id 50 rsKey=20 for file id 51 rsKey=21 for file id 52
My selected rsKey is 3 and its record id is 22. That file belongs to Donna and the filename is georgia.JPG. That line of text comes from this: response.write " That file belongs to " & rsNew("USER_ID") & " and the filename is " & rsNew("FILENAME")&"."
Here is the main code for assigning array keys to recordset items:
'<--- First we get the database connection and sql statement
taken care of and make the recordset
Dim conn ' ADO connection
Dim rs ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
strDBPath = Server.MapPath("database_interface/gallery_demo/IMAGE_GALLERY.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
strSQL = "SELECT * FROM IMAGEFILES ORDER BY FILE_ID " & ";"
'To pre-filter records comment out above and uncomment below with correct fields
for keyID and OrderField
'strSQL = "SELECT * FROM IMAGEFILES WHERE FILE_ID ="& keyID &" ORDER BY " &
OrderField & ";"
Set rsConn = Server.CreateObject("ADODB.Connection")
' Create recordset and set the page size
Set rs = Server.CreateObject("ADODB.Recordset")
' Open rs
rs.Open strSQL, conn,3,3, adCmdText
'<--- BEGIN where key numbers are assigned to items in the record set: rs.movefirst 'going to the first record as a
place to begin the array
x = 0
do until rs.eof
strID = rs("FILE_ID")
'response.write " " & strID & " " 'uncomment to have text here
x = x + 1 ' Setting up our array's key numbers
do until rs("FILE_ID") <> strID
if x = 3 then ' Which key do you want to work with? I
response.write " Your record id for rsKey number 3 is " & rs("FILE_ID") & "<BR>
rsKey = x ' Here is where we BENCHMARK the key-to-record association
MyRec = rs("FILE_ID")
if NOT x = 3 then
response.write " rsKey=" & x & " for file id " & rs("FILE_ID") & "<br> "
If rs.eof Then Exit Do
'<--- END where key numbers are assigned to items in the record set.
response.write " " 'text here if you want
response.write "<b>My selected rsKey is " & rsKey &" and its record id is " &
MyRec & "."
newSQL = "SELECT * FROM IMAGEFILES WHERE FILE_ID ="& MyRec & ";"
'The rsKey from above is used here to find one record by
its ordinal number and then to filter the table for only that record
Set rsNew = Server.CreateObject("ADODB.Recordset")
rsNew.Open newSQL, conn,3,3, adCmdText
response.write " That file belongs to " & rsNew("USER_ID") & " and the filename
is " & rsNew("FILENAME")&".</b>"
' Close DB objects and free variables
set rsNew = Nothing
Set rs = Nothing
Set conn = Nothing
I adapted this code to put selected record keys into a new array using keys 2,3,
The Primary Keys of the items are:
20 22 23
The code using Scripting Dictionary Object, with the key variables that
were set above.
d.Add rsaKey, MyaRec
d.Add rsbKey, MybRec
d.Add rscKey, MycRec
Response.Write("<p>The values of the items are:</p>")
for i = 0 To d.Count -1
s = s & a(i) & "<br>"
Another array that shows the rsKey variables being used as key numbers:
disArray(0) = "null" 'I chose to work with
ordinals 1, 2, and 3, but array keys must begin with 0. I simply made the key(0)
equal null. If I were using higher ordinals such as 11, 12, and 13, then I would
be required to begin disArray( ) with 0 and to fill in all the keys between 0
and 11 with null as placeholders. If this were to be a completely dynamic array
for which I wouldn't know how many keys, I might use select count or recordcount
to get the total number of potential keys.
disArray(rsaKey) = MyaRec
disArray(rsbKey) = MybRec
disArray(rscKey) = MycRec
response.write "<BR>Here is key disArray(rsaKey): " &disArray(rsaKey)&"<BR>"
response.write"Here is a readout of the full array:<BR>"
for each i in disArray
if NOT i = disArray(0) then
response.write i& " - "
Here is key disArray(rsaKey): 20 Here is a readout of the full array: - 20 - 22 - 23 -
Here are our key variables placed into a simple array:
Here are the Primary Keys from our newRs recordset: 20, 22, 23,
Here is the code: <%
response.write "Here are the Primary Keys from our rsNew recordset: "
for each i in simpleArray
response.write i &", "
ASP Examples Menu of Active Server Pages scripts code Classic ASP