update excel using oledb (is primary key possible)
-
Hi, I bind the gridview to excel in disconnected mode. works fine. MAke changes to gridview data. changes happen and reflected in dataset. Try to update the dataset using adapter (to transfer the changes to same excel in connected mode). commandbuilder thorws exception as it is not able to find a primary key in excel sheet. How to do it? public partial class excel_excel_disconnmode : System.Web.UI.Page { OleDbConnection oo; OleDbDataAdapter adp; DataSet ds; protected void Page_Load(object sender, EventArgs e) { oo = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\ASP1\\Book1.xlsx;Extended Properties=Excel 12.0"); adp = new OleDbDataAdapter("select ID from [Sheet1$]", oo); if (!IsPostBack) { ds = new DataSet(); ViewState["ds"] = ds; adp.Fill(ds, "sheet1"); GridView1.DataSource = ds; GridView1.DataBind(); } else { ds = (DataSet)ViewState["ds"]; GridView1.DataSource = ds; } } protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; // turn to edit mode GridView1.DataBind(); } protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { int pos = e.RowIndex; ds.Tables[0].Rows[pos].Delete(); GridView1.DataBind(); } protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { int pos = e.RowIndex; string name = ((TextBox)GridView1.Rows[pos].Cells[0].Controls[0]).Text; ds.Tables[0].Rows[pos][0] = name; GridView1.EditIndex = -1; GridView1.DataBind(); } protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; GridView1.DataBind(); } //to trasnfer the changes from disconnected to connected excel protected void Button1_Click(object sender, EventArgs e) { adp = new OleDbDataAdapter("select ID from [Sheet1$]", oo); OleDbCommandBuilder xx = new OleDbCommandBuilder(adp); adp.Update(ds, "sheet1"); } }
-
Hi, I bind the gridview to excel in disconnected mode. works fine. MAke changes to gridview data. changes happen and reflected in dataset. Try to update the dataset using adapter (to transfer the changes to same excel in connected mode). commandbuilder thorws exception as it is not able to find a primary key in excel sheet. How to do it? public partial class excel_excel_disconnmode : System.Web.UI.Page { OleDbConnection oo; OleDbDataAdapter adp; DataSet ds; protected void Page_Load(object sender, EventArgs e) { oo = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\ASP1\\Book1.xlsx;Extended Properties=Excel 12.0"); adp = new OleDbDataAdapter("select ID from [Sheet1$]", oo); if (!IsPostBack) { ds = new DataSet(); ViewState["ds"] = ds; adp.Fill(ds, "sheet1"); GridView1.DataSource = ds; GridView1.DataBind(); } else { ds = (DataSet)ViewState["ds"]; GridView1.DataSource = ds; } } protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; // turn to edit mode GridView1.DataBind(); } protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { int pos = e.RowIndex; ds.Tables[0].Rows[pos].Delete(); GridView1.DataBind(); } protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { int pos = e.RowIndex; string name = ((TextBox)GridView1.Rows[pos].Cells[0].Controls[0]).Text; ds.Tables[0].Rows[pos][0] = name; GridView1.EditIndex = -1; GridView1.DataBind(); } protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; GridView1.DataBind(); } //to trasnfer the changes from disconnected to connected excel protected void Button1_Click(object sender, EventArgs e) { adp = new OleDbDataAdapter("select ID from [Sheet1$]", oo); OleDbCommandBuilder xx = new OleDbCommandBuilder(adp); adp.Update(ds, "sheet1"); } }
-
make any one of the column as primary key in access table in which u want to make updates...