Solved

Error Executing Database Query.

Posted on 2004-04-15
16
179 Views
Last Modified: 2013-12-24
When I try to submit the form, I get the below.  What I'm I doing wrong?

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '90 days'

Apart of the code is below:

<cfquery datasource="#datasource#">
INSERT INTO SPECIALPROJ_REGISTRATION(FIRST_NAME,LAST_NAME,SPON_DISTRICT,CMO_CITY,CMO_STATE,VOL_WORK_NUM,VOL_HOME_NUM,
SUP_NAME,SUP_WORK_NUM,TOURS_DUTY) VALUES (#SESSION.HLS.FIRST_NAME#,#SESSION.HLS.LAST_NAME#,
#SESSION.HLS.SPON_DISTRICT#,#SESSION.HLS.CMO_CITY#,#SESSION.HLS.CMO_STATE#,#SESSION.HLS.VOL_WORK_NUM#,#SESSION.HLS.VOL_HOME_NUM#,
#SESSION.HLS.SUP_NAME#,#SESSION.HLS.SUP_WORK_NUM#,#SESSION.HLS.TOURS_DUTY#)
</cfquery>

Thank you.
0
Comment
Question by:Isaac
  • 6
  • 2
  • 2
  • +4
16 Comments
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
You'll need single quotes around any value that is not an integer, i.e.

<cfquery datasource="#datasource#">
INSERT INTO SPECIALPROJ_REGISTRATION(FIRST_NAME,LAST_NAME,SPON_DISTRICT,CMO_CITY,CMO_STATE,VOL_WORK_NUM,VOL_HOME_NUM,
SUP_NAME,SUP_WORK_NUM,TOURS_DUTY) VALUES ('#SESSION.HLS.FIRST_NAME#','#SESSION.HLS.LAST_NAME#',
'#SESSION.HLS.SPON_DISTRICT#','#SESSION.HLS.CMO_CITY#','#SESSION.HLS.CMO_STATE#','#SESSION.HLS.VOL_WORK_NUM#','#SESSION.HLS.VOL_HOME_NUM#',
'#SESSION.HLS.SUP_NAME#','#SESSION.HLS.SUP_WORK_NUM#','#SESSION.HLS.TOURS_DUTY#')
</cfquery>
0
 
LVL 11

Expert Comment

by:hart
Comment Utility
the best coding practice is to use cfquery param

if the database field is
<CFQUERYPARAM CFSQLTYPE="CF_SQL_NUMERIC" VALUE=""> ---- numeric datatype
<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE=""> ---- text/varchar2
<CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE=""> ---- date
<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE=""> --- date and time
<CFQUERYPARAM CFSQLTYPE="CF_SQL_BIT" VALUE=""> --- bit [0/1]

and ur code could be

<cfquery datasource="#datasource#">
INSERT INTO SPECIALPROJ_REGISTRATION(FIRST_NAME,
                            LAST_NAME,
                            SPON_DISTRICT,
                            CMO_CITY,
                            CMO_STATE,
                            VOL_WORK_NUM,
                            VOL_HOME_NUM,
                            SUP_NAME,
                            SUP_WORK_NUM,
                            TOURS_DUTY)
                       VALUES (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.FIRST_NAME#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.LAST_NAME#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.SPON_DISTRICT#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.CMO_CITY#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.CMO_STATE#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_NUMERIC" VALUE="#SESSION.HLS.VOL_WORK_NUM#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_NUMERIC" VALUE="#SESSION.HLS.VOL_HOME_NUM#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.SUP_NAME#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_NUMERIC" VALUE="#SESSION.HLS.SUP_WORK_NUM#">,
                           <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#SESSION.HLS.TOURS_DUTY#">)
</cfquery>


PS: Just remeber to identify which field is numeric and which one is text and put the queryparam accordingly.

Regards
Hart
0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
That is certainly true, it is best practise to use cfqueryparam, but then again, it is even better practise to have your queries as stored procedures...
0
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
I don't agree with the stored procedure theory.

It makes your database SQL specific, ie a stored procedure in one database is different to another.
0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
yup, thats another story, one that I discussed many times - people do not move from db platform just like that when you pay 10.000's
of dollars for it.
0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
It's like saying - oh lets spend 10 grant on MS SQL but let's not use its specific features incase maybe one day we'll change to Oracle and pay another 10.000
0
 
LVL 11

Expert Comment

by:hart
Comment Utility
If there are multiple queries on the page then a stored procedure would be a good idea,
but i was just refering w.r.t insert statement that TheInnovator had posted..

rest is upto the TheInnovator :-)

Regards
Hart



0
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
Yes,

But we develop standard applications that cost 100k+ and sell them lots of times, so we don't know what platform the customer will wish to deploy on. Your argument only works if you only ever write an app for one customer and never resell or reuse any of the bits for other customers. Perhaps for internal development this works, but not for a software development house.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
Not wanting to make a big discussion out of this but... ;-)
I know what you mean Plucka, I am currently working with Shado, it works on many db platforms, it does not make use of indexes,
relationships or correct datatypes and I tell you its slow as hell..... Fine it works on many platforms but look at what you are sacrificing
I reckon if you want to make an app for many platforms then you build the specific features for each platform on top and not in the app,
but still take advantage of them, not using them is just laziness.

And for 100K I certainly would want all the feautures, hell I know if I sold an app for 100k I would have the time to write platform specifc
features.

My 2cents ;-)
0
 
LVL 5

Author Comment

by:Isaac
Comment Utility
I thought <CFQUERYPARAM> was just for query strings.  And that it was to prevent malicious users from tampering with your query string.
0
 
LVL 25

Expert Comment

by:James Rodgers
Comment Utility
from the help files

The CFQUERYPARAM is designed to do the following things:

Allows the use of SQL bind parameters.
Allows long text fields to be updated from an SQL statement.
Improves performance.
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 400 total points
Comment Utility
But stored procedures are not an option for an access database which this one is.

cfqueryparam is a good idea, but also one thing to check besides single quotes
around non-integer values is to have # around date values as access uses the # to
know something is a date
0
 
LVL 5

Author Comment

by:Isaac
Comment Utility
So the date would be

<cfquery datasource="#datasource#">
INSERT INTO REGISTRATION(FIRST_NAME,LAST_NAME,SPON_DISTRICT,CMO_CITY,TO_DATE) VALUES ('#SESSION.HLS.FIRST_NAME#','#SESSION.HLS.LAST_NAME#',
'#SESSION.HLS.SPON_DISTRICT#','#SESSION.HLS.CMO_CITY#','#SESSION.HLS.CMO_STATE#',##SESSION.HLS.TO_DATE##)
</cfquery>
0
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
Yep.
0
 
LVL 9

Expert Comment

by:shooksm
Comment Utility
Actually, procedures are available to Access (I believe since Office 2000):

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp?frame=true#acadvsql_procs
0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
Thats correct your date would be ##SESSION.HLS.TO_DATE## or use #createODBCDate(SESSION.HLS.TO_DATE)# or #createODBCDateTime(SESSION.HLS.TO_DATE)#
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now