PDA

View Full Version : SQL error


skwilliams
12-01-2005, 01:44 PM
<span style='color:red'>reformated post coz it broke the forum -davidj</span>

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|valu e|State|value|Zip|
value|Phone|value|Fax|value|Authorized|value|Compl etedby|value|
PCsentDate|value|textfield|value|textfield2|value| textfield3|value|
textfield4|value|textfield5|value|select|value|sel ect2|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|',n one,''|Fax|',none,
''|Auth|',none,''|Formmadeby|',none,''|Postcarddat e|',none,
NULL|CY|none,none,NULL|PY|none,none,NULL|2Y|none,n one,
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'|MPerX mas|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></td>
<td></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"> <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").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
%>

davidj
12-01-2005, 02:12 PM
it could be that your adding ' inside Its ('If It''s Paper')

CoName = 'If It''s Paper'

needs to be

CoName = 'If Its Paper'

Creative Insanity
12-01-2005, 05:44 PM
dj is correct, you cannot have it's as the ' is read as an open quote by the server and it will expect a close quote as well. Just use if it is paper.

davidj
12-06-2005, 12:32 PM
dj is correct,

of course i am. That goes without saying

domedia
12-06-2005, 01:23 PM
[color=red]reformated post coz it broke the forum -davidj[/color]
lol is that true? man, we *really* need an update ;-)

davidj
12-06-2005, 01:27 PM
lol is that true? man, we *really* need an update ;-)

if you paste a code example the forum formats it as a 1 line string so in this example there was about 3 km of horizontal scrolling!

skwilliams
12-06-2005, 02:25 PM
Thanks.