Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i want to insert record in the sql server database table.
table structure
Sno int,
name varchar,
companycode int.
i want recognize sno by company code at the response on web request.
like company code 1 is one then sno start from 1,2,3,4...so on.same for another company code.
my problem is when i am requesting table at same time then same no is generated.
i had also tried stored procedure but it fails when multiple request generated at a time.
please help me to resolve this issue.
Posted

Change the table design.
Set the Sno as a primary key that increments for every row - http://technet.microsoft.com/en-us/library/ms189039.aspx[^].

If you do not want Sno to be a primary key, you can still make it increment automatically - http://technet.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx[^].
 
Share this answer
 
Comments
Member 8825505 25-Jan-14 5:40am    
but i have already mentioned i need different sno company wise.
samit kaneriya 25-Jan-14 7:19am    
hi give Procedure code for insert time
Try to do something like this.


SQL
DECLARE @MAX_sno int=null;
SELECT @MAX_sno=MAX(person_id) FROM TABLENAME WHERE companycode='Your_companycode'   ;
SET @MAX_sno=@MAX_sno+1;

----In MAX_sno you will get new sno value which can be used for new record insertion 
 
Share this answer
 
Comments
Member 8825505 1-Feb-14 0:18am    
it not work for me. :-( i try it from multiple terminal computer at ones than it going to fail.
V5709 1-Feb-14 0:31am    
try this.
------------

DECLARE @MAX_sno int=null;
SELECT @MAX_sno=MAX(person_id) FROM TABLENAME WITH (HOLDLOCK) WHERE companycode='Your_companycode' ;
SET @MAX_sno=@MAX_sno+1;

----------------------

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900