Excel data needs to be updated in existing SQL table

Question

I am exporting data from excel and show it in gridview and when I click submit, it needs to be updated in existing table. Below is my excel data S.No. RequestID Subdivision No Parcel No CUID Status Remarks 1 5 42 55 55 Open Pending I have successfully exported and show it in Gridview. Now I want to update the same in the existing table(Tablename:ParcelInf). Below is my table detail Table ParcelInf( [RequestID] [int] IDENTITY(1,1) NOT NULL, FormattedRequestID AS ('CUID' + RIGHT('00' + CAST(RequestID AS VARCHAR(10)),10)), [SubdivisionNo] [nvarchar](20) NOT NULL, [ParcelNo] [nvarchar](20) NOT NULL, [Region] [nvarchar](20) NULL, [City] [nvarchar](20) NULL, [Zone] [nvarchar](20) NOT NULL, [CoordinateSystem] [nvarchar](20) NOT NULL, [CUID] [nvarchar](20) NULL, [Status] [nvarchar](20) NULL, [Remarks] [nvarchar](20) NULL, [UpdateDate] [date] NULL, [RequestedBy] [nvarchar] (20) NULL, FOREIGN KEY (RequestID) REFERENCES RequestInf (RequestID) Could you please help me on this.

Alok Saxena · Answer

Hi, If issue resolved by my suggestion, please marked it. Thanks Alok Saxena

Alok Saxena · Answer

Hi, I read the all thread. All are correct the issue in Binding() method. What I feel in this method you are missing the code which fetch data into dtProducts (DataTable). Here in this code you are created a new DataTable and next two lines adding two columns. And very next lines you are using foreach (DataRow DR in dtProducts.Rows). There is a issue. Actually dtProducts is empty . private void BindGrid() { string Constr = null; SqlConnection con; SqlCommand cmd; string UpdateQuery = null; Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; con = new SqlConnection(Constr); DataTable dtProducts = new DataTable("ParcelInf"); dtProducts.Columns.Add("Status"); dtProducts.Columns.Add("Remarks"); foreach (DataRow DR in dtProducts.Rows) { con.Open(); UpdateQuery = "Update ParcelInf Set Status='" + DR["Status"] + "',Remarks='" + DR["Remarks"] + "' where RequestID=" + DR["RequestID"] ; cmd = new SqlCommand(UpdateQuery, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); } } } Regards Alok Saxena

R J · Answer

hi, Try use LinqToExcel (https://code.google.com/p/linqtoexcel/) to retrieve data from Excel to c#object which is more faster and you dont have to have a dependency on OLEDB etc.

Azarudeen Ibn Liyakath Ali · Answer

This is my complete code now using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; using System.Data.Sql; using System.IO; using System.Data.OleDb; using System.Configuration; public partial class Update_Request : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnUpload_Click(object sender, EventArgs e) { string ConStr = ""; string ext = Path.GetExtension(FileUpload1.FileName).ToLower(); string path = Server.MapPath("~/MyFolder/" + FileUpload1.FileName); FileUpload1.SaveAs(path); Label1.Text = FileUpload1.FileName + "\'s Please check the below detail again and Click Submit"; if (ext.Trim() == ".xls") { ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (ext.Trim() == ".xlsx") { ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } string query = "SELECT * FROM [Sheet1$]"; OleDbConnection conn = new OleDbConnection(ConStr); if (conn.State == ConnectionState.Closed) { conn.Open(); } OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); gvExcelFile.DataSource = ds.Tables[0]; gvExcelFile.DataBind(); conn.Close(); } protected void btnDownload_Click(object sender, EventArgs e) { Response.ContentType = "application/vnd.ms-excel"; Response.AppendHeader("Content-Disposition", "attachment; filename=TemplateforRequestUpload.xlsx"); Response.TransmitFile(Server.MapPath("~/MyFolder/TemplateforRequestUpload.xlsx")); Response.End(); } protected void Submit_Click(object sender, EventArgs e) { BindGrid(); Label2.Text = "Successfully Updated"; } private void BindGrid() { string Constr = null; SqlConnection con; SqlCommand cmd; string UpdateQuery = null; Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; con = new SqlConnection(Constr); DataTable dtProducts = new DataTable("ParcelInf"); dtProducts.Columns.Add("Status"); dtProducts.Columns.Add("Remarks"); foreach (DataRow DR in dtProducts.Rows) { con.Open(); UpdateQuery = "Update ParcelInf Set Status='" + DR["Status"] + "',Remarks='" + DR["Remarks"] + "' where RequestID=" + DR["RequestID"] ; cmd = new SqlCommand(UpdateQuery, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); } } } I didnt use sqlbulkcopy with the data table dtproducts. Please let me know where am going wrong

Azarudeen Ibn Liyakath Ali · Answer

I had added before foreach and checked, its not throwing any error but it is not updating the data

Khan Abrar Ahmed · Answer

Hi before foreach please add below code and please check the value in column and then check in the database. DataTable dtProducts = new DataTable("ParcelInf"); dtProducts.Columns.Add("Status"); dtProducts.Columns.Add("Remarks");

Azarudeen Ibn Liyakath Ali · Answer

I am getting in the binding time. Error in foreach statement (Error: dtProducts.Rows Does not exist in current context) foreach (DataRow DR in dtProducts.Rows)

Khan Abrar Ahmed · Answer

Hi, where you are getting this error while updating to database or binding time.

Azarudeen Ibn Liyakath Ali · Answer

I am getting an error in dtProducts.Rows(Error: Does not exist in current context) When I give the following, error disappears DataTable dtProducts = new DataTable("ParcelInf"); dtProducts.Columns.Add("Status"); dtProducts.Columns.Add("Remarks"); Correct me If am wrong?

Khan Abrar Ahmed · Answer

Hi try this code for update private void BindGrid() { string Constr = null; SqlConnection con ; SqlCommand cmd ; string UpdateQuery = null; Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; con = new SqlConnection(Constr); foreach (DataRow DR in dtProducts.Rows) { con.Open(); UpdateQuery = "Update ParcelInf Set Status='"+ DR["Status"]+"',Remarks='"+DR["Remarks"]+"' where RequestID="+DR["Remarks"]; cmd = new SqlCommand(UpdateQuery, con); cmd.ExecuteNonQuery(); cmd.Dispose(); con.Close(); } }

Azarudeen Ibn Liyakath Ali · Answer

If I give the below in SQL, it works fine Update ParcelInf Set Status='Open',Remarks='Pending' where RequestID=1; But still the code which you have provided is not updating in SQL

Kailash Chandra Behera · Answer

Check ur update statement

Azarudeen Ibn Liyakath Ali · Answer

Still it is not giving the output. I dont know where I am going wrong

Kailash Chandra Behera · Answer

sorry i forgot to execute query try this private void BindGrid() { String Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; SqlConnection con = new SqlConnection(Constr); con.Open(); foreach (DataRow DR in dtProducts.Rows) { String UpdateQuery = "Update ParcelInf Set Status='"+ DR["Status"]+"',Remarks='"+DR["Remarks"]+"' where RequestID="+DR["Remarks"]; SqlCommand cmd = new SqlCommand(UpdateQuery, con); cmd.CommandText = UpdateQuery; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); cmd.Connection = con; } con.Close(); }

Azarudeen Ibn Liyakath Ali · Answer

I tried with the above, its loading without any error but the data is not updating in SQL. private void BindGrid() { String Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; SqlConnection con = new SqlConnection(Constr); con.Open(); DataTable dtProducts = new DataTable("ParcelInf"); dtProducts.Columns.Add("Status"); dtProducts.Columns.Add("Remarks"); foreach (DataRow DR in dtProducts.Rows) { String UpdateQuery = "Update ParcelInf Set Status='" + DR["Status"] + "',Remarks='" + DR["Remarks"] + "' where RequestID=" + DR["RequestID"]; SqlCommand cmd = new SqlCommand(UpdateQuery, con); cmd.CommandText = UpdateQuery; cmd.CommandType = CommandType.Text; cmd.Connection = con; } con.Close(); }

Kailash Chandra Behera · Answer

see you r putting your excel data into data table named dtProducts, if yes then replace my code ds.Table[0] to dtProducts private void BindGrid() { String Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; SqlConnection con = new SqlConnection(Constr); con.Open(); foreach (DataRow DR in dtProducts.Rows) { String UpdateQuery = "Update ParcelInf Set Status='"+ DR["Status"]+"',Remarks='"+DR["Remarks"]+"' where RequestID="+DR["Remarks"]; SqlCommand cmd = new SqlCommand(UpdateQuery, con); cmd.CommandText = UpdateQuery; cmd.CommandType = CommandType.Text; cmd.Connection = con; } con.Close(); }

Azarudeen Ibn Liyakath Ali · Answer

Again thanks Kailash It shows an IndexoutofRangeException error foreach(DataRow DR in ds.Tables[0].Rows) ---- (Error: Cannot find table 0)

Kailash Chandra Behera · Answer

Replace your code in the bindgrid function; private void BindGrid() { String Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; SqlConnection con = new SqlConnection(Constr); con.Open(); DataSet ds = new DataSet(); foreach (DataRow DR in ds.Tables[0].Rows) { String UpdateQuery = "Update ParcelInf Set Status='"+ DR["Status"]+"',Remarks='"+DR["Remarks"]+"' where RequestID="+DR["Remarks"]; SqlCommand cmd = new SqlCommand(UpdateQuery, con); cmd.CommandText = UpdateQuery; cmd.CommandType = CommandType.Text; cmd.Connection = con; } con.Close(); } Thanks Kailash

