Solved

Error Executing Database Query.

Posted on 2004-04-15
16
184 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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 400 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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