Webmaster Forum / ColdFusion / Getting Started / March 2007

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

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.

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&amp;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?

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#',

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 :

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]

is this what you mean?

 Error Executing Database Query.
Syntax error in INSERT INTO statement.
 
The error occurred in
F:\data\webdesigns\wwwroot\CFIDE\Allied\newadminaction.cfm: line 10

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

SQL       INSERT INTO admin_users_table1 (full_user_name, user_dept, user_id,
user_pw, user_pw_confirm, level) VALUES ( '', 'OPS', '', '', '', 'yes')
DATASOURCE      allieddatabase
VENDORERRORCODE      3092
SQLSTATE      &nbsp;
Resources:

    * Check the ColdFusion documentation to verify that you are using the
correct syntax.
    * Search the Knowledge Base to find a solution to your problem.

Browser       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.3)
Gecko/20070309 Firefox/2.0.0.3
Remote Address       10.0.0.201
Referrer       http://allied-srv-03/CFS/newadminuser.cfm
Date/Time       29-Mar-07 05:29 PM
Stack Trace
at
cfnewadminaction2ecfm2080680316.runPage(F:\data\webdesigns\wwwroot\CFIDE\Allied\
newadminaction.cfm:10) at
cfnewadminaction2ecfm2080680316.runPage(F:\data\webdesigns\wwwroot\CFIDE\Allied\
newadminaction.cfm:10)

com.inzoom.adojni.ComException: Syntax error in INSERT INTO statement. in
Microsoft JET Database Engine code=3092 Type=1
    at com.inzoom.ado.Command.jniExecute(Native Method)
    at com.inzoom.ado.Command.execute(Command.java:40)
    at com.inzoom.jdbcado.Statement.exec(Statement.java:34)
    at com.inzoom.jdbcado.Statement.execute(Statement.java:107)
    at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:212)
    at coldfusion.sql.Executive.executeQuery(Executive.java:753)
    at coldfusion.sql.Executive.executeQuery(Executive.java:675)
    at coldfusion.sql.Executive.executeQuery(Executive.java:636)
    at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236)
    at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:500)
    at
cfnewadminaction2ecfm2080680316.runPage(F:\data\webdesigns\wwwroot\CFIDE\Allied\
newadminaction.cfm:10)
    at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
    at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:349)
    at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
    at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:225)
    at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:51)
    at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
    at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27)
    at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:69)
    at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52)
    at
coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilt
er.java:28)
    at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
    at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
    at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
    at
coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:115)
    at coldfusion.CfmServlet.service(CfmServlet.java:107)
    at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:78)
    at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
    at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
    at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:257)
    at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:541)
    at
jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:204)
    at
jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:318)
    at
jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:426)
    at
jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:264)
    at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

ok, thou we had it then. only one typo! a # was missing, however still get the
following error message :-(

thanks for sticking with this by the way, its really appreciated.

 Error Executing Database Query.
Syntax error in INSERT INTO statement.
 
The error occurred in
F:\data\webdesigns\wwwroot\CFIDE\Allied\newadminaction.cfm: line 15

13 : <cfqueryparam value='#form.user_pw_confirm#' null='#NOT
14 : len(trim(form.user_pw_confirm))#'>,
15 : <cfqueryparam value='#form.user_level#' null='#NOT
len(trim(form.user_level))#'>)
16 : </cfquery>
17 :

SQL       INSERT INTO admin_users_table (full_user_name, user_dept, user_id,
user_pw, user_pw_confirm, level) VALUES ( (param 1) , (param 2) , (param 3) ,
(param 4) , (param 5) , (param 6) )
DATASOURCE      allieddatabase
VENDORERRORCODE      3092
SQLSTATE      &nbsp;
Resources:

    * Check the ColdFusion documentation to verify that you are using the
correct syntax.
    * Search the Knowledge Base to find a solution to your problem.

Browser       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.3)
Gecko/20070309 Firefox/2.0.0.3
Remote Address       10.0.0.201
Referrer       http://allied-srv-03/CFS/newadminuser.cfm
Date/Time       29-Mar-07 08:59 PM
Stack Trace
at
cfnewadminaction2ecfm2080680316.runPage(F:\data\webdesigns\wwwroot\CFIDE\Allied\
newadminaction.cfm:15) at
cfnewadminaction2ecfm2080680316.runPage(F:\data\webdesigns\wwwroot\CFIDE\Allied\
newadminaction.cfm:15)

