Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2005, 11:02 AM
Authorized User
 
Join Date: Dec 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Problem

I have an update page in ASP tied to an MS Access database.

When submitting the update, I receive this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '$4'.
/CatReq/EditReq.asp, line 111


which refers to this line of code:

MM_editCmd.Execute

I added a response.write to it and get this:

SQL Statement is update tblCatReq set dtDate = '11/8/2005',AcctNo = '9760',CoName = 'If It''s Paper',ShipAdd = '4413 Brainerd Road',Email = '',MailAdd = '',Owner = '?, Joe',ShipCity = 'Chattanooga',ShipState = 'TN',ShipZip = '37411-5427',Phone = '423-622-0741',Fax = '',Auth = 'Joe',Formmadeby = 'mh',Postcarddate = NULL,CY = $4,089.41,PY = $13.80,2Y = $0.00,3Y = $0.00,4Y = $0.00,Referral = 'Senttowebsite',Cost = 'NoCharge',BGCeleb = 'N',VwL = 'N',Insp = 'Y',Rbwd = 'N',Stylart = 'N',Xmas = 'N',AI = 'N',PD = 'N',MPerXmas = 'N',MBusXmas = 'N' where pkID = 2

Any idea what my problem is?

Below is the code for the page.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
' *** 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 = ""
%>
<%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_CatReq_STRING
  MM_editTable = "tblCatReq"
  MM_editColumn = "pkID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "ListView.asp"
  MM_fieldsStr = "dtDate|value|Acct|value|txtName|value|Add1|value| Email|value|Add2|value|Owner|value|City|value|Stat e|value|Zip|value|Phone|value|Fax|value|Authorized |value|Completedby|value|PCsentDate|value|textfiel d|value|textfield2|value|textfield3|value|textfiel d4|value|textfield5|value|select|value|select2|val ue|BGCeleb|value|VL|value|Insp|value|Rbwd|value|SL |value|Xmas|value|AI|value|PD|value|PXmas|value|BX mas|value"
  MM_columnsStr = "dtDate|',none,NULL|AcctNo|',none,''|CoName|',none ,''|ShipAdd|',none,''|Email|',none,''|MailAdd|',no ne,''|Owner|',none,''|ShipCity|',none,''|ShipState |',none,''|ShipZip|',none,''|Phone|',none,''|Fax|' ,none,''|Auth|',none,''|Formmadeby|',none,''|Postc arddate|',none,NULL|CY|none,none,NULL|PY|none,none ,NULL|2Y|none,none,NULL|3Y|none,none,NULL|4Y|none, none,NULL|Referral|',none,''|Cost|',none,''|BGCele b|none,'Y','N'|VwL|none,'Y','N'|Insp|none,'Y','N'| Rbwd|none,'Y','N'|Stylart|none,'Y','N'|Xmas|none,' Y','N'|AI|none,'Y','N'|PD|none,'Y','N'|MPerXmas|no ne,'Y','N'|MBusXmas|none,'Y','N'"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")

  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

  ' 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
%>
<%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  Next
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    Response.Write("SQL Statement is " & 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 Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("IDNo") <> "") Then
  Recordset1__MMColParam = Request.QueryString("IDNo")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CatReq_STRING
Recordset1.Source = "SELECT * FROM tblCatReq WHERE pkID = " + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<html>
<head>
<title>Edit a Catalog Request</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="POST" action="<%=MM_editAction%>">
  <table width="90%" border="0" cellpadding="0" cellspacing="0">
    <tr>
      <td colspan="4"> <div align="center"><strong>Catalog Request</strong></div></td>
    </tr>
    <tr>
      <td width="6%">Date:</td>
      <td width="35%"><input name="dtDate" type="text" id="dtDate" value="<%=(Recordset1.Fields.Item("dtDate").Value) %>"></td>
      <td width="22%"><div align="right"> Acct</div></td>
      <td width="37%"><input name="Acct" type="text" id="Acct" value="<%=(Recordset1.Fields.Item("AcctNo").Value) %>"></td>
    </tr>
    <tr>
      <td>Name: </td>
      <td><input name="txtName" type="text" id="txtName" value="<%=(Recordset1.Fields.Item("CoName").Value) %>" size="40"></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>Add1</td>
      <td><input name="Add1" type="text" id="Add1" value="<%=(Recordset1.Fields.Item("ShipAdd").Value )%>" size="40">
        <div align="left"> </div></td>
      <td colspan="2"><div align="right">Email
          <input name="Email" type="text" id="Email" value="<%=(Recordset1.Fields.Item("Email").Value)% >">
        </div></td>
    </tr>
    <tr>
      <td>Mail</td>
      <td><input name="Add2" type="text" id="Add2" value="<%=(Recordset1.Fields.Item("MailAdd").Value )%>" size="40"></td>
      <td colspan="2"><div align="right">Owner
          <input name="Owner" type="text" id="Owner" value="<%=(Recordset1.Fields.Item("Owner").Value)% >" size="40">
        </div></td>
    </tr>
    <tr>
      <td>CSZ</td>
      <td><input name="City" type="text" id="City" value="<%=(Recordset1.Fields.Item("ShipCity").Valu e)%>" size="40">
      </td>
      <td colspan="2"><input name="State" type="text" id="State" value="<%=(Recordset1.Fields.Item("ShipState").Val ue)%>" size="5">
        <input name="Zip" type="text" id="Zip" value="<%=(Recordset1.Fields.Item("ShipZip").Value )%>" size="15">
      </td>
    </tr>
    <tr>
      <td>Phone</td>
      <td><input name="Phone" type="text" id="Phone" value="<%=(Recordset1.Fields.Item("Phone").Value)% >"></td>
      <td colspan="2">Fax
        <input name="Fax" type="text" id="Fax" value="<%=(Recordset1.Fields.Item("Fax").Value)%>" ></td>
    </tr>
    <tr>
      <td colspan="2">Person who authorized sending book</td>
      <td colspan="2"><input name="Authorized" type="text" id="Authorized" value="<%=(Recordset1.Fields.Item("Auth").Value)%> " size="30">
      </td>
    </tr>
    <tr>
      <td colspan="2">Form made out by
        <input name="Completedby" type="text" id="Completedby" value="<%=(Recordset1.Fields.Item("Formmadeby").Va lue)%>"></td>
      <td colspan="2">Postcard sent date
        <input name="PCsentDate" type="text" id="PCsentDate" value="<%=(Recordset1.Fields.Item("Postcarddate"). Value)%>"></td>
    </tr>
    <tr>
      <td colspan="4"> </td>
    </tr>
    <tr>
      <td colspan="4"><table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td colspan="5"><div align="center">Sales</div></td>
          </tr>
          <tr>
            <td width="100"> <div align="center">2005</div></td>
            <td width="100"> <div align="center">2004</div></td>
            <td width="100"> <div align="center">2003</div></td>
            <td width="100"> <div align="center">2002</div></td>
            <td width="100"> <div align="center">2001</div></td>
          </tr>
          <tr>
            <td width="100"> <div align="center">
                <input name="textfield" type="text" value="<%= FormatCurrency((Recordset1.Fields.Item("CY").Value ), 2, -2, -2, -2) %>">
              </div></td>
            <td width="100"> <div align="center">
                <input name="textfield2" type="text" value="<%= FormatCurrency((Recordset1.Fields.Item("PY").Value ), 2, -2, -2, -2) %>">
              </div></td>
            <td width="100"> <div align="center">
                <input name="textfield3" type="text" value="<%= FormatCurrency((Recordset1.Fields.Item("2Y").Value ), 2, -2, -2, -2) %>">
              </div></td>
            <td width="100"> <div align="center">
                <input name="textfield4" type="text" value="<%= FormatCurrency((Recordset1.Fields.Item("3Y").Value ), 2, -2, -2, -2) %>">
              </div></td>
            <td width="100"> <div align="center">
                <input name="textfield5" type="text" value="<%= FormatCurrency((Recordset1.Fields.Item("4Y").Value ), 2, -2, -2, -2) %>">
              </div></td>
          </tr>
        </table></td>
    </tr>
    <tr>
      <td colspan="3"> <table width="200">
          <tr>
            <td><label> Referral</label> <label>
              <select name="select">
                <option value="Telemarketed" <%If (Not isNull((Recordset1.Fields.Item("Referral").Value)) ) Then If ("Telemarketed" = CStr((Recordset1.Fields.Item("Referral").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Telemarketed</option>
                <option value="CalledIn" <%If (Not isNull((Recordset1.Fields.Item("Referral").Value)) ) Then If ("CalledIn" = CStr((Recordset1.Fields.Item("Referral").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Called
                In</option>
                <option value="Senttowebsite" <%If (Not isNull((Recordset1.Fields.Item("Referral").Value)) ) Then If ("Senttowebsite" = CStr((Recordset1.Fields.Item("Referral").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Sent
                to website</option>
                <option value="Emaildlrreq" <%If (Not isNull((Recordset1.Fields.Item("Referral").Value)) ) Then If ("Emaildlrreq" = CStr((Recordset1.Fields.Item("Referral").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Email
                dealer request</option>
                <%
While (NOT Recordset1.EOF)
%>
                <option value="<%=(Recordset1.Fields.Item("pkID").Value)%> " <%If (Not isNull((Recordset1.Fields.Item("Referral").Value)) ) Then If (CStr(Recordset1.Fields.Item("pkID").Value) = CStr((Recordset1.Fields.Item("Referral").Value))) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(Recordset1.Fields.Item("pkID").Value)%></option>
                <%
  Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
  Recordset1.MoveFirst
Else
  Recordset1.Requery
End If
%>
              </select>
              </label> <label> </label> <label> </label></td>
          </tr>
        </table></td>
      <td><table width="200">
          <tr>
            <td><label> Cost</label> <select name="select2">
                <option value="Charge" <%If (Not isNull((Recordset1.Fields.Item("Cost").Value))) Then If ("Charge" = CStr((Recordset1.Fields.Item("Cost").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>Charge</option>
                <option value="NoCharge" <%If (Not isNull((Recordset1.Fields.Item("Cost").Value))) Then If ("NoCharge" = CStr((Recordset1.Fields.Item("Cost").Value))) Then Response.Write("SELECTED") : Response.Write("")%>>No
                Charge</option>
                <%
While (NOT Recordset1.EOF)
%>
                <option value="<%=(Recordset1.Fields.Item("pkID").Value)%> " <%If (Not isNull((Recordset1.Fields.Item("Cost").Value))) Then If (CStr(Recordset1.Fields.Item("pkID").Value) = CStr((Recordset1.Fields.Item("Cost").Value))) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(Recordset1.Fields.Item("pkID").Value)%></option>
                <%
  Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
  Recordset1.MoveFirst
Else
  Recordset1.Requery
End If
%>
              </select> <label> </label></td>
          </tr>
        </table></td>
    </tr>
    <tr>
      <td colspan="4"> <table width="47%" border="1" align="center" cellpadding="0" cellspacing="0">
          <tr>
            <td colspan="4"><div align="center"><strong>Catalogs</strong></div></td>
          </tr>
          <tr>
            <td width="36%">BG Celebrations</td>
            <td width="13%"><input name="BGCeleb" type="checkbox" id="BGCeleb" value="<%=(Recordset1.Fields.Item("BGCeleb").Value )%>"></td>
            <td width="40%">Value with Love</td>
            <td width="11%"><input name="VL" type="checkbox" id="VL" value="<%=(Recordset1.Fields.Item("VwL").Value)%>" ></td>
          </tr>
          <tr>
            <td>Inspirations</td>
            <td><input name="Insp" type="checkbox" id="Insp" value="<%=(Recordset1.Fields.Item("Insp").Value)%> "></td>
            <td>Rainboworld</td>
            <td><input name="Rbwd" type="checkbox" id="Rbwd" value="<%=(Recordset1.Fields.Item("Rbwd").Value)%> "></td>
          </tr>
          <tr>
            <td>Stylart</td>
            <td><input name="SL" type="checkbox" id="SL" value="<%=(Recordset1.Fields.Item("Stylart").Value )%>"></td>
            <td>Christmas</td>
            <td><input name="Xmas" type="checkbox" id="Xmas" value="<%=(Recordset1.Fields.Item("Xmas").Value)%> "></td>
          </tr>
          <tr>
            <td>Affordably Inviting</td>
            <td><input name="AI" type="checkbox" id="AI" value="<%=(Recordset1.Fields.Item("AI").Value)%>"> </td>
            <td>Paper Duvet</td>
            <td><input name="PD" type="checkbox" id="PD" value="<%=(Recordset1.Fields.Item("PD").Value)%>"> </td>
          </tr>
          <tr>
            <td>Personal Christmas</td>
            <td><input name="PXmas" type="checkbox" id="PXmas" value="<%=(Recordset1.Fields.Item("MPerXmas").Valu e)%>"></td>
            <td>Business Christmas</td>
            <td><input name="BXmas" type="checkbox" id="BXmas" value="<%=(Recordset1.Fields.Item("MBusXmas").Valu e)%>"></td>
          </tr>
          <tr>
            <td colspan="4"><div align="center">
                <input type="submit" name="Submit" value="Submit">
              </div></td>
          </tr>
        </table></td>
    </tr>
    <tr>
      <td colspan="4"><div align="center">Home</div></td>
    </tr>
  </table>
  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("pkID").Value %>">
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>






Similar Threads
Thread Thread Starter Forum Replies Last Post
Update problem aceaceace Visual Basic 2005 Basics 6 February 21st, 2007 10:25 PM
Update problem filipczako ADO.NET 6 December 15th, 2005 11:58 AM
update problem lamdog ASP.NET 1.0 and 1.1 Basics 1 March 15th, 2005 11:59 PM
Update problem acko SQL Server 2000 3 June 29th, 2004 01:50 AM
update problem Justine Classic ASP Databases 21 May 26th, 2004 04:25 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.