Returning the records before and after a specific record

If this is the wrong group to be posting to, please forgive me.

I am working on improving an online store that uses a SQL back end and
ASP/vbscript combo for returning returning store items to the web pages.
Currently, when a user clicks a category, the asp/vbscript displays 5
records per page, alphabetical order of store items for that category. The
bottom of each page has hyperlinks to the other pages (ie. 1 2 3 4
5, with the 3 highlighted because you're on 3, and the others hyperlinsk to
their respective pages for that category). That's fine.

However, when we use links to specific items, we need to include the page
reference and anchor in the link, since we want customers to see the other
items and pages before and after that item. So, when the user clicks a link
that goes directly to a specific item (as opposed to an entire category),
let's say it's a jacket from the Clothing category, the link looks like
this:

www.mystore.com/store/ProductDisplayPage.asp?Category=Clothi ng&Page=3#Jacket

The page number might change in the future, though, because we add and
remove products all the time, so links become outdated. For instance, if we
add a bunch of clothing articles, the Jacket may be pushed to page 4 or 5
due to how the store displays 5 products per page, in alphabetical order by
product name. Is there a way to fix this problem, while still being able
to:

1) Show the products above and below a specific product linked to, and also
what page of how many you are on when viewing that product in its category
(ie. page 3 of 5, with the 1,2,4,and5 hyperlinks to those corresponding
pages)
2) Continue writing 5 products per page in alphabetical order when clicking
categories
3) Maintain link integrity to specific items, even if they are not
necessarily on the same page all the time (ie. stop using page numbers in
links, but still keeping with number 1)

One idea I thought I might be able to do is use a new field that would just
be the sort order for the products. Then, when a user clicks a link to a
specific product, the SQL would return that product, plus the 2 products
above and below it in terms of sort order on that page, and also still show
the pages previous and after at the bottom (page 1 2 3 4 5), with the 3
bolded because that's the page you're on when linking to this specific
product. However, I have no idea how to go about this, and I'm also not
sure it's the right approach.

Any help, advice, different ideas, etc. would be greatly appreciated, and
thanks for reading.

Re: Returning the records before and after a specific record

Hi Newbie,

Use the identity column in your recordset to link to your search page
results.

eg. sProvider can be a search text or if its numeric, a record identifier

if len(sProvider) then
sSQL = "SELECT Homepages.* FROM Homepages "
if isnumeric(sProvider) then
sSQL = sSQL & "WHERE HomePageId=" & sProvider & " "
else
select case Ucase(sProvider)
Case "LATEST"
sSQL = "SELECT TOP 5 * FROM Homepages "
sSQL = sSQL & "WHERE Status = 'Active' "
if len(sKeyword) then
sSQL = sSQL & "AND Keywords LIKE '" & sKeyword & "%' "
end if
sSQL = sSQL & "ORDER BY HomePageId DESC "
Case "ALL"


Your links to your product details will look something like this

href="searchresults.asp?txtProvider=<%=recordset("Identitycolumn").value%>"
target="_self">Product details

Alternatively instead of redisplaying the results page with just one record.
You may want to create a DetailsPage, that takes as an argument/parameter
of the Identity column value of the selected Product, reads that record from
the Products table and displays the expanded details.
eg.

href="ProductDetails.asp?ProductId=<%=recordset("Identitycolumn").value%>"
target="_blank">Product Details

Regards.
"Newbie Supreme" wrote in message
news:i_ydnTd-Y7vqjDDbnZ2dnUVZ_gKdnZ2d@comcast.com...
> If this is the wrong group to be posting to, please forgive me.
>
> I am working on improving an online store that uses a SQL back end and
> ASP/vbscript combo for returning returning store items to the web pages.
> Currently, when a user clicks a category, the asp/vbscript displays 5
> records per page, alphabetical order of store items for that category.
> The bottom of each page has hyperlinks to the other pages (ie. 1 2 3
> 4 5, with the 3 highlighted because you're on 3, and the others hyperlinsk
> to their respective pages for that category). That's fine.
>
> However, when we use links to specific items, we need to include the page
> reference and anchor in the link, since we want customers to see the other
> items and pages before and after that item. So, when the user clicks a
> link that goes directly to a specific item (as opposed to an entire
> category), let's say it's a jacket from the Clothing category, the link
> looks like this:
>
> www.mystore.com/store/ProductDisplayPage.asp?Category=Clothi ng&Page=3#Jacket
>
> The page number might change in the future, though, because we add and
> remove products all the time, so links become outdated. For instance, if
> we add a bunch of clothing articles, the Jacket may be pushed to page 4 or
> 5 due to how the store displays 5 products per page, in alphabetical order
> by product name. Is there a way to fix this problem, while still being
> able to:
>
> 1) Show the products above and below a specific product linked to, and
> also what page of how many you are on when viewing that product in its
> category (ie. page 3 of 5, with the 1,2,4,and5 hyperlinks to those
> corresponding pages)
> 2) Continue writing 5 products per page in alphabetical order when
> clicking categories
> 3) Maintain link integrity to specific items, even if they are not
> necessarily on the same page all the time (ie. stop using page numbers in
> links, but still keeping with number 1)
>
> One idea I thought I might be able to do is use a new field that would
> just be the sort order for the products. Then, when a user clicks a link
> to a specific product, the SQL would return that product, plus the 2
> products above and below it in terms of sort order on that page, and also
> still show the pages previous and after at the bottom (page 1 2 3 4
> 5), with the 3 bolded because that's the page you're on when linking to
> this specific product. However, I have no idea how to go about this, and
> I'm also not sure it's the right approach.
>
> Any help, advice, different ideas, etc. would be greatly appreciated, and
> thanks for reading.
>