com.inzoom.adojni.ComException: Syntax error in INSERT INTO statement. in
Microsoft JET Database Engine code=3092 Type=1
    at com.inzoom.ado.Command.jniExecute(Native Method)
    at com.inzoom.ado.Command.execute(Command.java:40)
    at com.inzoom.jdbcado.Statement.exec(Statement.java:34)
    at com.inzoom.jdbcado.PreparedStatement.execute(PreparedStatement.java:201)
    at
coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.j
ava:87)
    at coldfusion.sql.Executive.executeQuery(Executive.java:756)
    at coldfusion.sql.Executive.executeQuery(Executive.java:675)
    at coldfusion.sql.Executive.executeQuery(Executive.java:636)
    at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236)
    at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:500)
    at
cfnewadminaction2ecfm2080680316.runPage(F:\data\webdesigns\wwwroot\CFIDE\Allied\
newadminaction.cfm:15)
    at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
    at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:349)
    at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
    at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:225)
    at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:51)
    at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
    at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27)
    at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:69)
    at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52)
    at
coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilt
er.java:28)
    at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
    at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
    at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
    at
coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:115)
    at coldfusion.CfmServlet.service(CfmServlet.java:107)
    at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:78)
    at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
    at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
    at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:257)
    at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:541)
    at
jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:204)
    at
jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:318)
    at
jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:426)
    at
jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:264)
    at jrunx.scheduler.WorkerThread.run(WorkerThread.java:6

INSERT INTO admin_users_table1 (full_user_name, user_dept, user_id,
user_pw, user_pw_confirm, level) VALUES ( '', 'OPS', '', '', '', 'yes')
DATASOURCE allieddatabase

----

One thing I notice is that the VALUES do not seem to synch up with the INSERT
statement's field list.

In the above, the last item in the actual values is "YES" (user_pw_confirm?),
when the INSERT indicates the last item should be a numeric value (level) ...
this is why you are getting syntax errors ... because a numeric field cannot
have a string assigned to it.

I would hazard to say that the values being assigned are out of whack due to
the fact that some of the logic used in the filtering is throwing it off
somewhere

well, it may not actually be numerical, but that would be a good guess as to
the syntax error and generally people use numeric values for "Level"

if all fields in the DB are actually text, then the above should not be
throwing a syntax error for any reason since even the blanks are justified with
empty strings.

if the error was due to the DB field not allowing nulls, the error would state
that fact.

the only other thing I can think of , at least for you getting a "SYNTAX"
error is that you use simply "level" in the INSERT command ... "LEVEL" could be
a reserved word for your database ... try using "[level]" instead, or changing
that field name... or is it supposed to be "user_level"?

as for the CFDUMP ... it will always show things out of order to the form,
usually in alphabetical order ... that won't affect anything unless you are
using it to feed the INSERT, which you are not. As long as the INSERT and
VALUES sync, they can be in any order.

Also, can you verify please, that all fields in the DB are in fact CHAR fields?

If any fields are numeric or BIT fields and entries are out of sync, it could explain a few things.

I just re-reviewed your initial coding.

Can you do me a favor, since you have been making changes here and there throughout this, could you post up a copy of the current code.

Thx

ok here is the cf code from the action page:

<cfdump var="#form#"><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
(<cfqueryparam value='#form.full_user_name#' null='#NOT
len(trim(form.full_user_name))#'>,
<cfqueryparam value='#form.user_dept#' null='#NOT
len(trim(form.user_dept))#'>,
<cfqueryparam value='#form.user_id#' null='#NOT
len(trim(form.user_id))#'>,
<cfqueryparam value='#form.user_pw#' null='#NOT
len(trim(form.user_pw))#'>,
<cfqueryparam value='#form.user_pw_confirm#' null='#NOT
len(trim(form.user_pw_confirm))#'>,
<cfqueryparam value='#form.user_level#' null='#NOT
len(trim(form.user_level))#'>)
</cfquery>

and here is the html code from the form page: (if needed)

  <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&amp;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="user_level">Level</label>
  <select name="user_level" id="app_select">
    <option>yes</option>
    <option>no</option>
  </select>
  <br>
  <input name="Submit" type="submit" id="submit_button" value="Submit">
</form>

change the code in the INSERT from "level" to "[level]"

and then re-try submitting the form and see what happens.

no problem ... we've all been there at one time or another :)