Skip to main content
D
dieg02000
Participant
December 31, 2008
Answered

Updating a table via form

  • December 31, 2008
  • 3 replies
  • 555 views
Hello -

I'm trying to do what (should) be an easy task, but i'm pulling out what's left of my hair. I can't seem to update an Access table.

tble_message has the following fields:
messagesID : autonumber/key
to [data type number]
from [data type text]
Date [data type Date/Time]
message [data type text]
time [data type Date/Time]


I created the following form:
- - - begin form.cfm - - -

<FORM ACTION="input2.cfm?newcall2=YES&toID=#URLEncodedFormat(toID)#&budName=#URLEncodedFormat(budname)#" Method=Post>

<INPUT TYPE="hidden" NAME="to" VALUE=<cfoutput>#toID#</cfoutput>>
<INPUT TYPE="HIDDEN" NAME="from" value=<cfoutput>"#LoginName#"</cfoutput>>
<INPUT TYPE="hidden" NAME="DATASOURCE" VALUE="testdb">
<INPUT TYPE="hidden" NAME="TABLENAME" VALUE="tbl_Messages">

Message to: <cfoutput>#budName#</cfoutput>

<TEXTAREA NAME="YMESSAGE" ROWS="5" COLS="40" WRAP="Virtual"></TEXTAREA>

<INPUT TYPE="Submit" VALUE="send message">
<INPUT Type="reset" Value="start again">
</form>
- - - end form.cfm - - -




this form feeds input2.cfm



- - - begin input2.cfm - - -
<cfoutput>
#message#
#toid#
#loginName#
</cfoutput>

<cfquery name="addmess" DATASOURCE="testdb">
Insert Into tbl_Messages
(message,to,from)

Values
('<cfoutput>#message#</cfoutput>','<cfoutput>#toid#</cfoutput>','<cfoutput>#loginName#</cfoutput>'
)
</CFQUERY>
- - - end input2.cfm - - -

i do see the expected values in my cfoutput:

<cfoutput>
#message#
#toid#
#loginName#
</cfoutput>


= = = error message = = =
I am getting an Error Executing Database Query
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft]
[ODBC Microsoft Access Driver] Syntax error in Insert Into statement

the error occurred in c:\.... input2.cfm: line 21

20 Values
21 ('<cfoutput>#message#</cfoutput>','<cfoutput>#toid#</cfoutput>','<cfoutput>#loginName#</cfoutput>')
22 </CFQUERY>

= = = end error message = = =

Form Fields:
fieldnames:to,from,datasource,tablename,ymessage
from=chancho2000
tablename=tbl_messages
to=100
ymessage=testing the input form

url parameters:
newcall2=yes
toID=



.. i just can't seem to populate the database

any help or direction would be appreciated.. thanks!




dieg0
    This topic has been closed for replies.
    Correct answer Fetch
    Per the MS Article shown here

    http://support.microsoft.com/kb/286335

    FROM is a reserved word, though TO is not, so that's the actual problem with the INSERT statement. Note however that all of the suggestions above regarding syntax, cfqueryparam, et al. are spot on and should be followed.

    3 replies

    F
    FetchCorrect answer
    Participating Frequently
    December 31, 2008
    Per the MS Article shown here

    http://support.microsoft.com/kb/286335

    FROM is a reserved word, though TO is not, so that's the actual problem with the INSERT statement. Note however that all of the suggestions above regarding syntax, cfqueryparam, et al. are spot on and should be followed.
    -
    -__cfSearching__-
    Inspiring
    December 31, 2008
    Kapitaine wrote:
    > Prefix your column names with something more descriptive like
    > "messages_" so a column becomes "messages_columnName".
    > This will avoid a lot of issues with reserved words and will also
    > make your code easier to read.

    I agree about using descriptive column names. Though personally I dislike the redundancy created by repeating the table name in _every_ column. It usually possible to avoid keywords without going to that extreme. But that is just my personal preference ;-) Looking at their table name, it is probably more in line with their current schema.
    -
    -__cfSearching__-
    Inspiring
    December 31, 2008
    dieg02000 wrote:
    > .. i just can't seem to populate the database

    What is happening? Are you receiving an error, if so what is the full error message?

    > <cfquery name="addmess" DATASOURCE="ritmo2000">
    > Insert Into tbl_Messages
    > (message,to,from)
    > Values
    > ('<cfoutput>#message#</cfoutput>','<cfoutput>#toid#</cfoutput>','<cfoutput>#loginName#</cfoutput>'
    > )

    I do not use Access but FROM is almost certainly a reserved keyword and should not be used as a column name. Try renaming it. I do not know about the other column names like "To".

    Though it may not cause an error the <cfoutput> tags are not needed and should be removed. The #variables# should be evaluated automatically. Also consider scoping your variables to avoid conflicts and using cfqueryparam. Something like

    <cfquery name="addmess" DATASOURCE="ritmo2000">
    Insert Into tbl_Messages (message, to, TheRenamedFromColumn)
    Values
    (
    <cfqueryparam value="#form.message#" cfsqltype="cf_sql_varchar">,
    <cfqueryparam value="#toid#" cfsqltype="cf_sql_integer">,
    <cfqueryparam value="#loginName#" cfsqltype="cf_sql_longvarchar">
    )

    -
    -__cfSearching__-
    Inspiring
    December 31, 2008
    > <cfqueryparam value="#form.message#" cfsqltype="cf_sql_varchar">,
    > <cfqueryparam value="#form.toid#" cfsqltype="cf_sql_integer">,
    > <cfqueryparam value="#form.loginName#" cfsqltype="cf_sql_longvarchar">

    Correction, the data types are swapped. It is probably more like

    (
    <cfqueryparam value="#form.message#" cfsqltype="cf_sql_longvarchar">,
    <cfqueryparam value="#toid#" cfsqltype="cf_sql_integer">,
    <cfqueryparam value="#loginName#" cfsqltype="cf_sql_varchar">
    )

    A
    Anonymous
    December 31, 2008
    Prefix your column names with something more descriptive like "messages_" so a column becomes "messages_columnName". This will avoid a lot of issues with reserved words and will also make your code easier to read.

    Also, use CFQUERYPARAM when inserting your variables. Please please pleeeease do this.

    Scope your variables too. I can't stress the importance of this. e.g. if a variable is from a form it should be "#form.myVar#"...and NOT simply "#myVar". Same goes for URL, e.g. "#url.myVar#" and also query results like so "#myQueryName.myVar#".

    Good luck.

    Mikey.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices