469,332 Members | 6,678 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,332 developers. It's quick & easy.

Unable to update view in Sql Server 2000

Hi,
I have an application that's running fine on development servers (web

and database-sql server 2000). I'm updating a record through a third
party component but I don't think the component is the problem. What's
happening is that I'm updating fields that are part of view. I'm only
updating fields in one table of the view and this works fine in the
development environment.
What happens in the production environment when I try to update
(using the third party component) I get the following message:
"Current recordset does not support updating. This may be a limitation
of the provider or of the selected locktype."
As an experiment I took the same code but removed the view, leaving
only the table I want to update as the record source. In that case the
update worked. So it seems that something in the production database
doesn't like me updating a view. However I can do that in the database
in the development environment.
The third party component is dbnetgrid which works fine in the
development environment. I can only conclude it's something about the
database that prevents me from updating this same table if it's in a
view. I've talked to our DBA but he says there's no difference between
the databases. Any ideas would be appreciated.
Neil

Oct 18 '05 #1
2 8159
In Standard SQL, an updatable view has to be built on one table and has
to include a key. The idea is that a row in the VIEW maps to one and
only one row in the base table. It is very restricted.

Some vendors allow for more power than that, but this is not uniform or
well-defined. The real problem is that the question of view updatablity
is known to be undecidable.

What you have as a portable, standard way to handle a multi-table view
update is an INSTEAD OF trigger. You can look up the SQL Server
version in BOL.

Oct 18 '05 #2
fig000 (ne***********@yahoo.com) writes:
I have an application that's running fine on development servers (web
and database-sql server 2000). I'm updating a record through a third
party component but I don't think the component is the problem. What's
happening is that I'm updating fields that are part of view. I'm only
updating fields in one table of the view and this works fine in the
development environment.
What happens in the production environment when I try to update
(using the third party component) I get the following message:
"Current recordset does not support updating. This may be a limitation
of the provider or of the selected locktype."
As an experiment I took the same code but removed the view, leaving
only the table I want to update as the record source. In that case the
update worked. So it seems that something in the production database
doesn't like me updating a view. However I can do that in the database
in the development environment.
The third party component is dbnetgrid which works fine in the
development environment. I can only conclude it's something about the
database that prevents me from updating this same table if it's in a
view. I've talked to our DBA but he says there's no difference between
the databases. Any ideas would be appreciated.


Unfortunately, this is difficult to answer without knowledge about
the submitted queries and the view and table definitions.

I guess the component submits the queries. To see what that is, you
cam use Profiler. However, this requires sysadm privileges, which I
assume that you don't have.

To compare the view table definitions, you can use sp_help, or script
them from Query Analyzer. Give particular attention to keys.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Rob Tweed | last post: by
1 post views Thread by Reza Sadeghi | last post: by
3 posts views Thread by hussein | last post: by
16 posts views Thread by Serdar Kalaycý | last post: by
1 post views Thread by SkyGringo | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.