SQL Server gets confused, insert instead update

Question

Post reply

SQL Server gets confused, insert instead update

PSSQL

Mr or Mrs. 500

Points: 570
More actions
April 28, 2010 at 2:22 am

#221948

Hello,
Several times a month, in different not related databases and servers, I get a strange error while updating a record. Here is the code used
Set cn = New ADODB.Connection
cn.Open DTGetConnSrv
sSql = "SELECT * FROM PHisGinecologia WHERE NumHist = " + lNH
Set rsP = New ADODB.Recordset
rsP.Open sSql, cn, adOpenKeyset, adLockOptimistic, adCmdUnknown
If rsP.EOF = True And rsP.BOF = True Then
MsgBox "Record does not exist"
Else
rsP!NumHist = lNH
rsP!DateUpd = Date
rsP!Description = sDesc
rsP.Update
End If
This code updates a record, changing data of some of their fields. If the record is really not found, I get the MessageBox text. This is working the majority of times, but sometimes I get the following error:
-2147217873: No se puede insertar una fila de claves duplicadas en el objeto 'PHisGinecologia' con índice único 'nh'. -----> Can not insert a row with duplicate keys in the PHisGinecologia object with a unique index 'nh' (nh is the name of the unique index with the field "NumHist").
I have tried multiple variants of this code with no success. What I can not understand is wy SQL Server sometimes interprets this code like an insert

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Maybe I'm wrong, but I see an Update.

Can't it be the cause of the duplicate key error?

-- Gianluca Sartori

PSSQL Mr or Mrs. 500 Points: 570 More actions · Answer 2

Hello Gianluca,

I am using a Visual Basic ADODB recordset.

rsP.Open is where the record is searched for editing.

If the record is not found, a message apears. If the record is found, changes are written individually to each column, and rsP.Update writes back the recordset to the SQL Server.

What I can not understand is if the record is found, why the error is telling about an Insert, that I am not using at all.

WayneS SSC Guru Points: 95460 More actions · Answer 3

PSSQL (4/28/2010)
What I can not understand is if the record is found, why the error is telling about an Insert, that I am not using at all.

Probably because, behind the scenes, SQL is doing a delete and an insert when it updates a record. But if this record has this value, you shouldn't be getting this error. Is it possible that there are processes inserting records concurrently with this, where this number could be inserted?

You might want to check out this BOL link: Using the inserted and deleted Tables

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PSSQL Mr or Mrs. 500 Points: 570 More actions · Answer 4

Hello Wayne,

Thanks for your BOL clarification. As you told, this explains what is happening in the background.

Now I need further investigation, because strange enough, in one side this table is not involved in any bulk update or insert operation, it is allways managed in a one by one basis.

On other side, when the user begins editing this record, a special semaphore text file is created, permitting other users to read the same record, but not to edit or delete. Besides it, the main column is unique.