asp.net - avoid duplicate entries, not key field -

i have table, has 2 columns

key -> primary key, auto incremented reqnumber -> custom generated value 

the reqnumber structured year/month/day/number, number number of times request made day. example, if 3 requests made today , 2 yesterday, table have

key    reqnumber 1      2013/07/16/001 2      2013/07/16/002 3      2013/07/17/001 4      2013/07/17/002 5      2013/07/17/003 

the problem i'm having sometime multiple users save @ same time. proc save, first checks how many created today , adds 1 that, above see 3 created today next 1 4. after insert.

but if 2 users hit save @ same time, both 4, means when insert happens 2013/07/17/004 twice.

is there way avoid this, either in sql or .net? locks way, slow performance correct?

if want application able scale, you're going want create new table:

create table daycounter (     lastreset datetime not null,     nextvalue int not null ) 

then, you'll need function can next available value @ will:

create function nextcounter()     returns char(3) begin     begin transaction         declare @lastreset datetime         declare @nextvalue int          select @lastreset = lastreset daycounter         if (datepart(day, dateadd(day, 1, @lastreset)) = datepart(day, getdate())             update daycounter set lastreset = getdate(), nextvalue = 1          select @nextvalue = nextvalue daycounter         update daycounter set nextvalue = nextvalue + 1     commit transaction      return right('000' + cast(@nextvalue char), 3) end 

so can build update statement strings value, it's safe collisions and manages resetting daily.