"Operation must use an updatable query" error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

"Operation must use an updatable query" error

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-03 : 18:33:22

Hello,

How are you today?

When I run this query:

update [Input Table] set [Input Table].F_NUMBER = 'Not Stated' where [Input Table].F_Number is Null

I get the error message:

"Operation must use an updatable query"

My table structure looks like this:


Column Name    Data Type Length Allow Nulls
[TRAV-KEY]	int	   4	        0
C_NUMBER	nvarchar   11		1
F_NUMBER	nvarchar   11		1
F_ARRV_DAT	nvarchar   11		1
F_TIME		nvarchar   4		1
AD_CODE	        nvarchar   1	        1
SEX		nvarchar   1		1
BIRTH_YR	nvarchar   4	 	1
CNRTY	        nvarchar   15	  	1
NATY	 	nvarchar   10		1
OCCUPATION	nvarchar   15	  	1
RESIDENCE	nvarchar   23		1
PROVINCE	nvarchar   2	 	1
PORT_ED		nvarchar   15		1
ACCOM		nvarchar   10		1
PURPOSE		nvarchar    2		1
L_O_STAY	nvarchar    5		1
POSTAL_CD	nvarchar    7		1
ErrorCheck	bit	    1		1
ErrorString	nvarchar   255		1

Any ideas why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-03 : 18:36:05

Googled the error and found this:

http://dbforums.com/arch/213/2002/10/543578

Does that help?

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-03 : 18:48:54

Hi,

Sorry, let me explain what I am doing. I have a Microsoft Access Database which acts as a front end to my SQL Server database (Access Project).

I have encountered this problem with IIS database permissions and MS Access in the past while developing asp applications, but I don't think that it applies in this situation.

As well, initially [Input Table] did not have a primary key defined, but on the suggestion of someone I made [TRAV-KEY] the primary key, but still got the same error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-03 : 18:51:54

So does the query work in Query Analyzer:

UPDATE [Input Table]
SET F_NUMBER = 'Not Stated'
WHERE F_Number IS NULL

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-03 : 19:20:32

is that your entire UPDATE statement? typically that error has nothing to do with permissions, but rather when you are trying to update a "non-updatable" query, such as an aggregated query or several tables joined together.

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-04 : 11:54:56

Hello,

Thanks for the responses

1. Yes, the Query works in Query Analyser.
2. Yes, this is the entire Update statement. The statement is actually built dynamically:

Set Fieldsqry = db.CreateQueryDef("", _
"update [Input Table] set [Input Table]." & DynFN & " = '" & DynRV & "' where [Input Table]." & DynFN & " is Null ")

Fieldsqry.Execute

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-12 : 10:01:17

Hi,

I forgot to relink the tables after adding the primary key. Once I did this the query ran ok.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-03-12 : 10:59:23

Syntacks.

Find a way to messagebox or debug the dynamic created part of Fieldsqry once it is assigned.

It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-12 : 12:37:25

Hello,

Just to clarify, my issue has been resolved and the query is running fine now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources