PDA

View Full Version : Ecommerce List Page Issue

Sully
06-04-2003, 12:24 AM
Hello,

Thanks for looking!

I have a problem with an ASP ecommerce product list page. The page is supposed to list only three products vertically and then the next three on the 'next' page. This code is not limiting the page to three products, but will show all.

If the result of the query is four products listproduct.asp will return and display four products. At the bottom of the page it will display "Page 1 2". When the "2" is selected it will display the forth item (the forth item had been displayed on page one too).

---code---

<%
CtgId = request.querystring("ctg")
Page = request.querystring("p")
if Page = "" then Page = 1

set objConn = server.createobject("ADODB.Connection")
objConn.Open CONN_STR

strSQL = "SELECT * FROM categories WHERE categoryId = " & sql_numeric(CtgId)
set objRsCat = server.createobject("ADODB.Recordset")
objRsCat.Open strSQL, objConn, 3, 1, 0
if not objRsCat.EOF then
path = replace(objRsCat("path"),"_",",")
level = objRsCat("level")
description = objRsCat("categoryDesc")
set objRsPath = server.createobject("ADODB.Recordset")
objRsPath.Open "SELECT * FROM categories WHERE categoryId IN (" & path & ") ORDER BY [level]", objConn, 3, 1, 0
set objRsSub = server.createobject("ADODB.Recordset")
objRsSub.Open "SELECT * FROM categories WHERE parentCategoryId = " & sql_numeric(CtgId), objConn, 3, 1, 0
end if
objRsCat.Close : set objRsCat = nothing

strSQL = "SELECT * FROM listproductsbycategory WHERE (status = 1) AND categoryId = " & sql_numeric(CtgId) & " ORDER BY productId DESC"
set objRsProd = server.createobject("ADODB.Recordset")
objRsProd.Open strSQL, objConn, 3, 1, 0
if not objRsProd.EOF then
objRsProd.PageSize = 3
objRsProd.AbsolutePage = Page
Found = objRsProd.RecordCount
else
Found = 0
end if

PAGE_TITLE = description
%>

<!--#include file="_head_main.asp"-->
<!--#include file="_left_col.asp"-->

<h2><%=description%></h2>

<p>
<%
if not objRsPath.EOF then
%>Browse : <a href="browse.asp">All categories</a> : <%
while not objRsPath.EOF
if cstr(objRsPath("categoryId")) = CtgId then
%><b><%=objRsPath("categoryDesc")%></b> : <%
else
%><a href="listproducts.asp?ctg=<%=objRsPath("categoryId")%>"><%=objRsPath("categoryDesc")%></a> : <%
end if
objRsPath.MoveNext
wend
end if
%>
</p>
<p>
<%
if not objRsSub.EOF then
totalsub = objRsSub.RecordCount
countsub = 0
%>See also in <%
while not objRsSub.EOF
%><a href="listproducts.asp?ctg=<%=objRsSub("categoryId")%>"><%=objRsSub("categoryDesc")%></a><%
objRsSub.MoveNext
countsub = countsub + 1
select case countsub
case totalsub : %>.<%
case totalsub-1 : %>, and <%
case else : %>, <%
end select
wend

end if
%>
</p>

<span class="smallexpl">Total <%=Found%> product(s)</span>
<%
if not objRsProd.EOF then
%>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2">
<hr noshade size="1">
</td>
</tr>
<%
while not objRsProd.EOF
%>
<tr>
<td width="110"><a href="preview.asp?prd=<%=objRsProd("productId")%>" title="click to see product details"><img src="<% format_image objRsProd("smallimageurl")%>" width="100" height="100" border="0"></a></td>
<td>
<p><b><font size="2"><a class="product" href="preview.asp?prd=<%=objRsProd("productId")%>" title="click to see product details"><%=objRsProd("name")%></a></font></b><br>
<span class="smallexpl">Avg. Customer Rating:</span> <%format_rating objRsProd("rating")%><br>
<font color="#666666">List price: <s>$<%=formatnumber(objRsProd("listprice"),2)%></s></font><br>
<%if session("btob") then%>
<b>B to B price: </b><font color="#CC0000"><b>$<%=formatnumber(objRsProd("btobprice"),2)%></b></font>
<%else%>
<b>Our price: </b><font color="#CC0000"><b>$<%=formatnumber(objRsProd("price"),2)%></b></font>
<%end if%>
<br>

