I want to insert a course to my database table that has StudentId and CourseId fields. and I don't want to insert a duplicate course to each student. I use the below code for this. but if once I insert just one course for each student after running the program it shows to me "This unit has already been registered in the database"
//Avooid Insert Duplicate StudentId data to db
DataSet ds = new DataSet();
SqlCommand CmdDuplicate = new SqlCommand("select * from EDU_Student_Course_Registration where StudentId Like N'%" + LblStudentId.Text + "%'", con);
SqlDataAdapter da = new SqlDataAdapter(CmdDuplicate);
da.Fill(ds);
int j = ds.Tables[0].Rows.Count;
//int s=ds
//Avooid Insert Duplicate CourseId data to db
DataSet ds2 = new DataSet();
SqlCommand CmdDuplicate2 = new SqlCommand("select * from EDU_Student_Course_Registration where CourseId Like N'%" + LblCourseId.Text + "%'", con);
SqlDataAdapter da2 = new SqlDataAdapter(CmdDuplicate2);
da2.Fill(ds2);
int j2 = ds.Tables[0].Rows.Count;
if (j > 0 && j2 > 0)
{
string myStringVariable1 = "This unit has already been registered in the database";
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + myStringVariable1 + "');", true);
ds.Clear();
ds2.Clear();
}
else
{
//// insert the data of [EDU_Student_Course_Registration] Table
string Sqlstr_SSLesson = " Insert Into EDU_Student_Course_Registration (" + " StudentId," + " CourseId," + " CreateDate " + " ) " +
"VALUES ('" + Int32.Parse(LblStudentId.Text) + "' ,'" + Int32.Parse(LblCourseId.Text) + "','" + obj.CreateDateTime() + "')";
sqlcom.CommandText = Sqlstr_SSLesson;
con.Open();
sqlcom.Connection = con;
sqlcom.ExecuteNonQuery();
sqlcom.Connection.Close();
string myStringVariable = "Your information has been successfully recorded";
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + myStringVariable + "');", true);
How I wanna do it right؟!