A

Anonymous

Answered

Syntax error in insert query

Forum|Forum|17 years ago
December 17, 2008
7 replies
2724 views

I have a flash form that is used to inserts a record into an Access database table. In the for there are 4 datefields, several text input fields, and several select fields. When I submit the form, I get a syntax error that reads:

Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
The error occurred on line 184. Complex object types cannot be converted to simple values.

Line 184 is the last line of the Values in the insert query. The query looks like this:

<CFQUERY DATASOURCE="#REQUEST.DataSource#">
INSERT INTO EstimateNumber(
BidNumber,
Project,
JobSite,
EstimatedBy,
Region,
Company,
Division,
InquiryNumber,
SafetyChecklist,
SafetyChecklistDate,
QCChecklist,
QCChecklistDate,
EstimatedValue,
UserUsername,
UserPassword,
Updated,
ReviewDate,
ReviewedBy,
Discipline,
BidDate,
JobNumber,
UpdatedBy
)
VALUES(
#FORM.BidNumber#,
'#FORM.Project#',
'#FORM.JobSite#',
'#FORM.EstimatedBy#',
#FORM.Region#,
#FORM.Company#,
#FORM.Division#,
'#FORM.InquiryNumber#',
#FORM.SafetyChecklist#,
#FORM.SafetyChecklistDate#,
#FORM.QCChecklist#,
'#FORM.QCChecklistDate#',
#FORM.EstimatedValue#,
'#FORM.UserUsername#',
'#FORM.UserPassword#',
#FORM.Updated#,
#FORM.ReviewDate#,
#FORM.ReviewedBy#,
#FORM.Discipline#,
#FORM.BidDate#,
#FORM.JobNumber#,
'#FORM.UpdatedBy#'
)
</CFQUERY>

Any recomendations?

Database access

This topic has been closed for replies.

Correct answer -__cfSearching__-

chrispilie wrote:
> Any recomendations?

1) Turn on debugging so you can view the actual query generated and post the sql here
2) Dump the form scope. Are all of the form values simple strings?
<cfdump var="#FORM#">

3) For values inserted into a date/time column, you should use cfqueryparam or CreateODBCDate, CreateODBCDateTime to convert the string to a proper date/time object. This ensures the value is inserted correctly

Though I personally recommend using cfqueryparam for _all_ parameters (not just date values).

D

Dan_Bracuk

Inspiring

This is a troubleshooting suggestion. Write your query with the commas at the start of each line.

insert into atable (
field1
, field2
etc
, fieldx
)
values
(
value1
, value2
etc
, valuex
)

Then comment out all the fields and values except the first one. Run the query. If it crashes, do what you have to do to make it work. Once it is working, uncomment the fields/values one by one and fix each problem as it comes up.

A

Anonymous

Thanks Dan. I actually did this several times. I was getting some really strange results.The process did help greatly. Your suggestion helps me feel better about my troubleshooting skill development. I decided to start from scratch and be a little more careful with my coding since troubleshooting this issue just made a mess out of my code. I recoded the form and insert query. Cfsearching's and Adam's suggestions seem to be the one that helped over all. I am still a newb so reading up and using the cfqueryparam was very helpful. I had been accustomed to going the long way around these problems due to lack of undestanding of the tag and this cleared up a lot. Ultimately my sloppy code was the cause of the problem. Thanks all of you for the help.

N

Newsgroup_User

Inspiring

you have some of your date values surrounded with ' (single quotes) and
others not. i can't recall right now how access wants them, but your
best bet is to use <cfqueryparam> and createodbcdate() to ensure access
understands the date you are passing to it correctly.

somewhere in the back of my mind i seem to recall some strange things
access wanted with dates, like you have to pass them as #01-01-2009# or
something like that.... but createodbcdate() and <cfqueryparam> are a
MUCH better option...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

A

Anonymous

