0

I have an attendance that is not supposed to check a user in more than once so before I check in a user I run the following query: 

Cmd.CommandText = "SELECT * FROM [Attendance] 
WHERE [EID] = @EID AND 
[Location] = @loc AND 
[StormEvent] = @storm AND 
(
 (DATEDIFF(hh, [CheckIn], @checkIn)< 24) AND 
 (DATEDIFF(hh, [CheckIn], @checkIn)> -1) AND
 [CheckOut] Is NULL
)"

Most time it works but I find that when I have several users checking in employees at once I get duplicate records. How can I avoid this? I'm using transactions but that doesn't seem to do the trick.

Gratzy
  • 9,164
  • 4
  • 30
  • 45
Dayomat
  • 1
  • Set transaction isolation as serializable. – Bhasyakarulu Kottakota Sep 19 '14 at 18:50
  • The select appears to be ascertaining if they have records in the past 24, but how are you doing the insert/update? Here's [an example](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) of a transaction, but may not fit your scenario – Jimmy Smith Sep 19 '14 at 18:52
  • 6
    You shouldn't do this with querying before, because between the time you query and the time you insert, another user could do the insert. It's best to have a UNIQUE constraint on what makes the record unique; that way the database enforces the rule. Then you can go ahead and do the INSERT without the pre-check. If the INSERT works you're all set. If there's a duplicate, the INSERT will throw and you can catch the exception and report a friendly "user is already checked in" message. – Ed Gibbs Sep 19 '14 at 18:58

0 Answers0