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

Muhammad Aamir

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.

10 Replies

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

Praveen Sreeram10y ago
Accepted answer
You need to call a Stored Procedure from your c# code using ado.net
0
Vinay Singh10y ago
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
0
Muhammad Aamir10y ago
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 <5 && 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.");
}
0
Praveen Sreeram10y ago
Replace 6 with 5 in the the following If(@Counter >= 6) and remove equal to symbol. It should be if(@Counter>5)
0
Praveen Sreeram10y ago
Please have a look at the following http://www.c-sharpcorner.com/uploadfile/jitendra1987/sql-server-stored-procedure-with-C-Sharp-net/
0
Muhammad Aamir10y ago
any example ?
0
Muhammad Aamir10y ago
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'
0
Praveen Sreeram10y ago
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 <=5
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
0
Vinay Singh10y ago
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
0
Nitin Sontakke10y ago
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.
0