Date/Time in Access provide some pretty strage hurdles. In many occassions I have simply changed the field to a Text field and ran with that and never seemed to have problems with the result. I didn't like doing it but I never received errors. I have been receiving some parameter errors with <cfqueryparam> like the database doesn't recognized the preceding declared field in the update or insert query. Here is an example of a wierd error with Access. I have 2 tables. One is for estimate numbers and the other is for sub estimate numbers that can be assigned to specific estimate numbers. If is actually a complicated explanation about what is taking place behind the doors on this. The concept is simple on the surface. Both tables have the same fields except the subestimate table has 1 extra for a user defined subestimate number. The only relation between the two will be the estimate number. This insert works fine.

INSERT INTO EstimateNumber(
BidNumber,
Project,
JobSite,
EstimatedBy,
Region,
Company,
Division,
InquiryNumber,
SafetyChecklist,
SafetyChecklistDate,
QCChecklist,
QCChecklistDate,
EstimatedValue,
UserUsername,
UserPassword,
ReviewDate,
Discipline,
BidDate,
JobNumber,
UpdatedBy
)
VALUES(
#FORM.BidNumber#,
'#FORM.Project#',
'#FORM.JobSite#',
'#FORM.EstimatedBy#',
#FORM.Region#,
#FORM.Company#,
#FORM.Division#,
'#FORM.InquiryNumber#',
#FORM.SafetyChecklist#,
<cfqueryparam
value="#FORM.SafetyChecklistDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.QCChecklist#,
<cfqueryparam
value="#FORM.QCChecklistDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.EstimatedValue#,
'#FORM.UserUsername#',
'#FORM.UserPassword#',
<cfqueryparam
value="#FORM.ReviewDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.Discipline#,
<cfqueryparam
value="#FORM.BidDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.JobNumber#,
'#FORM.UpdatedBy#'
)

This next one is the same exact query with the same table setup with one added field but kicks back a parmeter error expecting 4. I can't explain it.

INSERT INTO SubEstimate(SubBidNumber,
BidNumber,
Project,
JobSite,
EstimatedBy,
Region,
Company,
Division,
InquiryNumber,
SafetyChecklist,
SafetyChecklistDate,
QCChecklist,
QCChecklistDate,
EstimatedValue,
UserUsername,
UserPassword,
ReviewDate,
Discipline,
BidDate,
JobNumber,
UpdatedBy
)
VALUES(#FORM.SubBidNumber#,
#FORM.BidNumber#,
'#FORM.Project#',
'#FORM.JobSite#',
'#FORM.EstimatedBy#',
#FORM.Region#,
#FORM.Company#,
#FORM.Division#,
'#FORM.InquiryNumber#',
#FORM.SafetyChecklist#,
<cfqueryparam
value="#FORM.SafetyChecklistDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.QCChecklist#,
<cfqueryparam
value="#FORM.QCChecklistDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.EstimatedValue#,
'#FORM.UserUsername#',
'#FORM.UserPassword#',
<cfqueryparam
value="#FORM.ReviewDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.Discipline#,
<cfqueryparam
value="#FORM.BidDate#"
cfsqltype="CF_SQL_DATE "
maxlength="50"
>,
#FORM.JobNumber#,
'#FORM.UpdatedBy#'
)

IF I get rid of the cfqueryparam fields it works. If I simply remove the param, I get a Syntax error. Needless to say I commented this query for the sake of sanity for the time being.

-

-__cfSearching__-

Inspiring

> Date/Time in Access provide some pretty strage hurdles.
> cfsqltype="CF_SQL_DATE(space)"

I have not used Access regularly in quite some time. But my recollection is that using CreateODBCDate/cfqueryparam solved any issues. Though I used cf_sql_timestamp, not cf_sql_date. It is what MS SQL uses, and seemed to work for Access as well.

As an aside, is that trailing space after cf_sql_date a typo?

> kicks back a parmeter error expecting 4

IIRC, one cause of that error is passing in a string without single quotes. Without the quotes MS Access does not recognize the form value as string. So it looks for a column or some other object with that name. Obviously it cannot find one, which causes an error.

Example:
INSERT INTO ( TextColumn )
VALUES ( ThisIsAStringThatShouldBeQuoted )

