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.strSess ionID, Sum(tblCart.int Qty) AS SumOfintQty
" & _
"FROM tblCart " & _
"GROUP BY tblCart.strSess ionID " & _
"HAVING tblCart.strSess ionID='" & strSessionID & "'"
objRS.Open strSQL, objConn

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

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

strSQL="SELECT tblCart.strSess ionID, tblCart.strISBN ,
tblCart.intQty, tblBookDescript ion.ISBN, tblBookDescript ion.strTitle,
tblBookDescript ion.dblPrice " & _
"FROM tblBookDescript ion, tblCart " & _
"WHERE tblCart.strSess ionID='" & 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="ProductPa ge.asp?isbn= <% =objRS("ISBN")% > "> <%
=objRS("strTitl e")%> </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("dblPric e")*.8))
dblPrice = (objRS("dblPric e"))
%>
<! Price >
<FONT face=arial,verd ana,helvetica>< B>List Price:
<font color=#990000>< strike><% =dblPrice
%></strike></font><br>

<FONT face=arial,verd ana,helvetica>O ur Price:
<font color=#990000> <% =curDiscPrice %> </font><br>

<FONT face=arial,verd ana,helvetica>Y ou 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]

isaac2004 wrote:

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.strSess ionID, Sum(tblCart.int Qty) AS SumOfintQty
" & _
"FROM tblCart " & _
"GROUP BY tblCart.strSess ionID " & _
"HAVING tblCart.strSess ionID='" & strSessionID & "'"
objRS.Open strSQL, objConn

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

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

strSQL="SELECT tblCart.strSess ionID, tblCart.strISBN ,
tblCart.intQty, tblBookDescript ion.ISBN, tblBookDescript ion.strTitle,
tblBookDescript ion.dblPrice " & _
"FROM tblBookDescript ion, tblCart " & _
"WHERE tblCart.strSess ionID='" & strSessionID & "'"

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

--
Mike Brind

[isaac2004]

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

the SQL is
strSQL:SELECT tblCart.strSess ionID, tblCart.strISBN , tblCart.intQty,
tblBookDescript ion.ISBN, tblBookDescript ion.strTitle,
tblBookDescript ion.dblPrice FROM tblBookDescript ion, tblCart WHERE
tblCart.strSess ionID='vtypj355 4213ri55s5za3lr n'

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

[Mike Brind]

isaac2004 wrote:

Simple debugging - what do you get if you response.write strSQL at this

point in your code?

the SQL is
strSQL:SELECT tblCart.strSess ionID, tblCart.strISBN , tblCart.intQty,
tblBookDescript ion.ISBN, tblBookDescript ion.strTitle,
tblBookDescript ion.dblPrice FROM tblBookDescript ion, tblCart WHERE
tblCart.strSess ionID='vtypj355 4213ri55s5za3lr n'

this means it is relying on the the Session ID and grabbing all books
out of the tblBookDescript ion, 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, tblBookDescript ion.ISBN,
tblBookDescript ion.strTitle, tblBookDescript ion.dblPrice FROM tblCart
INNER JOIN tblBookDescript ion ON tblCart.strISBN =
tblBookDescript ion.ISBN WHERE tblCart.strSess ionID='" & 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 tblBookDescript ion, and the SessionID,
which you already have in strSession.

--
Mike Brind