1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

error

Discussion in 'C#' started by salim, Sep 25, 2006.

0
  1. #1
    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
     
    salim, Sep 25, 2006 IP
  2. kamrag

    kamrag Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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
     
    kamrag, Sep 25, 2006 IP
  3. shaileshk

    shaileshk Well-Known Member

    Messages:
    455
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    108
    #3
    'ProductID = , 1567'.
    Have erroor '

    pls post you SQL Syntex and Code so we can help
     
    shaileshk, Sep 26, 2006 IP
  4. salim

    salim Peon

    Messages:
    52
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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
    delpr:confused:pen()

    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>&nbsp;</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
    %>
     
    salim, Sep 26, 2006 IP
  5. rb3m

    rb3m Peon

    Messages:
    192
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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.
     
    rb3m, Sep 28, 2006 IP
  6. honeydesign

    honeydesign Peon

    Messages:
    62
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    WHERE ProductID = "
    to
    WHERE ProductID = '"

    becuase productID is string , you must have the '.
     
    honeydesign, Sep 29, 2006 IP
  7. kamrag

    kamrag Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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, "'", "''") + "'"
     
    kamrag, Sep 29, 2006 IP