xl sheet Upload and Insert in asp.net c#
aspx:XL Question Upload:
<div>
<asp:Label ID="lblXLUpload" runat="server" Text="XL Upload
Question:"></asp:Label>
<i style="color:Red">(Should be xls format)
</i>
<asp:FileUpload ID="QueUploadXL" runat="server"/>
<asp:RequiredFieldValidator ID="ReqXL" runat="server"
ValidationGroup="XL" ControlToValidate="QueUploadXL"
ErrorMessage="Select XL File"></asp:RequiredFieldValidator>
<asp:Button ID="btnXLUpload" runat="server" Text="XL Data Upload"
ValidationGroup="XL" onclick="btnXLUpload_Click"/>
</div>
-------------------------------------------------------------
aspx:cs
NameSpace:
using System.Data.OleDb;
protected void btnXLUpload_Click(object sender, EventArgs e)
{
try
{
if (Page.IsValid)
{
if (QueUploadXL.HasFile)
{
string FName, Path, OlXL;
FName = QueUploadXL.FileName;
Path = Server.MapPath(".");
string XLPath = Path + "/Image/" + FName;
QueUploadXL.SaveAs(Path + "/Image/" + FName);
string XLConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + XLPath + ";" + "Extended Properties=Excel
8.0;";
OleDbConnection OleConn = new
OleDbConnection(XLConnString);
OleDbCommand OleCmd = new OleDbCommand();
OlXL = "Select * From [Sheet1$]";
OleCmd = new OleDbCommand(OlXL, OleConn);
OleConn.Open();
dt.Load(OleCmd.ExecuteReader());
OleConn.Close();
FileInfo fileinfo = new FileInfo(XLPath);
fileinfo.Delete();
Count = Convert.ToInt16(dt.Rows.Count.ToString());
for (int i = 0; i < Count; i++)
{
try
{
Conn.Open();
Que = dt.Rows[i]["Question"].ToString();
Cate = dt.Rows[i]["Category"].ToString();
OptA = dt.Rows[i]["OptionA"].ToString();
OptB = dt.Rows[i]["OptionB"].ToString();
OptC = dt.Rows[i]["OptionC"].ToString();
OptD = dt.Rows[i]["OptionD"].ToString();
Answer = dt.Rows[i]["Answer"].ToString();
Already Record Check:
C Select = "Select QuestionID From
KnowledgeBowl_Question Where Question='" + Que + "'";
da = new SqlDataAdapter(Select, Conn);
da.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
{
QuesID = ds.Tables[0].Rows[0]["QuestionID"].ToString();
Already Record Check in Delete:
Delete = "Delete From Question Where
QuestionID='" + QuesID + "'";
Cmd = new SqlCommand(Delete, Conn);
Cmd.ExecuteNonQuery();
ds.Clear();
Insert();
}
else
{
Insert();
}
}
catch (Exception Ex)
{
Response.Write(Ex);
}
finally
{
Conn.Close();
}
}
Response.Write("<script>alert('Added Sucessfully!!')
</script>");
}
}
}
catch (Exception Ex)
{
}
}
public void Insert()
{
SqlCommand Cmd = new SqlCommand("SP_Question", Conn);
Cmd.Parameters.Add("@Question", SqlDbType.VarChar).Value =
Que.Trim();
Cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value =
Cate.Trim();
Cmd.Parameters.Add("@OptionA", SqlDbType.VarChar).Value =
OptA.Trim();
Cmd.Parameters.Add("@OptionB", SqlDbType.VarChar).Value =
OptB.Trim();
Cmd.Parameters.Add("@OptionC", SqlDbType.VarChar).Value =
OptC.Trim();
Cmd.Parameters.Add("@OptionD", SqlDbType.VarChar).Value =
OptD.Trim();
Cmd.Parameters.Add("@Answer", SqlDbType.VarChar).Value =
Answer.Trim();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.ExecuteNonQuery();
}
No comments:
Post a Comment