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.

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

Answers (10)