?
Solved

Error Executing Database Query.

Posted on 2004-04-15
16
Medium Priority
?
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +4
16 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 10838350
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
ID: 10839479
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
ID: 10840151
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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
LVL 18

Expert Comment

by:Plucka
ID: 10840258
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
ID: 10840404
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
ID: 10840409
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
ID: 10840424
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
ID: 10840480
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
 
LVL 17

Expert Comment

by:Tacobell777
ID: 10840773
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
ID: 10841686
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
ID: 10841968
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 1600 total points
ID: 10843075
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
ID: 10843993
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
ID: 10844359
Yep.
0
 
LVL 9

Expert Comment

by:shooksm
ID: 10844365
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
ID: 10845970
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

752 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