cf/dw error

Error Executing Database Query.
Syntax error in INSERT INTO statement.

Ok so now we need to look at your insert query. Do you see in the debug
output. If you have full debugging turned on, it should be showing the
processed SQL statement. Can you post that.

no the error was

Error Executing Database Query.
Syntax error in INSERT INTO statement.


this is the result from the cf dump - interesting?

FIELDNAMES FULL_USER_NAME,USER_DEPT,USER_ID,USER_PW,USER_PW_CONFIRM,USER_LEVEL,SUBMIT
FULL_USER_NAME [empty string]
SUBMIT Submit
USER_DEPT OPS
USER_ID [empty string]
USER_LEVEL yes
USER_PW [empty string]
USER_PW_CONFIRM [empty string]

What was the error? Undefined value?

If so, was an option selected on the form. IIRC if no option is
selected then the variable does not exist on the form structure passed
to the action page.

Adding this to the top of your action page can help see what is being
passed and what is not.

<cfdump var="#form#">

The error occurred in F:\data\webdesigns\wwwroot\CFIDE\Allied\newadminaction.cfm: line 9

7 : '#form.user_pw#',
8 : '#form.user_pw_confirm#',
9 : '#form.level#' )
10 : </cfquery>
11 :

Your error message is telling the entire story. You did not change
'new_admin_use' to 'form' on line four.

4 : '#new_admin_user.full_user_name#',
SHOULD BE
4 : '#form.full_user_name#',

zac1234 wrote:
> yep thought you might say that, i meant to edit my post to say i had already
> tried that.
>
> this is the error message i get from that:
>
> Element FULL_USER_NAME is undefined in NEW_ADMIN_USER.
>
> The error occurred in
> F:\data\webdesigns\wwwroot\CFIDE\Allied\newadminaction.cfm: line 4
>
> 2 : INSERT INTO admin_users_table (full_user_name, user_dept, user_id,
> user_pw, user_pw_confirm, level)
> 3 : VALUES (
> 4 : '#new_admin_user.full_user_name#',
> 5 : '#form.user_dept#',
> 6 : '#form.user_id#',
>
>

yep thought you might say that, i meant to edit my post to say i had already tried that.

this is the error message i get from that:

Error Executing Database Query.
Syntax error in INSERT INTO statement.

The error occurred in F:\data\webdesigns\wwwroot\CFIDE\Allied\newadminaction.cfm: line 9

7 : '#form.user_pw#',
8 : '#form.user_pw_confirm#',
9 : '#form.level#' )
10 : </cfquery>
11 :


EDIT <<< >>>>just discovered that "level" is a reserved word in access, so changed it to user_level but still didnt work

so what s right with or without the # ?

Yup, I can see your problem now.

With the hash/pound/# is required, but you are naming your variables wrong.

On your form page, the form is named "new_admin_user" and this can be
used for javascript purposes at that time. But once the form is
submitted to that action page, the data structure is named "form".

Your insert will need to look something like this:
<cfquery name="add_new_admin" datasource="allieddatabase">
INSERT INTO admin_users_table
(full_user_name, user_dept, user_id, user_pw, user_pw_confirm, level)
VALUES
('#form.full_user_name#',
'#form.user_dept#',
'#form.user_id#',
'#form.user_pw#',
'#form.user_pw_confirm#',
'#form.level#' )
</cfquery>

Of course an even better practice would be to wrap each of those
variables in a proper <cfqueryParam ...> tag. Something like:
VALUES
(<cfqueryParam value="#form.full_user_name#"
cfSqlType="cf_sql_varchar">, ... )

ok, thanks for getting back.

this is what the code looks like if i do it myself - i know what you are going to say first of all that the VALUES need to have # blabla# around them. if i wrap the VALUES in # then all i get is
"new_admin_user.full_user_name is undefined in form"

if i take the # away the message i get is:

rror Executing Database Query.
Syntax error in INSERT INTO statement.

The error occurred in F:\data\webdesigns\wwwroot\CFIDE\Allied\newadminaction.cfm: line 1

1 : <cfquery name="add_new_admin" datasource="allieddatabase">
2 : INSERT INTO admin_users_table (full_user_name, user_dept, user_id, user_pw, user_pw_confirm, level)
3 : VALUES ('new_admin_user.full_user_name', 'new_admin_user.user_dept', 'new_admin_user.user_id', 'new_admin_user.user_pw', 'new_admin_user.user_pw_confirm', 'new_admin_user.level' )

so what s right with or without the # ?

here is my html code:

<form method="POST" name="new_admin_user" action="newadminaction.cfm">
<label for="full_user_name">Name</label>
<input type="text" name="full_user_name" id="full_user_name">
<label for="user_dept">Department</label>
<select name="user_dept" id="app_select">
<option selected>OPS</option>
<option>Sales</option>
<option>Accounts</option>
<option>P&W</option>
<option>General management</option>
</select>
<label for="user_id">user name</label>
<input type="text" name="user_id" id="use_id">
<label for="user_pw">Password</label>
<input type="text" name="user_pw" id="user_pw">
<label for="user_pw_comfirm">Confirm Password</label>
<input type="text" name="user_pw_confirm" id="user_pw_confirm">
<label for="level">Level</label>
<select name="level" id="app_select">
<option>yes</option>
<option>no</option>
</select>
<br>
<input name="Submit" type="submit" id="submit_button" value="Submit">
</form>

its just supposed to be a simple insert into the db of the form contents. when i click on submit it should take me to the newadminuseraction.cfm page but instead it i get the error messages.

is this the info you needed?

currently i am getting the following error message from within dw:

"while executing insepctserverbehanior in InsertRecord.htm, the
following javascript error(s) occured:


Two thoughts:

This is a DW error and may not be impacting your actual functionality.
DW itself uses quite a bit of JavaScript in it's wizards and such. Is
your code not working when run on the server? If it does work you can
ignore the DW error as annoying as it is.

There is a caching bug in DW itself. If you have the "Maintain
synchronization information" option selected on the "Remote Info"
section of the Site Definition it can cause problems. It prevents the
file date-time stamp from changing when a file is uploaded so that CF
does not realize a new version of the file is available and that it
needs to recompile it. You can either turn off this option, or download
and install the DW patch the fixes this bug.

if you have changed the code so there is no SQL and still get this error, it sounds like CF is caching the page and not serving up the new page ... either that or it is not actually transferring the page to the server.

check in the Admin section and make sure it is not caching pages and or clear the cache