Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error in SQL statement

Status
Not open for further replies.
skw8966

skw8966

Programmer
Apr 12, 2001
59
US
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"%>
<!--#include file="Connections/CatReq.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 = ""
%>
<%
' *** 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|State|value|Zip|value|Phone|value|Fax|
value|Authorized|value|Completedby|value|PCsentDate|value|textfield
|value|textfield2|value|textfield3|value|textfield4|value|textfield5|
value|select|value|select2|value|BGCeleb|value|VL|value|Insp|value|
Rbwd|value|SL|value|Xmas|value|AI|value|PD|value|PXmas|value|BXmas|value"
MM_columnsStr = "dtDate|',none,NULL|AcctNo|',none,'|CoName|',none,'|ShipAdd|',none,
'|Email|',none,'|MailAdd|',none,'|Owner|',none,'|ShipCity|',none,
'|ShipState|',none,'|ShipZip|',none,'|Phone|',none,'|Fax|',none,
'|Auth|',none,'|Formmadeby|',none,'|Postcarddate|',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,'|BGCeleb|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|none,
'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"><font size="4"><strong>Catalog Request</strong></font></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").Value)%>" size="40">
</td>
<td colspan="2"><input name="State" type="text" id="State" value="<%=(Recordset1.Fields.Item("ShipState").Value)%>" 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").Value)%>"></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"> <hr></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").Value)%>"></td>
<td>Business Christmas</td>
<td><input name="BXmas" type="checkbox" id="BXmas" value="<%=(Recordset1.Fields.Item("MBusXmas").Value)%>"></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
%>
 
Sort by date Sort by votes
Looks like you have required fields that are not being populated or its a datatype problem (ie trying to insert text into a numerical field)

Cheech

[Peace][Pipe]
 
Upvote 0 Downvote
None of the fields are required and I've checked the datatypes. Everything seems fine.
 
Upvote 0 Downvote
It appears to be related to the CY, PY, 2Y, 3Y, 4Y fields.

They are in currency format in MS Access. I made them currency format in Dreamweaver and get the error. If I set format to None, it works fine. I'd really like to display these fields in currency format though. They are sales figures.

Thanks.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question
Displaying results of an SQL Pivot Query
Replies
1
Views
646
jdhilljr
jdhilljr
miagriff
  • Locked
  • Question
Coldfusion if statement syntax
Replies
1
Views
663
LyndonOHRC
LyndonOHRC
FALCONSEYE
  • Locked
  • Question
Error: no known VMs. (check for corrupt jvm.cfg file) !!! How I Fixed It !!!
Replies
0
Views
825
FALCONSEYE
FALCONSEYE
HansKok
  • Locked
  • Question
Error after only changing text and not codes
Replies
0
Views
580
HansKok
HansKok
mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
543
mdvp
mdvp

Part and Inventory Search

Sponsor

Back
Top