Navigate in database

[Vinnie Davidson]

Hi!

I have a detail page that shows one spesific record based on the article ID.
I need to make a navigation on this detail page, with "previous article" and
"next article" links.
So, my question is how can I get the previous and next article ID based on
the article that is open??

I though about the MovePrevious and MoveNext methods, but it was no
success...
The SQL needs some parameters to get the correct records, like the example
below with "artID", "catID", "active", "custID".
My thoughs here is to open the record I'm in, move to previos record and get
the ID, move to next 2 times to get the next ID... Ofcouse this dont work
becouse of the artID in the SQL....?


<%
varID = Request.QueryString("id")

SQL = "SELECT artID, catID, active, custID from tblArticle where " &_
"((artID = '"&varID&"') AND (custID = '3') AND (active = '1'))"

set rsNavigate = server.CreateObject("adodb.recordset")
rsNavigate.Open SQL,connstring

rsNavigate.MovePrevious
dbPrev = rsNavigate.Fields("artID") 'Hoped this would give me the
previous ID..

rsNavigate.MoveNext
rsNavigate.MoveNext
dbNext = rsNavigate.Fields("artID") 'Hoped this would give me the next
ID...

rsNavigate.Close
set rsNavigate = nothing
%>


Can anyone please help me???
Vinnie

 

[Steven Burn]

If IsValidID(Clng(varID -1)) Then lPrevID=Clng(varID -1) Else lPrevID="NULL"
If IsValidID(Clng(varID +1)) Then lNextID=Clng(varID +1) Else lNextID="NULL"

Obviously you'll want to check to make sure the ID is valid before printing it to the page (did this on my freeware site).

Although I'm probably going to get crucified for doing it this way, I've used the following (modified a bit as I doubt you'll be using the fields I am).... works just fine for me;

<%
Function IsValidID(sID)
'// Allow numeric sID's only
If IsNumeric(sID)=False Then IsValidID=False: Exit Function

'// DB Connection etc goes here
objRst.Open "Select fldID From tblTable_Name order by fldID ASC", objDB, adOpenStatic, adLockReadOnly
Do While not fRst.eof
If objRst("fldID") = sID Then
IsValidID = True
Exit Do
End If
fRst.MoveNext
Loop
objRst.Close
End Function
%>

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Vinnie Davidson]

Thanks for your answer!

If I understand this right, the code just add or remove 1 from the current
ID (varID). This would work fine if i knew that the previous og next ID is
the one I want, but I dont. I have to check if the previous or next ID has
the correct categoryID (catID), customerID (custID) and active = 1. If these
criteria dont match, the code should "jump" to next record .... think you
got the point..

This is a "nut"for me....



If IsValidID(Clng(varID -1)) Then lPrevID=Clng(varID -1) Else lPrevID="NULL"
If IsValidID(Clng(varID +1)) Then lNextID=Clng(varID +1) Else lNextID="NULL"

Obviously you'll want to check to make sure the ID is valid before printing
it to the page (did this on my freeware site).

Although I'm probably going to get crucified for doing it this way, I've
used the following (modified a bit as I doubt you'll be using the fields I
am).... works just fine for me;

<%
Function IsValidID(sID)
'// Allow numeric sID's only
If IsNumeric(sID)=False Then IsValidID=False: Exit Function

'// DB Connection etc goes here
objRst.Open "Select fldID From tblTable_Name order by fldID ASC",
objDB, adOpenStatic, adLockReadOnly
Do While not fRst.eof
If objRst("fldID") = sID Then
IsValidID = True
Exit Do
End If
fRst.MoveNext
Loop
objRst.Close
End Function
%>

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Jeff Cochran]

Thanks for your answer!

If I understand this right, the code just add or remove 1 from the current
ID (varID). This would work fine if i knew that the previous og next ID is
the one I want, but I dont. I have to check if the previous or next ID has
the correct categoryID (catID), customerID (custID) and active = 1. If these
criteria dont match, the code should "jump" to next record .... think you
got the point..

This is a "nut"for me....

In otherwords your ID isn't sequential, and you're just querying data
for the ID in question, correct? Might want to pull in all matching
records and then use recordset paging to accomplish your task. Take a
look at:

http://www.aspfaq.com/show.asp?id=2120

Jeff