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.

Rated: by Aspin.com users What do you think? 5 Stars4 Stars3 Stars 2 Stars1 Star

'<--- 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
Dim rsKey
Dim MyRec
'dim CursorType,adOpenStatic
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 chose 3.
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")
end if
if NOT x = 3 then
response.write " rsKey=" & x & " for file id " & rs("FILE_ID") & "<br> "
end if
rs.movenext
If rs.eof Then Exit Do
loop
'<--- END where key numbers are assigned to items in the record set.
response.write " " 'text here if you want

loop
response.write "</br><br>"
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
rsNew.Close
set rsNew = Nothing
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
 

Assign variables from recordset to static keys


myArray(1) is 22 aka variable MybRec.
<%
Dim myArray(3)

myArray(0)=MyaRec
myArray(1)=MybRec
myArray(2)=MycRec
response.write "<br>myArray(1) is " & myArray(1) &" aka variable MybRec.<br>"