</p>

<p>
<img src="images/arrow_bullet.gif" width="13" height="13" hspace="3" align="left">
<b><a href="addtocart.asp?prd=<%=objRsProd("productId")%>" title="add <%=objRsProd("name")%> to shopping cart">Add to cart</a></b>
</p>
</td>
</tr>
<tr>
<td colspan="2">
<hr noshade size="1">
</td>
</tr>
<%
objRsProd.MoveNext
wend
%>
<tr>
<td colspan="2" align="center">
Page
<%
for npage = 1 to objRsProd.PageCount
if npage = Page then
%><font color="#CC0000"><b><%=npage%></b></font><%
else
%><a href="listproducts.asp?ctg=<%=CtgId%>&p=<%=npage%>"><%=npage%></a><%
end if
next
%>
</td>
</tr>
</table>
<%
end if
objRsProd.Close : set objRsProd = nothing
%>
jsawkang
06-04-2003, 03:48 AM
Hi....

anyway, i'm sure there is some code missing in the first part of ur code(select the product). try the code below and change the database to urs.

<%
Dim rs, sSQL

Dim Connect_String
Dim Page_Size
Dim Current_Page
Dim Page_Count

Dim objConn, objRS, recordcount, MyConn, StrConnect
set objConn=Server.CreateObject("adodb.connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("../db/urdatabase.mdb")
set objRS=objConn.Execute("Select Count(*) from Suggestion")
recordcount=objRS(0)
objRS.close
set objRS=nothing
objConn.close
Set objConn=nothing

Page_Size = 3 'this is where you set the # of records displayed per page

If Request.QueryString("Page") = "" Then
Current_Page = 1
Else
Current_Page = CInt(Request.QueryString("Page"))
End If

Set MyConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

StrConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("../db/etender.mdb") & ";DefaultDir=" & Server.MapPath(".") & ";DriverId=25;FILE=MS Access;MaxBufferSize=512;PageTimeout=5"
MyConn.Open StrConnect

RS.CursorLocation = adUseClient
RS.PageSize = Page_Size

sSQL="SELECT Suggestion.*, User1.* FROM Suggestion, User1 Where Suggestion.UserName=User1.UserName AND Status='Un-Read Suggestion' order by SuggestionID DESC"
' Set rs = cn.Execute(sSQL)
RS.Open sSQL, MyConn, adOpenStatic, adLockReadOnly, adCmdText

If rs.EOF or rs.BOF then
response.Write "<center>No Record Found</center>"
Response.Write "<a href=""archive.asp"
Response.Write """><font face=""Verdana"" size=""2"">Suggestion Archive>></font></a>" & "&nbsp;<font face=""Verdana"" size=""2""></font>" & vbCrLf
else

Page_Count = rs.PageCount

If 1 > Current_Page Then Current_Page = 1
If Current_Page > Page_Count Then Current_Page = Page_Count

RS.AbsolutePage = Current_Page

%>

good luck

regards
Sully
06-04-2003, 04:06 AM
JSK,

Oops! You're right!! I did leave off the top of the page when I cut 'n pasted. I would like to try your solution, but I'm using MSSQL - is it easily converted?

Here's the whole page:


<!--#include file="_include.asp"-->
<!--#include file="_inc_format.asp"-->
<%
CtgId = request.querystring("ctg")
Page = request.querystring("p")
if Page = "" then Page = 1

set objConn = server.createobject("ADODB.Connection")
objConn.Open CONN_STR

strSQL = "SELECT * FROM categories WHERE categoryId = " & sql_numeric(CtgId)
set objRsCat = server.createobject("ADODB.Recordset")
objRsCat.Open strSQL, objConn, 3, 1, 0
if not objRsCat.EOF then
path = replace(objRsCat("path"),"_",",")
level = objRsCat("level")
description = objRsCat("categoryDesc")
set objRsPath = server.createobject("ADODB.Recordset")
objRsPath.Open "SELECT * FROM categories WHERE categoryId IN (" & path & ") ORDER BY [level]", objConn, 3, 1, 0
set objRsSub = server.createobject("ADODB.Recordset")
objRsSub.Open "SELECT * FROM categories WHERE parentCategoryId = " & sql_numeric(CtgId), objConn, 3, 1, 0
end if
objRsCat.Close : set objRsCat = nothing

strSQL = "SELECT * FROM listproductsbycategory WHERE (status = 1) AND categoryId = " & sql_numeric(CtgId) & " ORDER BY productId DESC"
set objRsProd = server.createobject("ADODB.Recordset")
objRsProd.Open strSQL, objConn, 3, 1, 0
if not objRsProd.EOF then
objRsProd.PageSize = 3
objRsProd.AbsolutePage = Page
Found = objRsProd.RecordCount
else
Found = 0
end if

PAGE_TITLE = description
%>

<!--#include file="_head_main.asp"-->
<!--#include file="_left_col.asp"-->

<h2><%=description%></h2>

<p>
<%
if not objRsPath.EOF then
%>Browse : <a href="browse.asp">All categories</a> : <%
while not objRsPath.EOF
if cstr(objRsPath("categoryId")) = CtgId then
%><b><%=objRsPath("categoryDesc")%></b> : <%
else
%><a href="listproducts.asp?ctg=<%=objRsPath("categoryId")%>"><%=objRsPath("categoryDesc")%></a> : <%
end if
objRsPath.MoveNext
wend
end if
%>
</p>
<p>
<%
if not objRsSub.EOF then
totalsub = objRsSub.RecordCount
countsub = 0
%>See also in <%
while not objRsSub.EOF
%><a href="listproducts.asp?ctg=<%=objRsSub("categoryId")%>"><%=objRsSub("categoryDesc")%></a><%
objRsSub.MoveNext
countsub = countsub + 1
select case countsub
case totalsub : %>.<%
case totalsub-1 : %>, and <%
case else : %>, <%
end select
wend

end if
%>
</p>

<span class="smallexpl">Total <%=Found%> product(s)</span>
<%
if not objRsProd.EOF then
%>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2">
<hr noshade size="1">
</td>
</tr>
<%
while not objRsProd.EOF
%>
<tr>
<td width="110"><a href="preview.asp?prd=<%=objRsProd("productId")%>" title="click to see product details"><img src="<% format_image objRsProd("smallimageurl")%>" width="100" height="100" border="0"></a></td>
<td>
<p><b><font size="2"><a class="product" href="preview.asp?prd=<%=objRsProd("productId")%>" title="click to see product details"><%=objRsProd("name")%></a></font></b><br>
<span class="smallexpl">Avg. Customer Rating:</span> <%format_rating objRsProd("rating")%><br>
<font color="#666666">List price: <s>$<%=formatnumber(objRsProd("listprice"),2)%></s></font><br>
<%if session("btob") then%>
<b>B to B price: </b><font color="#CC0000"><b>$<%=formatnumber(objRsProd("btobprice"),2)%></b></font>
<%else%>
<b>Our price: </b><font color="#CC0000"><b>$<%=formatnumber(objRsProd("price"),2)%></b></font>
<%end if%>
<br>

</p>

<p>
<img src="images/arrow_bullet.gif" width="13" height="13" hspace="3" align="left">
<b><a href="addtocart.asp?prd=<%=objRsProd("productId")%>" title="add <%=objRsProd("name")%> to shopping cart">Add to cart</a></b>
</p>
</td>
</tr>
<tr>
<td colspan="2">
<hr noshade size="1">
</td>
</tr>
<%
objRsProd.MoveNext
wend
%>
<tr>
<td colspan="2" align="center">
Page
<%
for npage = 1 to objRsProd.PageCount
if npage = Page then
%><font color="#CC0000"><b><%=npage%></b></font><%
else
%><a href="listproducts.asp?ctg=<%=CtgId%>&p=<%=npage%>"><%=npage%></a><%
end if
next
%>
</td>
</tr>
</table>
<%
end if
objRsProd.Close : set objRsProd = nothing
%>

<!--#include file="_foot_main.asp"-->
<%
objConn.Close : set objConn = nothing
%>


Regards,
Sully
jsawkang
06-09-2003, 03:44 AM
Hi..

so, ur code can run now? as far as i know, u don't have to convert anything. just have to make sure the the connection to the MS SQL Database is correct.