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?
that was my first message, after consulting with many in forums, here is what I came up with.
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
delpro.Open()
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
%>