Prevent Duplicate Database Entry with Javascript, ASP.NET, and AJAX

Question

When a form is being submitted, I need to check if a duplicate already exists. If it does, it stops the form from being submitted and allows the person to edit the fields again. I have never really used ajax before, but I have read that for something like this, it would be the best to use. I also can not use PHP, but can use javascript and asp. Right now, I am able to check the database if duplicate values already exist by doing the following:

var  SQL =  " SELECT COUNT(LeaveID) AS Dup, personalID, StartDate, EndDate, StartTime, EndTime, LeaveType";
     SQL += " FROM onLeave";
     SQL += " WHERE personalID = " + ipersonalID;
     SQL += " GROUP BY personalID, StartDate, EndDate, StartTime, EndTime, LeaveType";
     SQL += " HAVING COUNT(LeaveID) > 1";
     SQL += " ORDER BY StartDate DESC";
var rs = Connection.Execute(SQL);

writeTable(rs)

writetable(rs) just outputs the data into a html table and ipersonalID is the current ID of the person viewing the page. The submit is just a simple input like below:

<input type="button" name="btnSubmit" value="Submit" onclick="submitSkedChange()" id="btnSubmit"/>

Use the right tool for the right job: ALTER TABLE onLeave ADD CONSTAINT uc_leave UNIQUE(personalID, StartDate, EndDate, StartTime, EndTime, LeaveType) — asawyer
– asawyer, Commented Aug 24, 2012 at 18:43

Icarus · Accepted Answer · 2012-08-24 20:33:37Z

2

I agree with the comment from @asawyer. A better approach is to add the unique constraint to the table and let it fail. On code behind you simply catch the Exception and display the error to the user. You can specifically catch the Violation of unique constraint error and act accordingly. For any other error, you'd have to decide what to do.

The above approach has several benefits IMO:

One less DB call.
Should perform better because of above and by not having to make an ajax call
It enforces data integrity at the DB level, which is a good thing.

edited Aug 24, 2012 at 20:33

answered Aug 24, 2012 at 19:03

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Icarus Over a year ago

@asawyer If he uses something similar to your script, yes, it would be violation of unique constraint. If he creates a primary key instead, then it would be a violation of a primary key constraint. I think he gets the point but I updated my answer anyway. Thanks.

Dave Over a year ago

Awesome! Thanks guys for the insight on that!

Collectives™ on Stack Overflow

Prevent Duplicate Database Entry with Javascript, ASP.NET, and AJAX

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related