Hi

I am getting an error in updating record.

MS Access + DWMX + ASP

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/DRE_Web/admin_sales_edit.asp, line 111

Can't figure it out.

Please any one help me.


Babu

Post your code Mashkur and any other relevant information you may have.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Not the whole thing if it's long, just around that error line, please. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

~ ~ ~ ~ ~ ~ ~
Chris Charlton <i>- DMXzone Manager</i>
<font size=1>[ Studio MX/MX2004 | PHP/ASP | SQL | XHTML/CSS | XML | Actionscript | Web Accessibility | MX Extensibility ]</font id=size1>

Hi

Thanks for reply. Here is the code inside Update page.

&lt;%
' *** 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 &lt;&gt; "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%&gt;
&lt;%
' *** Update Record: set variables

If (CStr(Request("MM_update") = "addnew" And CStr(Request("MM_recordId") &lt;&gt; "" Then

MM_editConnection = MM_DREConn_STRING
MM_editTable = "DRE_Sales"
MM_editColumn = "Property_ID"
MM_recordId = "'" + Request.Form("MM_recordId" + "'"
MM_editRedirectUrl = "thanks_add.asp"
MM_fieldsStr = "PID|value|Title|value|editdate|value|editstatus|value|range|value|region|value|type|value|spfeature|value|multimap|value|short_desc|value|long_desc|value|bedrooms|value|mathroom|value|gbp|value|euros|value|streetname|value|balcony|value|sqft|value|pcode|value|garden|value|servicecharge|value|groundrent|value|estatename|value|tele|value|editemail|value|editfax|value|Imain|value|I2|value|I3|value|I4|value|I5|value|I6|value|I7|value|I8|value|I9|value|I10|value|P1|value|P2|value"
MM_columnsStr = "Property_ID|',none,''|Title|',none,''|Date|',none,NULL|Status|',none,''|Price_Range|',none,''|Region|',none,''|Type|',none,''|Features|',none,''|Map|',none,''|Short_Description|',none,''|Long_Description|',none,''|Bedrooms|none,none,NULL|Bathrooms|none,none,NULL|GBP|none,none,NULL|Euros|none,none,NULL|Street_Name|',none,''|Balcony|',none,''|SQ_FT|',none,''|Postcode|',none,''|Garden|',none,''|Service_Charge|',none,''|Ground_Rent|',none,''|Estate_Agent|',none,''|Telephone|',none,''|Email|',none,''|Fax|',none,''|Imain|',none,''|I2|',none,''|I3|',none,''|I4|',none,''|I5|',none,''|I6|',none,''|I7|',none,''|I8|',none,''|I9|',none,''|I10|',none,''|P1|',none,''|P2|',none,''"

' 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 &lt;&gt; "" And Request.QueryString &lt;&gt; "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString &lt;&gt; "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%&gt;
&lt;%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update") &lt;&gt; "" And CStr(Request("MM_recordId") &lt;&gt; "" 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 &lt;&gt; "" 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 &lt;&gt; 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
MM_editCmd.Execute (Here is the Line 111 Error)
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl &lt;&gt; "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%&gt;
&lt;%
Dim rsupdate_sales__MMColParam
rsupdate_sales__MMColParam = "1"
If (Request.QueryString("Property_ID" &lt;&gt; "" Then
rsupdate_sales__MMColParam = Request.QueryString("Property_ID"
End If
%&gt;
&lt;%
Dim rsupdate_sales
Dim rsupdate_sales_numRows

Set rsupdate_sales = Server.CreateObject("ADODB.Recordset"
rsupdate_sales.ActiveConnection = MM_DREConn_STRING
rsupdate_sales.Source = "SELECT * FROM DRE_Sales WHERE Property_ID = '" + Replace(rsupdate_sales__MMColParam, "'", "''" + "'"
rsupdate_sales.CursorType = 0
rsupdate_sales.CursorLocation = 2
rsupdate_sales.LockType = 1
rsupdate_sales.Open()

rsupdate_sales_numRows = 0
%&gt;

Babu