help with database driven cart

[isaac2004]

hello i am making a spoof online book store site for a class and I was
wondering how i could fix a problem i am having. I have two tables, one
the cart and the other a table with book descriptions. what i am trying
to do is display the book info for the cart through a SQL statement.
the only problem is when i do this it just outputs the info for all
books in the database. how would i change my SQL statement to only read
the info for the books listed as ISBNs in the cart.

here is a code snippet watch for text wraps

' count total items in shopping cart
dim intItemCount, dblPrice, curDiscPrice, curPrice, dblSubTotal
dim dblShipping, dblTotalPrice
strSQL="SELECT tblCart.strSessionID, Sum(tblCart.intQty) AS SumOfintQty
" & _
"FROM tblCart " & _
"GROUP BY tblCart.strSessionID " & _
"HAVING tblCart.strSessionID='" & strSessionID & "'"
objRS.Open strSQL, objConn

If NOT objRS.EOF then
intItemCount = objRS("SumOfintQty")
else
intItemCount = 0
end if
objRS.Close

'List items in cart
If intItemCount < 1 then
response.write "<center><font face='Comic Sans MS'
color='#FF0000'>" & _
"Your Shopping Cart is empty.</font><br><br>"
Else

strSQL="SELECT tblCart.strSessionID, tblCart.strISBN,
tblCart.intQty, tblBookDescription.ISBN, tblBookDescription.strTitle,
tblBookDescription.dblPrice " & _
"FROM tblBookDescription, tblCart " & _
"WHERE tblCart.strSessionID='" & strSessionID & "'"

objRS.Open strSQL, objConn

%>
<div align="center">
<center>
<font face='Comic Sans MS' color='#FF0000'>You have <%
=intItemCount %>
book<% If intItemCount > 1 then response.write "s" %> in
your shopping cart.
</font><br><br>

<table cellpadding="4">
<tr>
<td>Item</td>
<td>Book Title</td>
<td>Qty</td>
<td>Price</td>
<td> </td>
<td> </td>
</tr>
<%
'List each item in cart
dim intCount
do while NOT objRS.EOF
intCount = intCount + 1
%>
<tr>
<td><% =intCount %>.</td>
<td>
<! Book Title, author, stock >
<a href="ProductPage.asp?isbn= <% =objRS("ISBN")%> "> <%
=objRS("strTitle")%> </a>
<br>
<font size="-1">
by <% =funListAuthors(objRS("ISBN")) %></a>
</font>
<br>
</td>

<td><% =objRS("intQty") %></td>
<td>
<% curPrice = FormatCurrency(objRS("dblPrice"))
curDiscPrice = FormatCurrency((objRS("dblPrice")*.8))
dblPrice = (objRS("dblPrice"))
%>
<! Price >
<FONT face=arial,verdana,helvetica><B>List Price:
<font color=#990000><strike><% =dblPrice
%></strike></font><br>

<FONT face=arial,verdana,helvetica>Our Price:
<font color=#990000> <% =curDiscPrice %> </font><br>

<FONT face=arial,verdana,helvetica>You Save:
<font color=#990000><% =FormatCurrency((dblPrice -
curDiscPrice)) %>(20%)</b></font><br><br>

<td><a href="?delISBN=<% =objRS("strISBN")
%>"> Remove</a></td>
<td><a href="?addISBN=<% =objRS("strISBN")
%>"> Add</a></td>
</tr>
<% objRS.MoveNext
Loop
%> </table>

thanks for the help if you have any questions just ask

Isaac

 

[Mike Brind]

hello i am making a spoof online book store site for a class and I was
wondering how i could fix a problem i am having. I have two tables, one
the cart and the other a table with book descriptions. what i am trying
to do is display the book info for the cart through a SQL statement.
the only problem is when i do this it just outputs the info for all
books in the database. how would i change my SQL statement to only read
the info for the books listed as ISBNs in the cart.

here is a code snippet watch for text wraps

' count total items in shopping cart
dim intItemCount, dblPrice, curDiscPrice, curPrice, dblSubTotal
dim dblShipping, dblTotalPrice
strSQL="SELECT tblCart.strSessionID, Sum(tblCart.intQty) AS SumOfintQty
" & _
"FROM tblCart " & _
"GROUP BY tblCart.strSessionID " & _
"HAVING tblCart.strSessionID='" & strSessionID & "'"
objRS.Open strSQL, objConn

If NOT objRS.EOF then
intItemCount = objRS("SumOfintQty")
else
intItemCount = 0
end if
objRS.Close

'List items in cart
If intItemCount < 1 then
response.write "<center><font face='Comic Sans MS'
color='#FF0000'>" & _
"Your Shopping Cart is empty.</font><br><br>"
Else

strSQL="SELECT tblCart.strSessionID, tblCart.strISBN,
tblCart.intQty, tblBookDescription.ISBN, tblBookDescription.strTitle,
tblBookDescription.dblPrice " & _
"FROM tblBookDescription, tblCart " & _
"WHERE tblCart.strSessionID='" & strSessionID & "'"

Simple debugging - what do you get if you response.write strSQL at this
point in your code?

 

[isaac2004]

Simple debugging - what do you get if you response.write strSQL at this
point in your code?

the SQL is
strSQL:SELECT tblCart.strSessionID, tblCart.strISBN, tblCart.intQty,
tblBookDescription.ISBN, tblBookDescription.strTitle,
tblBookDescription.dblPrice FROM tblBookDescription, tblCart WHERE
tblCart.strSessionID='vtypj3554213ri55s5za3lrn'

this means it is relying on the the Session ID and grabbing all books
out of the tblBookDescription, how would I just grab the books in the
cart tbl. thanx

 

[Mike Brind]

point in your code?

the SQL is
strSQL:SELECT tblCart.strSessionID, tblCart.strISBN, tblCart.intQty,
tblBookDescription.ISBN, tblBookDescription.strTitle,
tblBookDescription.dblPrice FROM tblBookDescription, tblCart WHERE
tblCart.strSessionID='vtypj3554213ri55s5za3lrn'

this means it is relying on the the Session ID and grabbing all books
out of the tblBookDescription, how would I just grab the books in the
cart tbl. thanx

I think you have the cartesian product of two tables in the result of
your query. This is because you haven't joined them properly in your
SQL. The SQL should be:

SELECT tblCart.intQty, tblBookDescription.ISBN,
tblBookDescription.strTitle, tblBookDescription.dblPrice FROM tblCart
INNER JOIN tblBookDescription ON tblCart.strISBN =
tblBookDescription.ISBN WHERE tblCart.strSessionID='" & strSessionID &
"'"

I would suggest that in future, when you are trying to get results from
more than one table, you should use the Access Query Builder to test
your query. Once you have found it works, switch to SQL view and use
the SQL Access produces for you.

You also need to have a look at the Access Online help with regard to
joining tables. If you don't explicitly join the tables (and you do
his by dragging the primary key from one table to the foreign key in
the other so you get a black line joining them, or by defining table
relationships in the relationships pane), you will get all the results
from one table x the matching records in the other. So, if your cart
has two books in it, and your books table has 10 books in, you will get
a recordset of 20 books (that is, all books twice).

Also, in your original code, you unnecessarily selected the ISBN number
in tblCart, when it is also in tblBookDescription, and the SessionID,
which you already have in strSession.