• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' - [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Hi guys.
I have a new problem. I'm still working on developing a webdatabase. I develop codes in Dreamweaver CS3 and the database is Access placed on a webhotel.
The problem is on an updateform. I made a lot of similar update-forms and they all work perfect... beside this one. When I click the button to update, I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/crt_survey/crt_survey_edit_note.asp, line 37

Line 37 is the second last line in the code below (MM_editCmd.Execute) I have no idea what is wrong. I used these codes on around 20 other update-pages with no errors. On top of this, this form is the most simple one. Only 1 single field to update! (field = "note" from table="tbl_note").
This issue is driving me nuts! Can you please help me? :o)
Friendly regards
Ullenulle
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="IsLoggedIn.asp"-->
<!--#include virtual="/Connections/crt_conn.asp" -->
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If
 
' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' IIf implementation
Function MM_IIf(condition, ifTrue, ifFalse)
  If condition = "" Then
    MM_IIf = ifFalse
  Else
    MM_IIf = ifTrue
  End If
End Function
%>
<%
If (CStr(Request("MM_update")) = "form1") Then
  If (Not MM_abortEdit) Then
    ' execute the update
    Dim MM_editCmd
 
    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_crt_conn_STRING
    MM_editCmd.CommandText = "UPDATE tbl_note SET note = ? WHERE note_id = ?" 
    MM_editCmd.Prepared = true
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 255, Request.Form("note")) ' adVarWChar
	MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

Open in new window

0
ullenulle
Asked:
ullenulle
  • 3
  • 3
1 Solution
 
milduraitCommented:
A couple of ideas.

a) Single quotes around the note column
MM_editCmd.CommandText = "UPDATE tbl_note SET note = '?' WHERE note_id = ?"

b) I would test the values in the forms and check for anomalies such as single quotes etc
Response.write("|" & Request.Form("note") & "|")
Response.write("<br/>|" & Request.Form("MM_recordId") & "|")

0
 
ullenulleAuthor Commented:
Hi Mildurait.

Ad a) I tried single quotes, but that generated this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/crt_survey/crt_survey_edit_note.asp, line 39

Ad b) The tests were correct. "MM_recordId" were 15 (correct) and "note" did show, what I wrote in the form... but somehow it won't write the stuff to the database.

Any other suggestions?
0
 
daveamourCommented:
What database are you using?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ullenulleAuthor Commented:
Hi. As I wrote I'm using Microsoft Access, and everything else is working fine... just this single, stupid form won't update. :-(  All other forms are updating perfectly.
Friendly regards
Ullenulle
0
 
milduraitCommented:
Have you tried simplifying the command text, even response.writing sql below, copying and running within access?

Dim sql as string
sql = "UPDATE tbl_note SET note = '" & Request.Form("note")  & "' WHERE note_id = " & Request.Form("MM_recordId")

Has this form ever worked?
Is note of string (text/memo) type (not numeric) etc?

0
 
milduraitCommented:
...must fly for 24 hours..
Sabbatarian.
Hope you can find a solution.
0
 
ullenulleAuthor Commented:
Hi again. No... it didn't work, so I made another solution that works. I guess this is just "one of those things" that happens. :o) Thanks for your suggestions!
Friendly regards
Ullenulle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now