asked on

Any idea what this query error means?

I'm getting this error when i use an insert query.
Any ideas of what it means?
Here is the error, and below that is my query:

/*****************************************************/
Error Diagnostic Information
unknown exception condition

PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (34:2) to (34:50) in the template file E:\Inetpub\wwwroot\pmo\scheduler\all_from_jad.cfm.
/*****************************************************/

/*****************************************************/
<CFQUERY datasource="pmocalendar3" name="query3">
insert into pmo
(PhaseNum, InitiatNum, meetdate, StartTime, EndTime, meettype, Bridgenum, Location, sesphone, Releaseinfo, Status, InitiatTitle, dtedoccreatedate, dtedoceditdate, EhRev, timezone, RemarksHistory, sesmanager, strFunctionalBrkdwn, meetdate2, JadDate)
values('#PhaseNumber#', '#InitiativeNumber#', '#Dateformat(JadDate, "m/d/yyyy")#', '#JadStartTime#', '#JadEndTime#', '#DocumentType#', '#JadBridge#', '#JADLocation#', '#JadWork#', '#Release#', '#Jadstatus#', '#InitiativeTitle#', '#DateEntered#', '#EhDate#', '#EhRev#', '#Time_ZoneEN#', '#JADRemarksHistory#', '#JadContact#','#Introduction#', '#Dateformat(JadDate, "mm/dd/yyyy")#', '#JadDate#')
</cfquery>

/*****************************************************/

TallerMike

Are you changing/adding/looking at any application/server/session variables on that page?

If so, ensure that you have put a CFLOCK around the action

TallerMike

Also, if you are using SQL 7, you may have a problem with some of the components it installs.

Please see the following:

http://search.atomz.com/search/?sp-a=sp1001395b&sp-p=any&sp-q=PCodeRuntimeContextImp%3A%3AexecuteSQLTagCFQ

Looks like you probably need to update your drivers

anandkp

Hi there,

the usual cause of this is generally the
1. text fields missing out teh single quotes
2. numeric fields enclosed in single quotes
3. date fields not formated using creteODBCdate()
4. missing out expressions like "where / into / tablename ... & so on ...

In ur case its the date i feel - just use the createODBCDate thre & then run the query & see what it gives

the best way to debug wld be to print the query using CFOUtput .. so u know waht values r going in.

let me know

K'Rgds
Anand

cgttsp01

ASKER

Arand,

I should have explained earlier that this query is in a loop output of another query. Meaning I run a select query from a Lotus Notes db, then loop through that query's recordset with this query to insert the fields into my Access table.

Since the variable for date already exists, because I am just passing it from the select query to the insert query, how can I use createODBCDate? In my insert query I am trying to formate the date coming from the lotus notes table so it will go into my Access table cleanly.

Any other suggestions?
Thanks.

anandkp

Since ur inserting a date field in a DB - u shld use CreateODBCDate(date) - so it can be inserted in ur table ...

i hope ur getting what i am trying to say here ...

K'Rgds
Anand

TallerMike

<CFQUERY datasource="pmocalendar3" name="query3">
INSERT INTO pmo
(
PhaseNum,
InitiatNum,
meetdate,
StartTime,
EndTime,
meettype,
Bridgenum,
Location,
sesphone,
Releaseinfo,
Status,
InitiatTitle,
dtedoccreatedate,
dtedoceditdate,
EhRev,
timezone,
RemarksHistory,
sesmanager,
strFunctionalBrkdwn,
meetdate2,
JadDate
)
values
(
'#PhaseNumber#',
'#InitiativeNumber#',
#CreateODBCDate(JadDate)#,
'#JadStartTime#',
'#JadEndTime#',
'#DocumentType#',
'#JadBridge#',
'#JADLocation#',
'#JadWork#',
'#Release#',
'#Jadstatus#',
'#InitiativeTitle#',
'#DateEntered#',
'#EhDate#',
'#EhRev#',
'#Time_ZoneEN#',
'#JADRemarksHistory#',
'#JadContact#',
'#Introduction#',
#CreateODBCDate(JadDate)#,
'#JadDate#')
</cfquery>

AS anandkp was explaining, here's what it would look like

cgttsp01

ASKER

TallerMike,

Thanks for clarifying that. I changed my query to match what you have above, but got this error.

Why am I getting this?

This is a simple Access DB.

/************************************************/
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{d '2002-11-20'}''.
/************************************************/

cgttsp01

ASKER

I noticed in the output of query one to Notes db that there was a special character '@' in the field initiative_title for a specific record.

So, I excluded that record and both my queries worked fine after that.

Now I need to eliminate any special characters, can I do that in the query?

ASKER CERTIFIED SOLUTION

TallerMike

membership

This solution is only available to members.

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

Start Free Trial

cgttsp01

ASKER

Thanks TallerMike.
Your suggestion works perfectly!

TallerMike

Don't forget anandkp whose original suggestions were of great help, all I did was expand on them. He deserves all or at least a split for his part.

cgttsp01

ASKER

good point.
I will setup another question just for points for him.
stephen