update and insert query error, but select works ok.

[JoyRose]

:rose;
Any ideas spring to mind about the following issue?

I'm getting an error trying to run an Update or Insert query. I can run a
Select query in the application, but not Insert or Update.
The Update and Insert queries works with SQL Query Analyzer.

Thanks.




<cfquery name="qUpSite" datasource="#request.optomDS#">
insert into optomSite
(SiteName)
VALUES('ThirdSite')
</cfquery>

<cfquery name="qUpSite" datasource="#request.optomDS#">
update optomSite
SET SiteName='TestSite'
where siteID = 1
</cfquery>

<cfquery name="qUpSite" datasource="#request.optomDS#">
SELECT *
FROM optomSite
where siteID = 1
</cfquery>

[Kronin555]

Your queries look fine. What's the error you're getting?

Also, just to cut back on the ping-pong questions and incase it's relevant
once you post the error:

what database?
what datatypes are those columns?

[JoyRose]

Thanks for replying.

Part of the problem is I'm not getting a useful error:
12:06:01.001 - Database Exception - in
F:\DEV\Internet\Misc\JHolman\Optometry_db\optomAdm in\act_data2db.cfm : line 14
Error Executing Database Query.

SQL Server

siteID type = int (automatic identity column, primary key)
siteName type= varchar, length 100 ( no nulls allowed)

Like I said, the queries run fine in SQL Query Analyzer. The table is updated,
no problem.

Thanks for whatever insights you can offier!

[mxstu]

In the CF administrator, check Enable Robust Exception Information and any other database options so that the error message will provide more information.

[Kronin555]

Are you allowing Update and Insert statements to be executed for that
datasource? I've never disabled those, so I don't know if Coldfusion would give
you a nicer error message or not...

Also, do what mxstu said. Enable Robust Exception Information and also check
Exception Information further down on that page.

[JoyRose]

I'm checking with the web server admin on that.

Another thing, but I don't think it could be related:
I've got security set up in the app. I use a cfif statement checking for
IsUserInRole(2) in the index page.
Everything else in the app works and the insert and update queries are called
into the index page. I also
tried the insert and update queries right after the security check and they
still don't work.

[mxstu]

Yes. Check the datasource options that Kronin555 mentioned. Make sure
INSERT and UPDATE are checked in the "Allow SQL" section. If those options
are unchecked (ie. disallowed), CF will usually give you a message like:

"Error Executing Database Query.
Executing the SQL statement is not allowed. "

[JoyRose]

I just noticed your question about allowing Update and Insert to be executed
for that datasource. That would be set by the SQL DBA, right? Would the SQL
Query Analyzer update the table if Update and Insert weren't allowed for the
datasource? Or are datasource permissions set in Cold Fusion Admin?

[mxstu]

No. You can set allowed sql operations in the CF Administrator. It is high
level and essentially allows you to say "do not allow ANY insert operations
for this datasource...." or ... "allow insert, delete and update operations
only".

Another possibility is that the account used to define the datasource has the
wrong the wrong database permissions. Did you test your queries in the query
analyzer using the same account used for the CF datasource?

There are a few possibilities but I would look at the detailed error message
first. It will tell you what the problem is.

[JoyRose]

I didn't have permission through ColdFusion Admin.
All better. :+>

Thanks for your help everyone!!

Make sure INSERT and UPDATE are checked in the "Allow SQL" section. If those
options are unchecked (ie. disallowed), CF will usually give you a message like: