473,563 Members | 2,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 8326
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****@sommarsk og.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
2046
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 think, specifically: - are these volumes, for this kind of configuration, at or beyond the levels at which PHP should be able to cope? I hope not...
1
2966
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 debugging on the web server. server side error occurred on sending debug HTTP Request. I am running WinXP Pro SP1 with all hotfixes and VS.NET...
3
3042
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 mak sure that the server is running correctly . verify that there are o syntax errors in the web.config... i check the IIS, it is running...
16
4183
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 project in debug mode (by hitting F5) it gives an error message "Error while trying to run project: Unable to start debugging on the web server.
4
3390
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 RaiseCallbackEvent(string eventArgs) { // update the data object with the values currently on screen FormView1.UpdateItem(true); }
22
3251
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 load DLL (aspnet_isapi.dll)." Of course the dll is able to be found, it's still in the framework directory and for grins I even put it in my...
17
3803
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 using the dataAdapter.update sub, I get an error. The problem seems to be that one of the fields was named 'desc' which is a reserved word for...
3
7360
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 insert/update performed on a view must provide dummy values for those columns, and code of the trigger should ignore them. But I cannot reproduce...
1
3681
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, db_accessadmin, and db_securityadmin roles in their database, and yet they are unable to add new users to their databases, despite the fact that logins exist...
0
7658
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7579
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7876
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7630
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7941
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3628
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
909
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.