I created an SQL statement like this: "SELECT * FROM table WHERE field = value" and the user can click on each item that is found in the database. I want to check if the user has clicked on either the last or the first record in the SQL clause.

I searched the internet and of course my books but could not find a good solutions. Something like IF RecordSet1.FirstRecord = true or something like recordset indexing?!

<hr><b>DMXZone support manager</b><br><a href="www.kousman.nl">Kousman web resellers</a>

There are a few ways that you can do this:

the first way presupposes that the recordset is ordered in some way - either by ID or by date for example - and that the ordered field has unique values. You can do a query to find the min max values of the ordered field, then you can check whether the ID field is 1 or max(id). like this:

SQL = "select min(id) as minVal, max(id) as maxVal from table where field = '" & myValue & "'" (autonumbered field ID version

or

SQL = "select min(date_created) as minVal, max(date_created) as maxVal from table where field = '" & myValue & "'"

then
set conn=server.createObject("adodb.connection"
conn.open [insert connection string]
set rs = conn.execute(SQL)
minVal = rs.fields("minVal"
maxVal = rs.fields("maxVal"
set rs = nothing


' then do the second SQL query to list the items from the database:
SQL = "SELECT * FROM table WHERE field = value"
set rs = conn.execute(SQL)
if not rs.eof then
while not rs.eof
if rs.fields("fieldname" = minVal or rs.fields("fieldname" = maxVal then
' = first or last item
response.write "&lt;ahref=""url?firstlast=true""&gt;" & rs.fields("fieldname" & "&lt;/a&gt;"
else
response.write "&lt;ahref=""url?firstlast=false""&gt;" & rs.fields("fieldname" & "&lt;/a&gt;"
end if
rs.movenext
wend
else
response.write "No records found"
end if
conn.close
set conn = nothing

then you can do your check for the first record or last record.

The second way works if your recordset does not contain unique values, for example, a list of people's first names and their favourite colour, then you will need to count the number if rows in the recordset and check the row number like this:

SQL= "select * from table where field = 'value'"
set conn = server.createObject("ADODB.connection"
conn.open [connectionstring]
set rs=server.createObject("ADODB.recordSet"
rs.open SQL,conn,3,3
if not rs.eof then
numRows = rs.numRows
counter = 0
while not rs.eof
counter = counter + 1
if counter = 1 or counter = numRows then
' = first or last item
response.write "&lt;ahref=""url?firstlast=true""&gt;" & rs.fields("fieldname" & "&lt;/a&gt;"
else
response.write("&lt;ahref=""url?firstlast=false""&gt;" & rs.fields("fieldname" & "&lt;/a&gt;"
end if
rs.movenext
wend
end if
rs.close
set rs= nothing
conn.close
set conn = nothing