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

    }


}

Recommended Answers

All 6 Replies

Have you ever though of user the Excep Interop? I used this years ago to read and write from an Excel file.

I know that doesn't help to much now, but I will try and find my code for that when I get home tonight after work.

However, if you choose to use this way, I could offer just a thought. Have you thought of incrementing though each row of the DataGrid? And updating a record at time? (probably using your ID as a where statement).

Is this what you were asking? Or did my half awake mind totally miss it? (LOL)

Hi AngelofD4rkness,

Thanks for your reply.

It is fine, If it is incrementing though each row of the dataGrid.

Or

It is fine, If it is directly exporting from Excel to Sql and updating the existing table.

Could you pls help me.

Okay need a quick refresher, so you mentioned those two things work. So then what exactly is the problem again? (help a tired mind here LOL)

It is not updating in sql

Quick question. I looked over your code, your BindGrid(), I see it's updating SQL, or at least should. But what I can't see what are you update the data with?

You foreach through that newly created dtProducts DataTable, but you create it, add columns to it and then try to run update statements. But I don't see you every populating the DataTable with any values (which I assume is intended to be containing the data from your dataGrid).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.