CFUPDATE Query Error Help

bfinelsen · bfinelsen

Ok, I am at a serious loss and am getting a headache trying to figure out
what exactly is wrong with this simple code. I have a form that pulls
information from a database and allows users to update the information. The
code for that page is:

<CFQUERY NAME="editmessage" DATASOURCE="subscriptions">
SELECT *
FROM tbl_Messages
WHERE messageID = #URL.messageID#
</CFQUERY>

<head>
<title><CFOUTPUT>#editmessage.type# Editor</CFOUTPUT></title>

<style type="text/css">

</style>
<body bgcolor="FFFFFF" text="000000">
<CFOUTPUT query="editmessage">
<form name="form" method="post" action="updatemessage.cfm">
<table width="900" border="0" align="center" cellpadding="5" cellspacing="0">
<tr>
<td> </td>
<td>[<a href="menu.cfm">Back to menu</a>] [<a
href="logout.cfm">Logout</a>] </td>
</tr>
<tr>
<td width="25%"><div align="right" class="style40"><span
class="style39">Type:</span></div></td>
<td width="75%">
<input name="Type" type="text" value="#Type#" size="50">
<span class="style38"></span></td>
</tr>
<tr>
<td width="25%"><div align="right" class="style40"><span
class="style39">From:</span></div></td>
<td width="75%"><span class="style38">
<input name="From" type="text" value="#From#" size="50">
</span></td>
</tr>
<tr>
<td width="25%"><div align="right" class="style40"><span
class="style39">Subject:</span></div></td>
<td width="75%"><span class="style38">
<input name="Subject" type="text" value="#Subject#" size="50">
</span></td>
</tr>
<tr>
<td width="25%" valign="top"><div align="right" class="style40"><span
class="style39">Body:</span></div></td>
<td width="75%"><span class="style38">
<textarea name="Body" cols="90" rows="10">#Body#</textarea>
</span></td>
</tr>
<tr>
<td colspan="2"><div align="center">
<input type="submit" value="Update">
</div></td>
</tr>
</table>
</form>
</CFOUTPUT>
</body>

Then on the page it posts to, I have the simple code of:

<CFUPDATE datasource="subscriptions" tablename="tbl_Messages">

However, I keep getting an error stating there is a syntax error in my
update query code and I'm clueless as to why. I have tried several different
things, but to no help. Please let me know what I am doing wrong. Thank you in
advance.

bfinelsen · bfinelsen

As a side note, I have also used the following code for the update with no
luck:

<CFQUERY NAME="UpdateMessage" DATASOURCE="subscriptions">
UPDATE tbl_Messgae
SET Type = '#Type#',
From = '#From#',
Subject = '#Subject#',
Body = '#Body#'
WHERE messageID = #messageID#
</CFQUERY>

paross1 · paross1

I don't know if it was just a typo in your post or not, but you misspelled tbl_Messages as tbl_Messgae in your UPDATE.

Phil

bfinelsen · bfinelsen

Phil,

Yes, I did notice that typo and I did correct that on my code as well, but still no luck :(

paross1 · paross1

What is the actual error that you are getting with your UPDATE (cfquery)
option? (I never use CFUPDATE because it has been notoriously problematic for
lots of folks.) The ususal suspects are datat type mismatches, such as
messageID actually being a character type and no quotes around #messageID#, etc.

Phil

bfinelsen · bfinelsen

I am actually using this code now, as I have seen other issues in the forums
with CFUPDATE:

<CFQUERY NAME="UpdateMessage" DATASOURCE="subscriptions">
UPDATE tbl_Messages
SET From = '#Form.mesFrom#',
Subject = '#Form.mesSubject#',
Body = '#Form.mesBody#'
WHERE Type = '#Form.mesType#'
</CFQUERY>

However, it is still returning an error and all it states in the CF log
files is:

Error Executing Database Query.[Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE
statement. The specific sequence of files included or processed is:
C:\websites\docs\subscriptions\updatemessage.cfm

I tried turning debugging on to specify exactly what is wrong, but I'm not
getting anything :(.....I did rename the field names becuase I was unsure if
they were creating the problem, but again...no help

bfinelsen · bfinelsen

Also...to help the field name types in access are as follows:

messageID = AutoNumber
Type = Text
From = Text
Subject = Text
Body = Memo

paross1 · paross1

You have a field name in your table that I'm sure is causing your problem, as
From is a reserved word. Try using brackets [ ] aroudn the offending word, and
any such others in the future.

<CFQUERY NAME="UpdateMessage" DATASOURCE="subscriptions">
UPDATE tbl_Messages
SET [From] = '#Form.mesFrom#',
Subject = '#Form.mesSubject#',
Body = '#Form.mesBody#'
WHERE Type = '#Form.mesType#'
</CFQUERY>

Phil

bfinelsen · bfinelsen

Phil,

I did rename the columns and changed the code, but didn't catch all of the
code originally, so when I went back and corrected that, it works. Ultimately,
I believe you are correct, that From was creating the problem, so I appreciate
your help :)

Ben

bfinelsen · bfinelsen

BTW, is there a place that lists all the current reserved words so that I can avoid this type of issue in the future?

paross1 · paross1

Quick Google........

[url]http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q286335[/url]
[url]http://www.databasedev.co.uk/ms-access-reserved-words.html[/url]
[url]http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18050[/url]

Phil

bfinelsen · bfinelsen

Awesome, again thank you so much for your help. You are a valuable asset to these forums :)

CFUPDATE Query Error Help

Thread Tools

Display