Azarudeen Ibn Liyakath Ali · Answer

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; using System.Data.Sql; using System.IO; using System.Data.OleDb; using System.Configuration; public partial class Update_Request : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnUpload_Click(object sender, EventArgs e) { //Coneection String by default empty string ConStr = ""; //Extantion of the file upload control saving into ext because //there are two types of extation .xls and .xlsx of excel string ext = Path.GetExtension(FileUpload1.FileName).ToLower(); //getting the path of the file string path = Server.MapPath("~/MyFolder/" + FileUpload1.FileName); //saving the file inside the MyFolder of the server FileUpload1.SaveAs(path); Label1.Text = FileUpload1.FileName + "\'s Please check the below detail again and Click Submit"; //checking that extantion is .xls or .xlsx if (ext.Trim() == ".xls") { //connection string for that file which extantion is .xls ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (ext.Trim() == ".xlsx") { //connection string for that file which extantion is .xlsx ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //making query string query = "SELECT * FROM [Sheet1$]"; //Providing connection OleDbConnection conn = new OleDbConnection(ConStr); //checking that connection state is closed or not if closed the //open the connection if (conn.State == ConnectionState.Closed) { conn.Open(); } //create command object OleDbCommand cmd = new OleDbCommand(query, conn); // create a data adapter and get the data into dataadapter OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); //fill the excel data to data set da.Fill(ds); //set data source of the grid view gvExcelFile.DataSource = ds.Tables[0]; //binding the gridview gvExcelFile.DataBind(); //close the connection conn.Close(); } protected void btnDownload_Click(object sender, EventArgs e) { Response.ContentType = "application/vnd.ms-excel"; Response.AppendHeader("Content-Disposition", "attachment; filename=TemplateforRequestUpload.xlsx"); Response.TransmitFile(Server.MapPath("~/MyFolder/TemplateforRequestUpload.xlsx")); Response.End(); } protected void Submit_Click(object sender, EventArgs e) { //ExportRowsToDB(); BindGrid(); Label2.Text = "Successfully Updated"; Response.Redirect("Thankyou.aspx"); } private void BindGrid() { String Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo"; SqlConnection con = new SqlConnection(Constr); con.Open(); String UpdateQuery = "Update ParcelInf Set Status= Status ,Remarks=Remarks where RequestID=Remarks"; SqlCommand cmd = new SqlCommand(UpdateQuery, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); gvExcelFile.DataSource = ds.Tables[0]; gvExcelFile.DataBind(); con.Close(); } I think I m not giving Update query properly in BindGrid. I have attached the excel file also.

Kailash Chandra Behera · Answer

Kindly share your code By

Azarudeen Ibn Liyakath Ali · Answer

Thanks Kailash for your reply. I want to use the update query to update the detail in the existing table. I already tried to using the bulk data private void ExportRowsToDB() { //container for source data DataTable dtProducts = new DataTable("ParcelInf"); //dtProducts.Columns.Add("RequestID"); dtProducts.Columns.Add("SubdivisionNo"); dtProducts.Columns.Add("ParcelNo"); dtProducts.Columns.Add("CUID"); dtProducts.Columns.Add("Status"); dtProducts.Columns.Add("Remarks"); DataRow dr = null; //iterate through each grid and populate source data foreach (GridViewRow gridRow in gvExcelFile.Rows) { dr = dtProducts.NewRow(); //dr["RequestID"] = (gridRow.Cells[1].Text); dr["SubdivisionNo"] = gridRow.Cells[1].Text; dr["ParcelNo"] = gridRow.Cells[2].Text; dr["CUID"] = gridRow.Cells[3].Text; dr["Status"] = gridRow.Cells[4].Text; dr["Remarks"] = gridRow.Cells[5].Text; dtProducts.Rows.Add(dr); } //insert data into destination table CopyData(dtProducts); } public void CopyData(DataTable sourceData) { string destConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; // Set up the bulk copy object. using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.DestinationTableName = "dbo.ParcelInf"; // Guarantee that columns are mapped correctly by // defining the column mappings for the order. //bulkCopy.ColumnMappings.Add("RequestID", "RequestID"); bulkCopy.ColumnMappings.Add("SubdivisionNo", "SubdivisionNo"); bulkCopy.ColumnMappings.Add("ParcelNo", "ParcelNo"); bulkCopy.ColumnMappings.Add("CUID", "CUID"); bulkCopy.ColumnMappings.Add("Status", "Status"); bulkCopy.ColumnMappings.Add("Remarks", "Remarks"); // Write from the source to the destination. bulkCopy.WriteToServer(sourceData); } } Could you please help me how I can use the update query in this case

Kailash Chandra Behera · Answer

Hi Azarudeen, You can use sqlbulkcopy to insert your bulk data (gridview data) into sql, kindly go throw the link will help you http://www.c-sharpcorner.com/UploadFile/87b416/performing-bulk-copy-in-ado-net/ Kindly mark as answer, if this helps you Thanks, Kailash