am trying to delete a record but I have this message: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ProductID = , 1567'. /salem/pages/fresh_food/TMP3rfr35u5pa.ASP, line 74 I looked at the meaning but so far I can't see that there is no reserved words I used or space, any idea? thanks
You want to delete record having ProductID = 1567 if ProductID is numeric then query expression should be "ProductID = 1567" else then query expression should be "ProductID = '1567'" end if
okay, this isn't simple as that, it's not about numeric...here is my code, I have made some changes (from instructions from other people in the forum)and may be you could spot where the error is just to get it delete.i did highlight where the changes where made DELPRODUCT.asp PAGE <%@LANGUAGE="VBSCRIPT"%> <!--#include file="Connections/connection1.asp" --> <% ' *** Edit Operations: declare variables Dim MM_editAction Dim MM_abortEdit Dim MM_editQuery Dim MM_editCmd Dim MM_editConnection Dim MM_editTable Dim MM_editRedirectUrl Dim MM_editColumn Dim MM_recordId Dim MM_fieldsStr Dim MM_columnsStr Dim MM_fields Dim MM_columns Dim MM_typeArray Dim MM_formVal Dim MM_delim Dim MM_altVal Dim MM_emptyVal Dim MM_i MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME")) If (Request.QueryString <> "") Then MM_editAction = MM_editAction & "?" & Request.QueryString End If ' boolean to abort record edit MM_abortEdit = false ' query string to execute MM_editQuery = "" %> <% ' *** Delete Record: declare variables if (CStr(Request("MM_delete")) = "form1" And CStr(Request("MM_recordId")) <> "") Then MM_editConnection = MM_connection1_STRING MM_editTable = "Products" MM_editColumn = "ProductID" MM_recordId = "" + Request.Form("MM_recordId") + "" MM_editRedirectUrl = "delproduct.asp" ' append the query string to the redirect URL If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString Else MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString End If End If End If %> <% ' *** Delete Record: construct a sql delete statement and execute it If (CStr(Request("MM_delete")) <> "" And CStr(Request("MM_recordId")) <> "") Then ' create the sql delete statement MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId If (Not MM_abortEdit) Then ' execute the delete Set MM_editCmd = Server.CreateObject("ADODB.Command") MM_editCmd.ActiveConnection = MM_editConnection MM_editCmd.CommandText = MM_editQuery MM_editCmd.Execute MM_editCmd.ActiveConnection.Close If (MM_editRedirectUrl <> "") Then Response.Redirect(MM_editRedirectUrl) End If End If End If %> <% Dim delpro__MMColParam delpro__MMColParam = "1" If (Request.Form("ProductID") <> "") Then delpro__MMColParam = Request.Form("ProductID") End If %> <% Dim delpro Dim delpro_numRows Set delpro = Server.CreateObject("ADODB.Recordset") delpro.ActiveConnection = MM_connection1_STRING delpro.Source = "SELECT ProductID FROM Products WHERE ProductID = " + Replace(delpro__MMColParam, "'", "''") + ""delpro.CursorType = 0 delpro.CursorLocation = 2 delpro.LockType = 1 delprpen() delpro_numRows = 0 %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="JavaScript" type="text/JavaScript"> <!-- function MM_reloadPage(init) { //reloads the window if Nav4 resized if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) { document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }} else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload(); } MM_reloadPage(true); //--> </script> </head> <body> <div id="Layer1" style="position:absolute; left:4px; top:149px; width:759px; height:292px; z-index:7"> <form name="form1" method="POST" action="<%=MM_editAction%>"> <table width="75%" border="1" cellpadding="2"> <tr> <td>product id</td> <td> <input type="text" name="textfield"></td> </tr> <tr> <td> </td> <td> <input type="submit" name="Submit" value="del"> </td> </tr> </table> <input type="hidden" name="MM_delete" value="form1"> <input type="hidden" name="MM_recordId" value="<%= delpro.Fields.Item("ProductID").Value %>"> </form> </div> <div id="Layer6" style="position:absolute; left:7px; top:15px; width:756px; height:117px; z-index:6"> <table width="100%" border="1" bordercolor="#FFFFFF" bgcolor="#FFFFFF"> <tr> <td width="28%" height="102"><p align="center"><strong><font color="#009900" size="6">Top Value Supermarket</font></strong></p></td> <td width="3%" bgcolor="#009900"><div align="center"></div></td> <td width="3%" bgcolor="#00CC00"><div align="center"></div></td> <td width="7%" bgcolor="#00FF00"><div align="center"></div></td> <td width="59%" bgcolor="#00FF99"><p align="center"><strong><font color="#FF0000" size="5" face="Blackadder ITC">Value always...in your hands</font></strong></p> <p align="center"><strong>| Home | Online Shopping | Site Map | Contact US | My Account | </strong></p></td> </tr> </table> </div> </body> </html> <% delpro.Close() Set delpro = Nothing %> this is another example from another page deleteproducts.ASP page <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%> <!--#include file="../../Connections/connection1.asp" --> <% ' *** Edit Operations: declare variables Dim MM_editAction Dim MM_abortEdit Dim MM_editQuery Dim MM_editCmd Dim MM_editConnection Dim MM_editTable Dim MM_editRedirectUrl Dim MM_editColumn Dim MM_recordId Dim MM_fieldsStr Dim MM_columnsStr Dim MM_fields Dim MM_columns Dim MM_typeArray Dim MM_formVal Dim MM_delim Dim MM_altVal Dim MM_emptyVal Dim MM_i MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME")) If (Request.QueryString <> "") Then MM_editAction = MM_editAction & "?" & Request.QueryString End If ' boolean to abort record edit MM_abortEdit = false ' query string to execute MM_editQuery = "" %> <% ' *** Delete Record: declare variables if (CStr(Request("MM_delete")) = "DELETEPRODUCT" And CStr(Request("MM_recordId")) <> "") Then MM_editConnection = MM_connection1_STRING MM_editTable = "Products" MM_editColumn = "ProductID" MM_recordId = "" + Request.Form("MM_recordId") + "" MM_editRedirectUrl = "DELETEPRODUCT.ASP" ' append the query string to the redirect URL If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString Else MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString End If End If End If %> <% ' *** Delete Record: construct a sql delete statement and execute it If (CStr(Request("MM_delete")) <> "" And CStr(Request("MM_recordId")) <> "") Then ' create the sql delete statement MM_editQuery = "delete from " & MM_editTable & " where " & MM_editColumn & " = " & MM_recordId If (Not MM_abortEdit) Then ' execute the delete Set MM_editCmd = Server.CreateObject("ADODB.Command") MM_editCmd.ActiveConnection = MM_editConnection MM_editCmd.CommandText = MM_editQuery Response.Write "SQL= " & MM_editQuery Response.End() MM_editCmd.Execute MM_editCmd.ActiveConnection.Close If (MM_editRedirectUrl <> "") Then Response.Redirect(MM_editRedirectUrl) End If End If End If %> <% Dim Recordsetdelete Dim Recordsetdelete_numRows Set Recordsetdelete = Server.CreateObject("ADODB.Recordset") Recordsetdelete.ActiveConnection = MM_connection1_STRING Recordsetdelete.Source = "SELECT ProductID FROM Products" Recordsetdelete.CursorType = 0 Recordsetdelete.CursorLocation = 2 Recordsetdelete.LockType = 1 Recordsetdelete.Open() Recordsetdelete_numRows = 0 %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="JavaScript" type="text/JavaScript"> <!-- function MM_reloadPage(init) { //reloads the window if Nav4 resized if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) { document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }} else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload(); } MM_reloadPage(true); //--> </script> </head> <body> <div id="Layer6" style="position:absolute; left:7px; top:15px; width:756px; height:117px; z-index:6"> <table width="100%" border="1" bordercolor="#FFFFFF" bgcolor="#FFFFFF"> <tr> <td width="28%" height="102"><p align="center"><strong><font color="#009900" size="6">Top Value Supermarket</font></strong></p></td> <td width="3%" bgcolor="#009900"><div align="center"></div></td> <td width="3%" bgcolor="#00CC00"><div align="center"></div></td> <td width="7%" bgcolor="#00FF00"><div align="center"></div></td> <td width="59%" bgcolor="#00FF99"><p align="center"><strong><font color="#FF0000" size="5" face="Blackadder ITC">Value always...in your hands</font></strong></p> <p align="center"><strong>| Home | Online Shopping | Site Map | Contact US | My Account | </strong></p></td> </tr> </table> </div> <div id="Layer1" style="position:absolute; left:16px; top:153px; width:188px; height:274px; z-index:7"> <table width="100%" height="264" border="1" bordercolor="#FFFFFF"> <tr> <td height="43">ADD PRODUCT</td> </tr> <tr> <td>DELETE PRODUCT</td> </tr> <tr> <td>UPDATE PRODUCT</td> </tr> <tr> <td>RETURNED PRODUCTS</td> </tr> <tr> <td>UNSOLD PRODUCTS</td> </tr> <tr> <td>VIEW PRODUCT</td> </tr> </table> </div> PRODUCT ID PRODUCT NAME PRODUCT DESCRIPTION PRODUCT PRICE STOCK ID IMAGE <div id="Layer2" style="position:absolute; left:222px; top:154px; width:541px; height:276px; z-index:8"> <form ACTION="<%=MM_editAction%>" METHOD="POST" name="DELETEPRODUCT" id="DELETEPRODUCT"> <table width="75%" border="1" bordercolor="#00FF99" bgcolor="#00FF99"> <tr> <td width="51%">PRODUCT ID:</td> <td width="49%"> <input name="ProductID" type="text" id="ProductID"></td> </tr> </table> <p align="center"> <input type="submit" name="Submit" value="DELETE PRODUCT"> </p> <input type="hidden" name="MM_recordId"> <input type="hidden" name="MM_delete" value="DELETEPRODUCT"> <input type="hidden" name="MM_recordId" value="<%= Replace (Recordsetdelete.Fields.Item("ProductID").Value , ",", "")%>"> </form> </div> </body> </html> <% Recordsetdelete.Close() Set Recordsetdelete = Nothing %>
Have you tried taking out the last comma in this line? : delpro.Source = "SELECT ProductID FROM Products WHERE ProductID = " + Replace(delpro__MMColParam, "'", "''") + "" I didn't take a very deep look at the code, but that's the first thing that jumps out to me.
You did not mentioned the datatype of productID in your database if productID is numeric delpro.Source = "SELECT ProductID FROM Products WHERE ProductID = " + Replace(delpro__MMColParam, "'", "''") + "" else ' productID is varchar delpro.Source = "SELECT ProductID FROM Products WHERE ProductID = '" + Replace(delpro__MMColParam, "'", "''") + "'"