Uniqueness between 3 fields

Riddhi Valecha

10y1.1kLearn iOS Programming

Hi all...

I need a logic in SQL Server database end -

I have 4 master tables -

1. EmployeeMaster

1 - Abhineet

2 - Neha

3 - Jaya

etc - 55 employees (1,2,3 - auto increment primary key)

--------

2. SubjectMaster

1 - Java

2 - Project Management

3 - Time Management

4 - Oracle

etc - 100 subjects(1,2,3,4 - auto increment primary key)

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

3. OtherSubjectMasters

1- AJAX

2 -Query String

etc - 90 records (1,2,3... auto increment primary key)

-----------

4. Ouarters -

Q1(April2016-June-2016)

Q2(July2016-September2016)

Q3(October2016-December2016)

Q4(January2017-March2017)

-----------

Now, from front - end, the dropdownlist is binded with SubjectMaster.

Incase, the user wants to insert other subject (not present in SubjectMaster table, then he/she inserts othersubject in OtherSubjectMaster table)

---------

Transaction table -

ID	EmpID	SubjectID	OtherSubjectID	Quarter
1	1 (Abhineet)	1	0	Q3(October2016-December2016)
2	1 (Abhineet)	0	2	Q4(January2017-March2017)

Here, if user enters subject from dropdownlist(subjectmaster table), then "SubjectID" field will have the ID of Subject and OtherSubjectID will have 0.

----

Logic that I need -

I want to check that one employee - is allowed to attend one training in one quarter.

Eg - here now, Abhineet will not be able to attend the training of subject id - 1 in Quarter-3 (Q3).

He may attend training of other subjects in this same quarter - Q3.

How do we get uniqueness between - Employee , Subject and Quarter ??

Please guide....

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Riddhi Valecha10y ago
Hi..

How to check uniquess from stored procedure....

Do you have the query for the same ?

Because, the record gets updated ..

Eg - I select a record from Gridview , and make the EmployeeID, SubjectID, same - still - instead of giving error, it gets updated.

How to stop the updation at this point ?

Please guide and help !
0
Shubham Kumar10y ago
Hi ,

if you have to check the uniqueness of more then a column so you should have to check from back end with stored procedure, Proc make it fast to check
0
Riddhi Valecha10y ago
No no.. This is not what I want...

I have made unique keys for four columns in sql server -

I want that I want to check this uniqueness in asp.net c# (front-end)...

I want to know which approach is better - back-end keys or front-end checking ??
0
Jithil John10y ago
Try something like this
http://www.c-sharpcorner.com/UploadFile/20abe2/how-to-check-user-name-or-email-availability-using-Asp-Net/
0
Riddhi Valecha10y ago
Dear , This is simple 3-tire application..

This is not MVC...
0
Jithil John10y ago
Try remote validation in asp.net mvc
0
Riddhi Valecha10y ago
I suppose, this will not be a good practice to keep a check in the database end , and instead check the uniqueness in front-end.

Do anyone know how to we do this ??

Please explain..
0
Riddhi Valecha10y ago
Hi..

Sorry for this long issue writing.

In back-end - I have put constraint on 3 columns (EmployeeID, SubjectID, LevelID, Quarter) - unique key.

Now, I am inserting bulk data from front-end (using foreach loop) , for each record I need to check and provide necessary user-friendly messages.

How do I do that ?
0
Shubham Kumar10y ago
hi Riddhi, hope you doing well,

Please don't see the whole condition together, divide your problem in chunks from that your problem goes to solve simply.Now come on your problem the problem is you hv to check the condition for every employee should attend one training program in one quarter. ok
just check as like.
1st: is employee attend any training program if yes then who quarter program else do whatever you want
2nd if yes then check which subject if previous subject and current subject are same so not allow
3rd if subject is not same so then allow... and so on
0
Jithil John10y ago
You can use remote validation option in MVC
For example
http://www.c-sharpcorner.com/code/1704/check-username-exist-or-not-using-json-in-mvc.aspx
0
Riddhi Valecha10y ago
Well..

How do I check that from font-end ?

Also, if this is set in back-end, then lines of code will be less...

Can you please guide to do a check from back-end ?
0
Jithil John10y ago
You can check it from the front end itself
0