Can't Edit Table

Hello, I'm an undergrad who is using SQL Server 2005 for the first time. I have a table Components where there are three columns, ID, Name and Value, where ID and name are the composite key.

I have entered some data into it from the user interface and it is entered just fine. But when I try to edit data in Value column it can't be done. This error message comes...

"No Row was updated
The Data in Row 7 was not committed
Error source: Microsoft.VisualStudio.DataTools
Error Message: The Row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3 rows).
Correct the errors and retry or press Esc to cancel change(s)."

The way I get it, does it say that it is being bound to VS.NET environment? I use it in my VB . NET programme. But I close every time that I open a connection. I'm positive about that. Then I tried, restarting the application, nothing happened! And then I closed Visual Studio .Net and even then nothing happened. And I'm getting very frustrated....

The funny thing is that, when I write a query using the SQL Pane of the SQL Server 2005 then, everything works perfectly. And I don't understand what this is...

I really appreciate if anyone can shed some light into me.
Thank You,
Prasad.

hi,

It seems that you are violation a primary key constraints,

If your using visual studio.net 2003 and later

a schema of the table is stored in the dataset for 2003

or other sqlclient objects of vsnet 2005.

A primary key constraints can be implemeted on these data layers(dataset.. and the likes)

And on the database.

what you can do.

1. Refresh the dataset schema (datatables.. etc) and other ado.net implementation

2. verify that your data is not violting pk on the database tables

hope it helps

|||

I am also getting the same message. I have been using 2000 for some time and this error does not make sense.

My table has no primary key or any relationship to another table. I created the table in the SSMSE and added records using ADO in VB6. I can not delete records and get the following message: "A Problem occurred attempting to delete row X. Error Source: Microsoft.VisualStudio.DataTools. Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(Y rows). Correct the errors and attempt to delete the row again or press ESC to cancel the changes(s)."

SQL will give me a very similar message if I try to edit a field in any of these rows.

Some rows SQL will allow me to edit or delete.

Any Ideas?

Peter

|||

Apparently this only effects SSMSE as a DELETE query issued from inside VB6 using an ODBC connection works flawlessly. This is the same DELETE query I issued using the Query designer in SSMSE.

Seems like a bug in SSMSE.

Peter

|||Suppose you have two identical rows in your table, where column X has the value 7. The query DELETE FROM yourTable WHERE X = 7 will delete both of these rows, and there will be no error. Now suppose you open the table in SSMS and try to delete just one of these rows (or edit just one of them). You will get an error, because there no DELETE (or update) statement is possible to modify just one of two identical rows.

Even without identical rows you may get this error when there is no primary key on the table. The position of a row in the visual editor is not an intrinsic property of the data in the table, and the only modifications you can make to a table are those that can be written as queries with conditions that depend on the column values in the table. A task like "delete the third row" does not correspond to a delete query unless some set of column values unambiguously describes the third row (in other words, unless there is a key). The row number as the table appears in the visual editor is not a table column, so it can't be the sole property used to identify the row to be deleted.

Steve Kass
Drew University
|||

I did write a query:

DELETE FROM SalesData WHERE Sequence = 5;

This should have deleted any rows where the Sequence Column is equal to 5. This query did not work in SSMSE when the rows where the Sequence was equal to 5 are identical. My understanding is any rows with a Sequence of 5 should be deleted. This works from VB6 and will not work in the query designer under SSMSE.

Maybe there is some interpretation going on when I select the 7th row in the SSMSE GUI and it tries to delete the rows one at a time and cannot do this since the rows are not unique. The SSMSE query designer should work the same as a Query from VB6 shouldn't it?

SalesData:
Seq Name ID
5 Tom A
5 Tom A
6 Bob B
6 Bob C

These do not work from within SSMSE using wither the GUI or the Query designer and DO work from VB6:

DELETE FROM SalesData WHERE Seq = 5;
DELETE FROM SalesData WHERE Seq = 5 AND ID = 'A';
DELETE FROM SalesData;

These work as expected:

DELETE FROM SalesData WHERE Seq = 6;
DELETE FROM SalesData WHERE Seq = 6 AND ID = 'B';

Peter House

|||

Hi there

got the same problem. Did you manage to solve it?

regards

Michael J

|||

Michael,

I did solve the problem - sort of. Apparently this is an issue with the SQL Management Studio environment. I could do the same exact query from within a VB program or MS Access Query and they would work correctly while the query would not work in the Management Studio.

I have not experienced this problem in a while and use the Management Studio Interface a lot with both the Express and Standard versions.

Peter

|||

Peter

I first experienced it now and I have had ss05 for 5 month and used the feature a lot. But as you say. One can do without it and hope that it resolves.....

Thanks for your reply!

Michael