Gridview Updating using Button

[asardeen]

I want to export the excel data and show it in gridview.

And I want to update the existing table i sql using Gridview data

Below is my code for exporting from Excel

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();

            conn.Close();

            }

After Exporting it, I want to update the existing table when submit button clicks.

Gridview Ex:

ID  Name  PurchaseNo  Status  Remarks
2   xxx   14256       Open    Pending

SQL Detail:

ID,Name,Custaddress,City,PurchaseNo,Status,Remarks 

I want to update only PurchaseNo,Status,Remarks where ID=xx,Name=x

Could you please help me

[EddyC27]

Hi

If you are making no changes to data after it has been imported from excel, and just want a straight save to sql:

Just before you set the Datasource of the grid, put your data into a Session so it can be used in the click event
Session["data"] = ds.Tables[0];

Then use a foreach loop in the submit buttons click event

DataTable dt = Session["data"] as DataTable;

foreach (DataRow dr in dt.Rows)
{
string s = String.Format("UPDATE SQLTable SET PurchaseNo = '{0}', Status = '{1}',Remarks = '{2}' WHERE ID='{3}' AND Name= '{4}'", dr["PurchasNo"].ToString(),dr["Status"].ToString(),dr["Remarks"].ToString(),dr["ID"].ToString(),dr["Name"].ToString());
 }

you can put the different parameters into seperate strings within the foreach loop if you prefer it that way, then just concatonate them into a single update statment; or Use the above string 's' in an Update statement to SQL database