Want to block user to insert row after getting max number from column of table

Question

0.00/5 (No votes)

See more:

Hi,

I have a table student and have a column ID (int ) , now i want to insert rows by adding ID+1 in table.

I got max id by MAX(ID)+1 when i insert one row and so on.Issue happen when multiple user get the same ID when they execute same query at same time and insert the same ID to table.

Posted 9-Oct-15 1:20am

Shrikant_Yadav

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anupam Shukla 2009 · Answer 1 · 2015-10-09T01:46:00

Solution 1

To prevent this situation you should set INDEITY=true for your column and get the value by using SCOPE_IDENTITY() after insertion of record. This way you can prevent duplicate value insertion.

Posted 9-Oct-15 1:46am

Anupam Shukla 2009

Comments

Shrikant_Yadav 9-Oct-15 7:52am

what would happen if column type is nvarchar? With nvarchar column i have vlaue "ABC001" and I am getting max value like MAX(RIGHT(ID,3)) +1 will be 002 and same would happen to other users ?

Anupam Shukla 2009 9-Oct-15 8:45am

First of all as per database normalization recommendation you should not create a column with multi valued dependency (string + max(Id)). You can create a view to use column with combination or store the string part in separate table (depends upon situation).

But if it is compulsory to store values in such way (string + max(Id)) you can use TRANSACTION with TRANSACTION ISOLATION LEVEL = READ COMMITTED. This way you can prevent to read old ID value while other write operation is in progress and you will always get final (committed) value.

Please explain your scenario so I can understand the issue exactly.