asked on

SQL Insert Error

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.

RCorfman

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.

elhome

ASKER

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.

ASKER CERTIFIED SOLUTION

RCorfman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elhome

ASKER

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))#'

RCorfman

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!