DaniWeb IT Discussion Community - Shopping Cart Not Working

Hey All,
i'm having real trouble with this. ok, i have a login page where the user logs in. they put items in the cart and then go to addorder from to place order in database. only problem is i can't get it to work. i think the problem is that its not reading login and getting required userid from customers table, but i can't fix it. i don't know what to do with it. i'll paste in pages. any help would be greatly appreciated.

LOGIN.ASP

<%

'Green colorset

'BackgroundColor="#C9DDB3"

'BorderColor="#006600"

'Blue colorset

BackgroundColor="#AFD1F8"

BorderColor="#000080"

'Purple colorset

'BackgroundColor="#FDC8F2"

'BorderColor="#800080"

Content = "" 'Clear the Content string

QStr = Request.QueryString("login") 'Save the login querystring to QStr

if ucase(left(QStr,6))="CREATE" then

Title = "Register"

else

Title = "Login"

end if

'The code below saves the contents the table must have in the variable Content

'The content depends on what's in the QueryString

if QStr="passfailed" then

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Wrong password</P><A href=Javascript:history.go(-1)>Back</A></td></tr>"

elseif QStr="createpassfailed" then

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Wrong password</P><A href=Javascript:history.go(-1)>Back</A><BR><BR><A HREF=login.asp>Cancel registration</A></td></tr>"

elseif QStr="namefailed" then

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Invalid username</P><A HREF=login.asp?login=createnew>Click here to create an acount</A><BR><BR><A HREF=Javascript:history.go(-1)>Back</A></td></tr>"

elseif QStr="createnamefailed" then

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Invalid username</P><A HREF=Javascript:history.go(-1)>Back</A><BR><BR><A HREF=login.asp>Cancel registration</A></td></tr>"

elseif QStr="creatednew" then

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Your account has been created</P><A HREF=login.asp>Login</A></td></tr>"

elseif QStr="createnew" then

Content = Content & "<form name=frmCreate method=POST action=create.asp>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Username: <input type=text name=Username></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Name: <input type=text name=FirstName></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Name: <input type=text name=Surname></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Address: <input type=text name=Address></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Phone: <input type=number name=Phonenumber></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Email: <input type=text name=Email></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Password: <input type=text name=Password></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><input type=submit name=cmdSubmit value=Register></td></tr>"

Content = Content & "</form>"

else

Content = Content & "<form name=frmMain method=POST action=verify.asp>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Username: <input type=text name=Username></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Password: <input type=password name=Password></td></tr>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><input type=submit name=cmdSubmit value=Login></td></tr>"

Content = Content & "</form>"

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><A HREF=login.asp?login=createnew>Click here to create an acount</A></td></tr>"

end if

%>



<p>

<br>

<div align="center">

<table border="2" cellspacing="5" bgcolor="<% Response.Write(BackgroundColor) %>" bordercolor="<% Response.Write(BorderColor) %>" width="250px">

<%

Response.Write("<tr><td valign=top align=center><b>" & Title & "</b></td></tr>")

Response.Write(Content) ' Paste the contents in the table

%>

</table>

</div>

</body>

And the Order form

ADDORDER.ASP

<%

'Option Explicit

'==================================================================

' name: AddOrder.asp

'Procedures:

' AddOrderToOrderTable()

' AddItemsToOrderDetails()

' ConvertEmptyToNull()

' DrawPage()

'=============================================================

%>



<%

'-------------------------------------------------------------

' name: AddOrderToOrderTable()

' description: Creates new record in Orders table,

' adds the customer's first name and surname and email to it,

' and gets the value the autonumber field called OrderID

' generated automatically for the new record.

' That OrderID value will be used to add ordered items

' to the OrderDetails table.

'--------------------------------------------------------------

Function AddOrderToOrderTable()

dim newOrderID

dim Todaysdate, username, conn, rs, sql

'------------------------------------------------------

'need to read in the users id name from the login screen and send it to every page

'use the variable "username" to store it

UserID = Session("UserID")

TodaysDate = Date()

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open(Server.Mappath("projectdatabase.mdb"))

set rs=Server.CreateObject("ADODB.recordset")

sql="SELECT * FROM Orders"

rs.Open sql, conn, adOpenDynamic

rs.Movelast

CurrentOrderID = rs("OrderID")

NewOrderID = CurrentOrderID + 1

RS.CLOSE

strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("projectdatabase.mdb") & ";DefaultDir=" & Server.MapPath("projectdatabase.mdb") & ";DriverId=4;FILE=MS Access;MaxBufferSize=512;PageTimeout=5"

strAddNewOrderID = "INSERT INTO Orders (UserID, Cost, Date) VALUES (" & UserID & ", '" & Cost & "', '" & todaysdate &"')"

Set dbConn = Server.CreateObject ("ADODB.Connection")

Set rstAddNewOrderID = Server.CreateObject ("ADODB.Recordset")

