How to insert only five values a an ID in SQL and C#

Question

i want to add only five subject marks for each student...how i will restrict an Stduent_ID ?if Stduent_ID=1 and inserted five times then for six entry i should not add...how i will do it in C# + SQL.

Praveen Kumar Sreeram · Answer

You need to call a Stored Procedure from your c# code using ado.net

Vinay Singh · Answer

Hi just try ones this if((select count( Student_ID ) from CourseSemOne where StudentId=121)>6) Begin print'Sorry! You cannot add more than five subject data for a single stduent' End else Begin print 'Insert Query' End

Muhammad Aamir · Answer

string sql = SELECT Count(Student_ID) FROM CourseSemOne where Student_ID='+Student_ID+' Group by Student_ID ; SqlConnection con = new SqlConnection(@Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=CS_DB;Integrated Security=True); con.Open(); SqlCommand cmd = new SqlCommand(sql con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); if (count =0) { obj.insertSemCourseOne(Student_ID Course_Code Course_Title Total_Marks Obtain_Marks Grade Value Cr_Hours Grade_Point GPA CGPA); // DatabaseConnnectionClass.UserMessage(Added); MessageBox.Show(Added Subject); Dataloaddd(); } else { MessageBox.Show(Sorry!For this Student ID = +Student_ID+ You cannot add more than 5 data.); }

Praveen Kumar Sreeram · Answer

Replace 6 with 5 in the the following If(@Counter >= 6) and remove equal to symbol. It should be if(@Counter>5)

Praveen Kumar Sreeram · Answer

Please have a look at the following http://www.c-sharpcorner.com/uploadfile/jitendra1987/sql-server-stored-procedure-with-C-Sharp-net/

Muhammad Aamir · Answer

any example ?

Muhammad Aamir · Answer

little bit i have did this issue like this ....but still it adding more than five value for a single ID.......any idea ? Declare @Counter int Set @Counter=(SELECT Count(Student_ID) as 'StudentCount' FROM CourseSemOne where Student_ID=3 Group by Student_ID ) if(@Counter >= 6) print'Sorry! You cannot add more than five subject data for a single stduent' else print 'Insert Query'

Praveen Kumar Sreeram · Answer

Hi Aamir If I understand correctly You want to pull the Top 5 Subjects based on Marks for each of the students. If would have helped us more if you have provided the Table Schema. I am assuming you are using two tables (1) Student and (2) Marks Basically you need to use Partition By clause for your requirement. Below is the query. With StudentMarks as ( SELECT FirstName LastName Rank() OVER(PARTITION BY StudentId ORDER BY Marks DESC) AS Rank FROM Student inner join Marks on Marks.StudentId=Student.Studentid ORDER BY TerritoryName; ) select * from StudentMarks where Rank I'm sorry I didn't test it. Hopefully It should work :-) Also If you want Top 5 Subjects based on the Sum of all Tests (there could be multiple marks for each subject for each test) then do a Group by on Each subject and Student and then do a Partition By on Each Subject and Student. Note: Implement the logic for this using either C# or Stored Procedure is really complex. Partition By is really a cool feature released in SQL Server 2005 version. Hope it is clear and helped Thanks Prawin

Vinay Singh · Answer

Hi just by adding a simple validation in your SP you can perform the opeartion. just have a look of the query. if((select count(SubjectId) from Student_Table where StudentId=121)>5) Begin //-----Here 0 will describe that student is having 5 subject select 0 as status End else Begin //-------------your insert query--------------- //--------Here 1will describe that student is data have been subject select 1 as status End

Nitin Sontakke · Answer

You can write a scalar sql server function to which you can pass the student id and get the number of records for example: select count(1) from tbl_students where student_id = @student_id if this function returns 5 raise error which can be handled by client.