473,320 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 8320
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Rob Tweed | last post by:
I have a customer who is having problems when their Windows 2000/IIS/PHP-based system begins to experience a level of loading that isn't, in my view, unreasonably high. I'm wondering what others...
1
by: Reza Sadeghi | last post by:
Hi I am getting this error when I tried to browse any asp.net in visual studio.net. I can build the project but when I try to run and debug the project I get error message that "Unable to start...
3
by: hussein | last post by:
'm not bale to run my projec on the 2000 server, he error was: error while trying to run project: unable to start debugging on the web server.erver side error occurred on sending debug HTTP request...
16
by: Serdar Kalaycý | last post by:
Hi everybody, My problem seems a bit clichè but I could not work around. Well I read lots of MSDN papers and discussions, but my problem is a bit different from them. When I tried to run the...
4
by: Jim Hammond | last post by:
It would be udeful to be able to get the current on-screen values from a FormView that is databound to an ObjectDataSource by using a callback instead of a postback. For example: public void...
22
by: EP | last post by:
When running my asp.net hosting service (asp.net without IIS), on server 2003 with IIS not installed, I get the following when trying to process a request. "System.DllNotFoundException: Unable to...
17
by: Benoit Martin | last post by:
I'm working on a project in VB.net connecting to a SQL Server 2000 database that I can't modify I created a dataset with a schema identical to the DB. When trying to update the DB from the dataset...
3
by: V T | last post by:
Hello all, SQL Server 2000 documentation http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx states that if view is using "NOT NULL" columns of a base table, then...
1
by: SkyGringo | last post by:
Hi. We are using SQL Server 2005 64-bit on Windows Server 2003 R2 x64 Enterprise Edition. I have a really weird problem where I have people out there who are placed in the db_owner,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.