dbConn.Open strConnect

rstAddNewOrderID.Open strAddNewOrderID, dbConn

'rs.("UserID") = ("Username")

'rs.("Date") = Date()

'rs.update

'Return new OrderID

AddOrderToOrderTable = newOrderID

End Function

'------------------------------------------

' name: AddItemsToOrderDetails()

'------------------------------------------

Sub AddItemsToOrderDetails(theNewOrderID)

'create the varibles needed

Dim ID

Dim Cost

Dim ProductQuantity

Dim ItemsInCart

Dim ItemNumber

Dim NewQuantity

Dim CurrentQuantity

Dim Name

'Get the number of ordered items in the session variable

itemsInCart = Session("ItemCount")

'Loop through all the items in the shopping cart, and add a new record into

'OrderDetails table for each of the items in the shopping cart.

for itemNumber = 0 to itemsInCart

if ShoppingCart(0, itemNumber) <> "" then

'Get values from shopping cart

ID = ShoppingCart(PRODUCT_ID, itemNumber)

Cost = ShoppingCart(PRODUCT_COST, itemNumber)

OrderQuantity = ShoppingCart(PRODUCT_QUANTITY, itemNumber)

Name = ShoppingCart(PRODUCT_NAME, itemNumber)

'Insert into the table

strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("projectdatabase.mdb") & ";DefaultDir=" & Server.MapPath("projectdatabase.mdb") & ";DriverId=4;FILE=MS Access;MaxBufferSize=512;PageTimeout=5"

strAddOrder = "INSERT INTO OrderDetails (ItemID, Date, Cost, Quantity) VALUES (" & ID & ", '" & Date & "', '" & Cost & "', '" & OrderQuantity & "')"

Set dbConn = Server.CreateObject ("ADODB.Connection")

Set rstAddOrder = Server.CreateObject ("ADODB.Recordset")

dbConn.Open strConnect

rstAddOrder.Open strAddOrder, dbConn

dbConn.close

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("projectdatabase.mdb")

set rs=Server.CreateObject("ADODB.Recordset")

rs.open "SELECT * FROM Albums where AlbumName = '" & AlbumName & "'",conn

'Subtract ordered quantity from current quantity in albums table

CurrentQuantity = rs.fields("Quantity")

NewQuantity = CurrentQuantity - OrderQuantity

conn.close

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("projectdatabase.mdb")

NewQuantity = CurrentQuantity - OrderQuantity

ID = ShoppingCart(PRODUCT_ID, itemNumber)

sql="update Albums set Quantity = " & NewQuantity & " where AlbumName = '" & Name & "'"

set RS = Conn.Execute(sql)

conn.Close

Set RS = Nothing

Set Conn = Nothing

End IF

Next

End Sub

'-------------------------------------------------------

' name: InputIsValid

' description: Checks for invalid data from the user

' entry form.

' Returns True if valid, false if not

'-------------------------------------------------------

Function InputIsValid

dim message, result

'Initialize result to True

result = True

'If any of the incoming values are empty,

'set the message variable indicating problem field, and

'result to False

If FirstName = "" Then

message = "You left First Name empty!"

result = False

ElseIf Surname = "" Then

message = "You left Surname empty!"

result = False

ElseIf EMail = "" Then

message = "You left Email empty!"

result = False

End If

'Set Session message variable

Session("Message") = message

'Return the result

InputIsValid = result

End Function

'---------------------

' name: DrawPage()

'---------------------

Sub DrawPage()

%>

<p align="right">logged in as <% Response.Write(session("username")) %><a href=logout.asp>Logout</a></p>

<p>



<area href="albumsearch.asp" shape="rect" coords="127, 33, 228, 58">

<h3>Thank you for your order, your album's will be sent to you within the next

5-12 days. <%= UserName %><br>

</h3>

<h3>

Your Order Identification Number is <%= OrderID %>. </h3>

<%

End Sub

'------------------------------------------------

' END OF PROCEDURES AND START OF MAIN CODE

'------------------------------------------------

dim OrderID

dim Username

'Get values for new record

UserName = Request.Form("UserName")

'If Shopping Cart doesn't exist already, then create it

If IsEmpty(Session("ShoppingCart")) Then

Session("ShoppingCart") = ShoppingCart

Session("ItemCount") = 0

End If

'Set local array equal to session shopping cart

ShoppingCart = Session("ShoppingCart")

OrderID = AddOrderToOrderTable()

'Now add each item as a new record in the OrderDetails table

Call AddItemsToOrderDetails(OrderID)

'Draw page

Call DrawPage()

'Clear out shopping cart

Session.Abandon

%>

ok, any help big or small will be great! i'm off to try and figure it out myself. oh, the error is:

Microsoft OLE DB Provider for ODBC Driverserror '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. /AddOrder.asp, line 62