Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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.
0
Isaac
Asked:
Isaac
  • 6
  • 2
  • 2
  • +4
1 Solution
 
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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
mrichmonCommented:
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now