update excel sheet dynamically in asp.net

Aug 3, 2011 at 2:49 PM

how to update excel sheet using asp.net.here is my code

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

        String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                   "Data Source=" + Server.MapPath("~/site/Employees/Employees1.xls") + ";" +
                   "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
        try
        {

        Label lb = (Label)SearchGrid.Rows[e.RowIndex].FindControl("Label1");
        TextBox tx1 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox2");
        TextBox tx2 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox4");
        TextBox tx3 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox6");
        TextBox tx4 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox8");
        TextBox tx5 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox10");
        TextBox tx7 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox12");
        TextBox tx8 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox14");
        TextBox tx9 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox16");
        TextBox tx10 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("TextBox18");
        TextBox tx11 = (TextBox)SearchGrid.Rows[e.RowIndex].FindControl("txtempid");
        //SearchGrid.EditIndex = -1;
            OleDbConnection objConn = new OleDbConnection(sConnectionString);
            objConn.Open();
            string sql = "update [Employee$] set Emp_ID ='" + tx11.Text + "',Employee_name='" + tx1.Text + "',Designation='" + tx3.Text + "',Department='" + tx2.Text + "',Company='" + tx4.Text + "',Email_id='" + tx5.Text + "', Phone_no='" + tx7.Text + "',Mobile_no='" + tx8.Text + "',Location='" + tx9.Text + "',Reporting_To='" + tx10.Text + "'where Emp_ID='" + txtSearch.Text + "'";
            //string sql = "UPDATE [Employee$] SET Emp_ID='2436' WHERE Emp_ID='2435'";
            OleDbCommand cmd = new OleDbCommand(sql,objConn);
            //cmd.CommandType = CommandType.Text;
            //cmd.Connection = objConn;
            //cmd.ExecuteNonQuery();
            OleDbDataReader dr;
           
            dr = cmd.ExecuteReader();
            SearchGrid.EditIndex = -1;
            showgrid();
            dr.Close();
            objConn.Close();
            //BindData();
        }
        catch(OleDbException ex)
        {

        }


    }