Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  query error in ASP using Access

Author  Topic  mrjones Starting Member 6 Posts Posted - 2001-11-16 : 00:49:15 I am writing ASP with an Access database. Can anyone help me get past this error message that I keep wrestling with? I am getting the following error message on an asp page connected to an access database: ---------------------------------------------------------------- 80004005|Operation_must_use_an_updateable_query. ---------------------------------------------------------------- The specific query language that generates this error is: ---------------------------------------------------------------- ' Add Item to cart IF productID <> "" THEN sqlString = "SELECT cart_id FROM cart " &_ "WHERE cart_userID=" & user_id & " " &_ "AND cart_productID=" & productID SET RS = Conn.Execute( sqlString ) IF RS.EOF THEN sqlString = "INSERT INTO cart ( " &_ "cart_userID, " &_ "cart_productID, " &_ "cart_quantity " &_ ") VALUES ( " &_ user_id & ", " &_ productID & ", 1 )" ELSE sqlString = "UPDATE cart SET " &_ "cart_quantity=cart_quantity+1 " &_ "WHERE cart_id=" & RS( "cart_id" ) END IF RS.Close SET RS = Nothing Conn.Execute sqlString END IF ---------------------------------------------------------------- The location of the error is the Conn.Execute... line, so the actual error itself could be anywhere in the SQL coding. In case you are wondering, the underlying "cart" table in the access database has the following fields: ---------------------------------------------------------------- Field name Data type cart_id autonumber that is primary key cart_userID number associating the cart with a customer cart_productID number containing productID of a cart item cart_quantity number containing the quantity of the item ---------------------------------------------------------------- I did a response.write for each of the three conditional instances of sqlStatement in order, and got the following results: ------------------------------------------------------------- Response.Write for the first instance of sqlStatement successfully generated: SELECT cart_id FROM cart WHERE cart_userID=33 AND cart_productID= ------------------------------------------------------------- Response.Write for the second instance of sqlStatement successfully generated: INSERT INTO cart ( cart_userID, cart_productID, cart_quantity ) VALUES ( , , 1 ) -------------------------------------------------------------- Response.Write for the 3rd instance of sqlStatement generated the following error in the log file: 80040e14|Syntax_error_(missing_operator)_in_query_expression_'cart_userID=33_AND_cart_productID=' -------------------------------------------------------------- does this info help you diagnose the problem and recommend a fix? Also, if you are super curious, the entire code for the page that generates the error summarized above is: ---------------------------------------------------------------- <% Response.Buffer = True If Request.Cookies("UserInfo")("userLevel") < 1 Then Response.Redirect "register.asp?" & Request.ServerVariables("SCRIPT_NAME") End If Dim userLevel userLevel = Request.Cookies("UserInfo")("userLevel") Dim username username = Request.Cookies("UserInfo")("username") Dim password password = Request.Cookies("UserInfo")("password") Dim user_id user_id = Request.Cookies("UserInfo")("user_id") ' Get Product ID productID = TRIM( Request( "pid" ) ) ' Open Database Connection Dataconn = "database path--removed here for security reasons" ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "& Dataconn Set Conn = Server.CreateObject("ADODB.Connection") Conn.open(ConStr) ' Add Item to cart IF productID <> "" THEN sqlString = "SELECT cart_id FROM cart " &_ "WHERE cart_userID=" & user_id & " " &_ "AND cart_productID=" & productID SET RS = Conn.Execute( sqlString ) IF RS.EOF THEN sqlString = "INSERT INTO cart ( " &_ "cart_userID, " &_ "cart_productID, " &_ "cart_quantity " &_ ") VALUES ( " &_ user_id & ", " &_ productID & ", 1 )" ELSE sqlString = "UPDATE cart SET " &_ "cart_quantity=cart_quantity+1 " &_ "WHERE cart_id=" & RS( "cart_id" ) END IF RS.Close SET RS = Nothing Conn.Execute sqlString END IF ' Update Shopping Cart Quantities IF Request( "updateQ" ) <> "" THEN SET RS = Server.CreateObject( "ADODB.Recordset" ) RS.ActiveConnection = Conn RS.CursorType = adOpenDynamic RS.LockType = adLockOptimistic sqlString = "SELECT cart_id, cart_quantity FROM cart " &_ "WHERE cart_userID=" & user_id RS.Open sqlString WHILE NOT RS.EOF newQ = TRIM( Request( "pq" & RS( "cart_id" ) ) ) IF newQ = "" OR newQ = "0" THEN RS.Delete ELSE IF isNumeric( newQ ) THEN RS( "cart_quantity" ) = newQ END IF END IF RS.MoveNext WEND RS.Close SET RS = Nothing END IF %> <html> <head><title>Menu Builder</title> </head> <!-- #INCLUDE FILE="basichtml.asp" --> <center> <!-- #INCLUDE FILE="toptable.asp" --> <font face="Comic Sans MS" size="3" color="#FFFF00"> <b><%=username%>'s Menu:</b> </font> <% ' Get the shopping cart sqlString = "SELECT cart_id, product_name, " &_ "cart_quantity, product_category " &_ "FROM cart, products " &_ "WHERE cart_userID = " & user_id & " "&_ "AND product_id = " & productID & " "&_ "ORDER BY product_category DESC" SET RS = Conn.Execute( sqlString ) IF RS.EOF THEN %> <p><font face="Comic Sans MS" size="3" color="#FFFF00"><b>You do not have any items in your shopping cart</b> </font><p> <form action="default.asp"> <input type="submit" value="Continue Shopping"> </form> <% ELSE orderTotal = 0 %> <form method="post" action="cart.asp"> <input name="updateQ" type="hidden" value="1"> <input name="username" type="hidden" value="<%=username%>"> <input name="password" type="hidden" value="<%=password%>"> <table border=1 cellpadding=4 cellspacing=0> <tr bgcolor="darkgreen"> <th><font face="Comic Sans MS" size="3" color="#FFFF00">Category</th></font> <th><font face="Comic Sans MS" size="3" color="#FFFF00">Menu Item</th></font> <th><font face="Comic Sans MS" size="3" color="#FFFF00">Servings</th></font> </tr> <% WHILE NOT RS.EOF orderTotal = orderTotal + RS( "cart_quantity" ) %> <tr> <td><font face="Comic Sans MS" size="3" color="#FFFF00"> <%=Server.HTMLEncode( RS( "product_category" ) )%> </td> <td><font face="Comic Sans MS" size="3" color="#FFFF00"> <%=Server.HTMLEncode( RS( "product_name" ) )%> </td> <td> <input name="pq<%=RS( "cart_id" )%>" type="text" size=4 value="<%=RS( "cart_quantity" )%>"> </td> </tr></font> <% RS.MoveNext WEND %> <tr> <td colspan=3> <table border=0> <tr> <td align="right"> <input type="submit" value="Update Cart"> </td> </form> <form method="post" action="checkout.asp"> <input name="username" type="hidden" value="<%=username%>"> <input name="password" type="hidden" value="<%=password%>"> <td> <input type="submit" value="Checkout"> </td> </form> <form action="default.asp"> <td> <input type="submit" value="Continue Shopping"> </td> </form> </tr> </table> </td> </tr></font> </table> <% END IF %> </center> </body> </html>