We help IT Professionals succeed at work.

SQL Insert Error

elhome
elhome asked
on
183 Views
Last Modified: 2013-12-24
I have the following code to insert form data into an Access database:

<cfquery name="AddEvent" datasource="SW01">
   INSERT INTO Events (Title,
   EventDescription,
   ExtDescription,
   HostSite, EventDate,
   EventTime,
   Address,
   ContactName,
   ContactPhone,
   ContactEmail,
   EventWebSite,
   Thumbnail,
   Image)
   
   VALUES ('#Form.Title#',
      '#Form.EventDescription#',
      '#Form.ExtDescription#,       
      '#Form.HostSite#',
      '#left(Form.EventDate,19)#',
        '#Form.EventTime#',
        '#Form.Address#',
        '#Form.ContactName#',
        '#Form.ContactPhone#',
        '#Form.ContactEmail#',
        '#Form.EventWebSite#',
        '#Form.Thumbnail#',
        '#Form.Image#')
</cfquery>

I get the following error message:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Extended description., 'Host Site', '3/14/2006', 'Time', 'Address City', 'Contact Name', 'Contact Phone', 'Contact Email', 'Web Site', 'Thumbnail', 'Image')'.  
 
The error occurred in D:\Inetpub\gamingmarketingsolutions\dev\cpcc\events_add_insert.cfm: line 23
 
21 :         '#Form.EventWebSite#',
22 :         '#Form.Thumbnail#',
23 :         '#Form.Image#')
24 : </cfquery>
25 :

 

--------------------------------------------------------------------------------
 
SQL    INSERT INTO Events (Title, EventDescription, ExtDescription, HostSite, EventDate, EventTime, Address, ContactName, ContactPhone, ContactEmail, EventWebSite, Thumbnail, Image) VALUES ('Title', 'Description', 'Extended description., 'Host Site', '3/14/2006', 'Time', 'Address City', 'Contact Name', 'Contact Phone', 'Contact Email', 'Web Site', 'Thumbnail', 'Image')  
DATASOURCE   SW01
VENDORERRORCODE   -3100
SQLSTATE   42000
 

 
I'm stumped as to what the missing operator could be.  
Comment
Watch Question

Commented:
are eventdate and eventtime in the database date/time data?
Two big thoughts (and the first one might fix the problem).
1) The value for EventTime being passed in is the literal 'Time'. This seems strange, Shouldn't it be some sort of literal that looks like a time?

2) You might try using the CreateODBCDate and CreateODBCTime functions.  I know the below won't work with Time as the literal, but if that is fixed.. try something like replacing the lines:
      '#left(Form.EventDate,19)#',
       '#Form.EventTime#',
with:
    '#CreateODBCDate(left(Form.EventDate,19))#',
    '#CreateODBCTime(Form.EventTime)#'

I would also strongly recommend that you look into using <CFQUERYPARAM> instead of passing form literals directly into you queries.  This tag will fix any problems with single/double quotes being passed in and assuring the right datatype values are being passed as defined by the <cfqueryparam> tag. For instance, you can say only numeric values can be passed and it will raise an error before it even gets to the database... but that is a different topic.

Author

Commented:
Thanks for your quick reply, but it didn't help.

RE #1: EventTime.  I just typed that into the field to make it easier to read the error message.  But it will not be OBDC time.  It will be a character string (e.g., 9 a.m.-2 p.m., All Day, etc.).

I tried using  the CreateODBCDate function, as you suggested, and got the same error message.  So the problem would appear to be something else.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ack!  I checked several times for missing quotes.  Just show you how important a second set of eyes can be.  Thanks very much.  It's working now.

FYI, when I added the quote, I got the following error, which was corrected when I returned to using '#left(Form.EventDate,19)#' instead of '#CreateODBCDate(left(Form.EventDate,19))#'

Commented:
Yeah, I'm not sure when CreateODBCDate is appropriate and when it isn't. I use Oracle as my back end and I pass in straight Oracle code. I just thought that might be it. I looked at the query and overlooked the missing quote too.  It was when i carefully went over the passed in sql statement that I saw it...

I still recommend looking into using <CFQUERYPARAM>. I'm a strong proponent of that tag. It edits and takes care of ensuring data types are right, and fixes embedded '," problems, etc.  On 'real' databases (ok, slight slam on access... ;)  ), it is the mechanism to generate bind variables, a MUST, especially in Oracle. Your DB performance/scaleability will be adversely affected without this on the real RDBMS engines.

But, that is just a side note. I'm glad you are up and working. Yeah!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.