Hash Deleted and autoincrement ID by 2 not 1

Question

Hash Deleted and autoincrement ID by 2 not 1

shaimaa.tarekelshoeiby

SSC Veteran

Points: 212
More actions
September 15, 2014 at 4:00 am

#293177

Hi All,
When I have a record that I copy and paste (Access 2010 datasheet view) into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns
Refreshing , I find that the id is incremented by 2 instead of one.
That is if the previous record has ID 20 , the #deleted record has ID 22 .
Anyone has an explanation and ideas to avoid the #deleted which happens if not all columns are filled(an explanation for this too please)

Viewing 15 posts - 1 through 15 (of 20 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

An MS Access database? Or linked tables to SQL Server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 2

shaimaa.tarekelshoeiby

SSC Veteran

Points: 212

September 15, 2014 at 4:53 am

#1745427

Linked to SQL Server 2012 view

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You're going to have to have a look at the definition of the view and test, on the SQL side, why inserts fail. Maybe one of the fields you're not specifying is required, or maybe you're inserting duplicate values into a unique column

As for the identity column, that's expected behaviour. A failed insert will increment identity columns same as a successful one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 4

I am inserting duplicate values in a unique column on copy-paste.

I actually copy paste and then I display the message of duplicate value then make that column have a non-duplicate value .

But if not all records have values, I get the hash deleted even though on refreshing the record is there with ID = previous ID +2

What tells SQL that when finding a duplicate value , delete the record?

Also, how can I fix the hash deleted issue AND make the ID increment to 1 and not 2?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It's not deleting it. It's failing to insert it. Duplicate values are not permitted in a unique constraint.

As I just explained, because you're having the insert fail, the identity value increments once for the insert failure (expected behaviour) and then a second time for the insert that succeeds.

As for how you fix it, stop inserting known duplicate values. Insert the correct data the first time and you won't have these problems. They're occuring because you are pasting duplicate values in and then fixing them later. Rather put the correct values in first time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 6

Well actually it's just that a user first attempts to copy a record then add the values they desire , but SQL won't be patient enough and will display a unique constraint error which is catched in Access and displayed as a more user-friendly message.

It is normal to first copy an entire record, paste it somewhere then attempt to add the changes a user wants in the new record which is far easier than having to insert all the record values in a new record.

Also, when all values of a record are there , and the duplicate record is changed to a non-duplicate one - a hash deleted does not happen

Otherwise if there are missing columns, it displays hash deleted (though the record is actually written and can be seen on refresh)

Any explanations for this and a solution please?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

I gave you a solution. Stop copy-pasting existing data, or write up an access form that gives you the behaviour that you want to see.

It's nothing to do with SQL 'not being patient'. When you enter a row in an Access datasheet that's linked to SQL, Access sends the row immediately when focus leaves the row (enter pressed for example). SQL picks that up as a duplicate key error (which it is) and rejects the inset, which increments the identity and gives you the errors

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 8

Ok , I understand

But what about the hash deleted . Any explanation and workaround ?

At first I thought it is because a record is inserted and the view wasn't refreshed to view this new record but it happens only when I copy/paste an entire record without providing value for all columns.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 9

Could it be that any of the empty columns do not accept a NULL value?

That would cause the insert to fail, resulting in the described behavior.

shaimaa.tarekelshoeiby SSC Veteran Points: 212 More actions · Answer 10

I am able to insert normally.

Only in case of copy/paste , the hash deleted happens if not all values are supplied for all the columns of the pasted record.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 11

Does your datasheet have any bound lookup columns?

If so, look at this.

It does sound like Gail is on track with the insert is failing.

Causing the increment to increase more than you expect.

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 12

I created a simple table in SQL Server 2012

Then a new Access db

Then linked the table by importing and choosing the linked option

Right clicking on a row (on the gray to the left), select Copy

Then right click on the gray to the left of new row, then select Paste

Then arrow down

Works just fine, leaving out the id, and incrementing as expected.

So I can't replicate the issue.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Greg Edwards-268690 (9/15/2014)
I created a simple table in SQL Server 2012
Then a new Access db
Then linked the table by importing and choosing the linked option
Right clicking on a row (on the gray to the left), select Copy
Then right click on the gray to the left of new row, then select Paste
Then arrow down
Works just fine, leaving out the id, and incrementing as expected.
So I can't replicate the issue.

Did you put a unique constraint on any of the columns and try to copy a row of data where there was unique data in those columns?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

the #deleted marker is there only as a reminder from Access' ADO or ODBC connector that you attempted to insert something. It's flagged that way to denote that something that was received back from the connection no longer matches what was in memory/local. You could see it if 10 people open the same form, and someone else wiped out a record while you're vewing the list (and any number of other scenarios like that).

It's visual only - doesn't get persisted anywhere.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?