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
The comma....is that on purpose or...?
'ProductID = [B],[/B] 1567'If "ProductID" is a number field only numbers are allowed.
If "ProductID" is a text field the value should be enclosed in "'"
well it's a number...I am using dreamweaver with some server beahaviour
am trying to delete a record but I have iniatially thismessage:
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 what I aam getting
then with some changes I had this message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ProductID = <font face='.
/salem/TMP4fbu36742n.asp, line 74
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"%>
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>
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>
<<%=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>
<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
%>
NOTE I DID GET RID OF SOME TAGS JUST TO GET SPACE TO BE ABBLE TO SEND THIS MESSAGE
delpro.Source = "SELECT ProductID FROM Products WHERE ProductID = " + Replace(delpro__MMColParam, "'", "''")
You're replacing a single qoute with two single qoutes....that doesn't help.
Single qoutes cannot exist in values, since the represent start and end tags in sql.
These are correct:
SELECT ProductID FROM Products WHERE ProductID = 4
SELECT ProductText FROM Products WHERE ProductText LIKE '%smth%'
These are incorrect:
SELECT ProductID FROM Products WHERE ProductID = '4'
SELECT ProductID FROM Products WHERE ProductID = 4,
SELECT ProductText FROM Products WHERE ProductText LIKE '%doesn't%'
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.