private DataTable readexcel()
{
string Path = (Server.MapPath("~/Exam/uploads/ChangeCity.xls")); //For retrive file for export into Database
try
{
DataTable dtExcel = new DataTable();
string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Path + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection con1 = new OleDbConnection(SourceConstr);
string query = "Select * from [Sheet1$]";
OleDbDataAdapter data = new OleDbDataAdapter(query, con1);
data.Fill(dtExcel);
GridSID.DataSource = dtExcel;
GridSID.DataBind();
return(dtExcel);
}
catch (Exception ex)
{
lblmessage.Text = ex.Message;
return(null);
}
finally
{
conLinq.Dispose();
}
}
protected void btnView_Onclick(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
con.Close(); con.Open();
DataTable dtDatas = (DataTable)ViewState["dtDatas"];
SqlDataAdapter ad = new SqlDataAdapter("select distinct ef.SID, st.Name, st.IMID from ExamForm ef, Student st where ef.MarkStatus='Approved' and ef.ExamSession='" + lblSessionHiddend.Text.ToString() + "' and ef.Course='" + ddlCourse.SelectedValue.ToString() + "' and ef.Part='" + ddlPart.SelectedValue.ToString() + "' and ef.SID=st.SID", con);
DataSet ds = new DataSet();
ad.Fill(ds);
GridSID.DataSource = ds;
GridSID.DataBind();
for (int i = 0; i < GridSID.Rows.Count; i++)
{
cmd = new SqlCommand("select CourseID from ExamCurrent where SID='" + GridSID.Rows[i].Cells[0].Text.ToString() + "'", con);
string cid = Convert.ToString(cmd.ExecuteScalar());
fillddl(cid, GridSID.Rows[i].Cells[0].Text.ToString());
int k = 0;
for (int jj = 0; jj < ddlSyllabus.Items.Count; jj++)
{
cmd = new SqlCommand("select SID from SExamMarks where SID='" + GridSID.Rows[i].Cells[0].Text.ToString() + "' and SubID='" + ddlSyllabus.Items[jj].Value.ToString() + "' and Status='Pass' and MarkStatus='Submitted'", con);
string id = Convert.ToString(cmd.ExecuteScalar());
if (id != "")
{
k = k + 1;
}
if (ddlPart.SelectedValue.ToString() == "SectionB")
{
if (k == 10)
{
k = ddlSyllabus.Items.Count;
}
}
}
if (k >= ddlSyllabus.Items.Count)
{
// cmd = new SqlCommand("update SExamMarks set MarkStatus='Approved' where SID='" + GridSID.Rows[i].Cells[0].Text.ToString() + "' and Part='" + ddlPart.SelectedValue.ToString() + "'", con);
// cmd.ExecuteNonQuery();
DataRow drNewRow = dtDatas.NewRow();
drNewRow["SID"] = GridSID.Rows[i].Cells[0].Text.ToString();
drNewRow["Name"] = GridSID.Rows[i].Cells[1].Text.ToString();
drNewRow["Stream"] = lblHiddendStream.Text.ToString();
drNewRow["Course"] = ddlCourse.SelectedValue.ToString();
drNewRow["Part"] = ddlPart.SelectedValue.ToString();
drNewRow["IMID"] = GridSID.Rows[i].Cells[2].Text.ToString();
dtDatas.Rows.Add(drNewRow);
GridExamForms.DataSource = dtDatas;
GridExamForms.DataBind();
}
}
con.Close();
con.Dispose();
btnView.Focus();
}