Inspiring

Answered

cf/dw error

Forum|Forum|19 years ago
March 24, 2007
20 replies
2397 views

This is really holding me up now. i posted the other for some help with some simple insert cf code. i still dont have this working and ive tried everythng there is to try. ive got a feeling that there is something wrong either with dw or cf server.

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

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

At line 576 of file "C:\program file\macromedia\dreamweaver 8\configuration\servermodels\coldfusion\dwscriptsserverimpl.js
typeerror: sqlVarRef has no properties"

i get this error message when i open up my new insert_new_admin_user.cfm page

so i have tried:

1, re writing the code several times
2, creating new table in the access db
3, repaired/install of dw
4, upgraded from cf server 6 to cf server 7
5, searched for help online

the strange thing is that other insert code works fine on pages that i created ages ago. its only the new ones that dont work. ive even tried coping the code from old pages and just changing the bits i need to but still doesnt work.

please see the other post for further details.
http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=2&threadid=1252879&enterthread=y

really need some help here. i wanted to have my site finised this weekend ...... but looks like it aint gunna happen :-(

edit>>> ive just tried something else: tried doing away with the sql altogether but i still get the same error which is

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

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

1 : <cfinsert datasource="allieddatabase" tablename="admin_users_table1">

the cf code im using for this one was:

<cfinsert datasource="allieddatabase" tablename="admin_users_table1">

zac

Getting started

This topic has been closed for replies.

Correct answer SafariTECH

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

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

S

SafariTECH

Inspiring

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

S

SafariTECHCorrect answer

Inspiring

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

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

Z

zac1234Author

Inspiring

do you know what? i've had one of the worst days of my working life today..................... but you have just cheered me up no end!!!!

i changed level to user_level - i also noticed that since re-writing the code i had called the table user_admin_table instead of user_admin_table1 doh!

i have now sucsesfully inserted a new user! :-))))) thanks so much to you both for being so patient with me. hopefully one day i'll be able to return the favour to someone else on here.

thanks, take care.

zac

Z

zac1234Author

Inspiring

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)

><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="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>

S

SafariTECH

Inspiring

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.

I think I may see something.

Thx

S

SafariTECH

Inspiring

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

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

Z

zac1234Author

Inspiring

yes i can confimr that in the access db these are set to text (which i believe is char)

S

SafariTECH

Inspiring

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.

S

SafariTECH

Inspiring

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

Syntax errors are usually the result of type mismatches or missing punctuation more times than not.

Is there a way you can make a submission so all fields are filled in so we see what syncs up with every INSERT setting? The blank strings are not letting us see if anything else is out if whack to what it should be.

Z

zac1234Author

Inspiring

quote:

Originally posted by: SafariTECH
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

Syntax errors are usually the result of type mismatches or missing punctuation more times than not.

interesting, when i use <cfdump var="#form#"> infront of the insert and read the whats being inputed they are in the worng order:

full_user_name,submit,user_dept,user_id,user_level,user_pw,user_pw_confirm <<<< this is not the order in which they are in on the form or the insert sql code. why is this?

also where does it say the user_level is numerical? as i have set this to text for the time being so as not to add to the confusion.

Z

zac1234Author

Inspiring

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
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.java: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(ClientScopePersistenceFilter.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

N

Newsgroup_User

Inspiring

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

Yup, this is the important part because it shows the SQL as it is
actually sent to the database. I suspect the empty strings maybe
causing the problem. There are a couple of ways one could handle this,
my prefered method is the <cfqueryparam...> tag.

<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.level# null='#NOT len(trim(form.level))#'>)
</cfquery>

No guarantees on the typing and that there are not syntax errors.

What this does is if there is no value in the form field, then a null
value is sent to the database instead of an empty string.

Z

zac1234Author

Inspiring

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
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(ClientScopePersistenceFilter.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)

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded