Navigate in database

24 Mar 2005 10:10 AM

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 :)

24 Mar 2005 10:42 AM

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!

Show quoteHide quote

"Vinnie Davidson" <ad***@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 :)
>
>

24 Mar 2005 11:35 AM

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

"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!

Show quoteHide quote

"Vinnie Davidson" <ad***@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 :)
>
>

24 Mar 2005 7:13 PM

Jeff Cochran

On Thu, 24 Mar 2005 12:35:45 +0100, "Vinnie Davidson"
<ad***@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

Show quoteHide quote

>"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
>%>