Error Executing Database Query.

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.
LVL 5
IsaacSharePoint Client Side DeveloperAsked:
Who is Participating?
 
mrichmonConnect With a Mentor Commented:
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
 
Tacobell777Commented:
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
 
hartCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Tacobell777Commented:
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
 
PluckaCommented:
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
 
Tacobell777Commented:
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
 
Tacobell777Commented:
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
 
hartCommented:
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
 
PluckaCommented:
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
 
Tacobell777Commented:
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
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
I thought <CFQUERYPARAM> was just for query strings.  And that it was to prevent malicious users from tampering with your query string.
0
 
James RodgersWeb Applications DeveloperCommented:
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
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
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
 
mrichmonCommented:
Yep.
0
 
shooksmCommented:
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
 
Tacobell777Commented:
Thats correct your date would be ##SESSION.HLS.TO_DATE## or use #createODBCDate(SESSION.HLS.TO_DATE)# or #createODBCDateTime(SESSION.HLS.TO_DATE)#
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.