i wrote a function in DAL layer which uses the stroreprocedure for returns the integer value but its ahowing an error.
public int restrictionInCourse(int studentID,int CourseID)
{
int id;
SqlConnection con = new SqlConnection(constring);
SqlCommand cmd;
try
{
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spCourseRestrict";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@courseID", CourseID);
cmd.Parameters.Add("@StudentID", studentID);
id = Convert.ToInt32(cmd.ExecuteScalar());
return id;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
my storeprocedure is:
CREATE PROC spCourseRestrict
@StudentID int,
@courseID int
AS
Begin
DECLARE @Status int
SET @Status=0
IF(@courseID=116)
BEGIN
IF Exists(SELECT courseID FROM dbo.CourseRegistrationStd WHERE CourseID=111 and UserID=@studentID)
BEGIN
SET @Status=1
END
END
SELECT @Status
END
GO