data updating/deleting/inserting question/issue

[russellbcopeland]

I am using a cconnection to an SQL 2005 database from C# and am running my operatins within a transaction.

Within the transaction I delete a record in one ExecuteNonQuery then I Perform an Insert via ExecuteScalar. If the record I insert has values that conflict with the deleted records values in a unique index the inset fails and the transactin gets rolled back etc.

I have tried various isolation levels on my transaction but it still errors on the insert.

If the deletes are included with the insert block then the insert succeeds.

What am I missing? I thought that the insert would see the deleted version if I used an isolatin level of readuncommitted. Clearly I am missing something?

Any clues?

 

[AlexCuse]

Are you inserting a replacement for the row that you just deleted? Why not just run an update?

Also, is there an identity column in your table being updated? that can cause problems for inserts.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[russellbcopeland]

it could be looked at as a replacement for the deleted row though it is being done in a generic way so that I would have no way of knowing which row it was that needed updating without adding extra overhead to my system. I am trying to avoid that overhead.

yes, there is an identity column in the table and there needs to be.. hence doing the Insert in a separate call than the update/deletes (the update/delete block may be updating and/or deleting numerous rows from numerous tables)

What I am getting at is trying to figure why the insert does not recognize the fact that the record has been deleted when the calls to the database are made separately though if made in one block the insert recognizes the deletes. This seems to be regardless of the fact that it is all being doen in a single transaction with any isolation level. Is there a way to make this work.

If not I'll have to do a lot more work and introduce some added ineffeciencies in my system by either reading the schema of the table to determine the unique indexes and checking in my objects for deleted rows with that set of values and reseting that record instead of inserting it so I can just do an update or adding the deletes to the insert block. Either way that increases my overhead.. which I am trying to avoid... It also bugs me not to know why the insert cannot recognize the delete.

 

[AlexCuse]

Have you set Identity_Insert property to ON when you run your query?

You can't insert to a table with an identity column without this unless you specify a column list (and make sure not to insert anything to the identity column).

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[russellbcopeland]

thanks but no that is not the problem. I'm not inserting the identity values... they are generated by sql. the inserts all specify their column lists. the problem only happens when the insert violates a unique index when the delete should have cleared out the violating record.

 

[AlexCuse]

I still think an update is the way to go, if you know what row to delete then you already know what row to update as well.

Anyway, you have both these operations within the same transaction, right? Have you tried debugging in Management Studio?

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Smeat]

I agree with Alex in that if you know which record to delete then you know which record to update and that should be the way to go.

If there is a specific reason why this is not a viable option then you might try doing a save point on the transaction after the delete operation (assuming your using an ADO.NET transaction) though I have no idea if this will solve your problem.

HTH

Smeat