> I can't explain it.
> ...
> form.SUBBIDNUMBER:N
> ..
> VALUES(#FORM.SubBidNumber#,

As Azadi mentioned you have some conflicting or suspcious formats in your values clause. For example:

1. In your form dump SUBBIDNUMBER looks like a string: "N". Yet it is not surrounded with single quotes in your values clause. That would cause a syntax error

2. The form dump shows at least three different date formats: mm-dd-yyyy, mm/dd/yyyy and ODBC format. Date strings are handled differently than ODBC formatted date values. One requires single quotes and the other does not. If you confuse the two it usually causes a syntax error.

> form.QCCHECKLIST:false
> #FORM.QCChecklist#,

3. Based on the values, you have several columns that might be either "text" or "yes/no" columns. Some of those values are not single quoted. So depending on the column data type, it may cause an error.

As we do not know the table structure or actual values used, we can only guess about the problem. You should enable debugging (See Adam's post for details). As he mentioned "It might be more obvious what the error is if you check the actual SQL.".

A

Anonymous

How is this?

form.UPDATEDBY:ChrisPilie
form.ESTIMATEDBY:sdfsd
form.QCCHECKLISTDATE:01-01-2000
form.INQUIRYNUMBER:no
form.REVIEWEDBY:
form.DIVISION:5
form.JOBSITE:Citgo Lyondell
form.PROJECT:sdfgfsdf
form.SUBMITIT:Insert
form.FIELDNAMES:SUBMITIT,USERUSERNAME,USERPASSWORD,UPDATEDBY,UPDATED,BIDNUMBER,JOBSITE,PROJECT,DISBIDNUMBER,DISCIPLINE,BIDDATE,REVIEWEDBY,REVIEWDATE,JOBNUMBER,ESTIMATEDBY,ESTIMATEDVALUE,INQUIRYNUMBER,SAFETYCHECKLIST,SAFETYCHECKLISTDATE,QCCHECKLIST,QCCHECKLISTDATE,REGION,DIVISION,COMPANY
form.QCCHECKLIST:false
form.BIDNUMBER:16
form.BIDDATE:12/15/2008
form.SAFETYCHECKLISTDATE:01-01-2000
form.ESTIMATEDVALUE:234234
form.USERPASSWORD:scott
form.UPDATED:{d '2008-12-17'}
form.DISBIDNUMBER:16
form.SAFETYCHECKLIST:false
form.COMPANY:2
form.JOBNUMBER:0
form.DISCIPLINE:10
form.REGION:5
form.REVIEDWBY:no
form.USERUSERNAME:chris
form.REVIEWDATE:01-01-2000
form.SUBBIDNUMBER:N

A

Anonymous

I haven't used the debbugging feature so I am not versed on the proccess.

N

Newsgroup_User

Inspiring

First of all, convert all your values into parameters, using
<cfqueryparam>. And do that every time you write s query with dynamic
values from now on.

Second, dump the form scope to see if there are any unexpected values in
there.

Third, switch on debugging, and robust exception handling, and eyeball the
*actual query* that CF is sending to the DB driver. It might be more
obvious what the error is if you check the actual SQL.

--
Adam

A

Anonymous

I used the cfqueryparam and have received som success. I am however getting some parameter errors on other very similar queries that I applied it to.

A

Anonymous

I toyed with the CreateODBCDate before within the insert statment and I got this error.

Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{d '2008-12-01'}''.
The error occurred on line 197. Complex object types cannot be converted to simple values.

-

-__cfSearching__-

Inspiring

Can you post the actual sql that was generated? It is hard to guess without seeing the values that were passed.

-

-__cfSearching__-Correct answer

Inspiring

chrispilie wrote:
> Any recomendations?

1) Turn on debugging so you can view the actual query generated and post the sql here
2) Dump the form scope. Are all of the form values simple strings?
<cfdump var="#FORM#">

3) For values inserted into a date/time column, you should use cfqueryparam or CreateODBCDate, CreateODBCDateTime to convert the string to a proper date/time object. This ensures the value is inserted correctly

Though I personally recommend using cfqueryparam for _all_ parameters (not just date values).

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded