ASP - Navigate in database

Author

Navigate in database

Vinnie Davidson

2005-03-24, 8:55 am

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

2005-03-24, 8:55 am

If IsValidID(Clng(varID -1)) Then lPrevID=3DClng(varID -1) Else =
lPrevID=3D"NULL"
If IsValidID(Clng(varID +1)) Then lNextID=3DClng(varID +1) Else =
lNextID=3D"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)=3DFalse Then IsValidID=3DFalse: 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") =3D sID Then
IsValidID =3D True
Exit Do
End If
fRst.MoveNext
Loop
objRst.Close
End Function
%>

--=20
Regards

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

Keeping it FREE!

"Vinnie Davidson" <admin@webressurs.no> wrote in message =
news:uyGS#mFMFHA.732@TK2MSFTNGP12.phx.gbl...
> Hi!
>=20
> 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=20
> "next article" links.
> So, my question is how can I get the previous and next article ID =
based on=20
> the article that is open??
>=20
> I though about the MovePrevious and MoveNext methods, but it was no=20
> success...
> The SQL needs some parameters to get the correct records, like the =
example=20
> below with "artID", "catID", "active", "custID".
> My thoughs here is to open the record I'm in, move to previos record =
and get=20
> the ID, move to next 2 times to get the next ID... Ofcouse this dont =
work=20
> becouse of the artID in the SQL....?
>=20
>=20
> <%
> varID =3D Request.QueryString("id")
>=20
> SQL =3D "SELECT artID, catID, active, custID from tblArticle where " =
&_
> "((artID =3D '"&varID&"') AND (custID =3D '3') AND (active =3D '1'))"
>=20
> set rsNavigate =3D server.CreateObject("adodb.recordset")
> rsNavigate.Open SQL,connstring
>=20
> rsNavigate.MovePrevious
> dbPrev =3D rsNavigate.Fields("artID") 'Hoped this would give me =
the=20
> previous ID..
>=20
> rsNavigate.MoveNext
> rsNavigate.MoveNext
> dbNext =3D rsNavigate.Fields("artID") 'Hoped this would give me =
the next=20
> ID...
>=20
> rsNavigate.Close
> set rsNavigate =3D nothing
> %>
>=20
>=20
> Can anyone please help me???
> Vinnie :)=20
>=20
>=20

Vinnie Davidson

2005-03-24, 8:55 am

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....

"Steven Burn" <somewhere@in-time.invalid> wrote in message
news:%23P8nW5FMFHA.1396@TK2MSFTNGP10.phx.gbl...
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" <admin@webressurs.no> wrote in message
news:uyGS#mFMFHA.732@TK2MSFTNGP12.phx.gbl...
> 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 :)
>
>

Jeff Cochran

2005-03-24, 3:55 pm

On Thu, 24 Mar 2005 12:35:45 +0100, "Vinnie Davidson"
<admin@webressurs.no> wrote:

>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

>"Steven Burn" <somewhere@in-time.invalid> wrote in message
>news:%23P8nW5FMFHA.1396@TK2MSFTNGP10.phx.gbl...
>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
>%>

Sponsored Links