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 NOT NULL,
[ParcelNo] nvarchar NOT NULL,
[Region] nvarchar NULL,
[City] nvarchar NULL,
[Zone] nvarchar NOT NULL,
[CoordinateSystem] nvarchar NOT NULL,
[CUID] nvarchar NULL,
[Status] nvarchar NULL,
[Remarks] nvarchar NULL,
[UpdateDate] [date] NULL,
[RequestedBy] [nvarchar] (20) NULL,
FOREIGN KEY (RequestID) REFERENCES RequestInf (RequestID)
Could you please help me on this.
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();
}
}
}
