locked
how to update access database in server with unique data from uploaded excel file and display updated records in a gridview RRS feed

  • Question

  • User-1160955539 posted

    Hello, I am writing a code for uploading an excel file and displaying the same in gridview using ado.net. But don't know how to update the access database with unique data from gridview , which is not already present in database, simultaneously with the update. My code is as follows:

    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Configuration;

    public partial class _Default : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
    if (FileUpload1.HasFile)
    {
    string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

    string FilePath = Server.MapPath(FolderPath + FileName);
    FileUpload1.SaveAs(FilePath);
    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
    }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
    string conStr = "";
    switch (Extension)
    {
    case ".xls": //Excel 97-03
    conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
    break;
    case ".xlsx": //Excel 07
    conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
    break;
    }
    conStr = String.Format(conStr, FilePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;

    //Get the name of First Sheet
    connExcel.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    connExcel.Close();

    //Read Data from First Sheet
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();

    //Bind Data to GridView
    GridView1.Caption = Path.GetFileName(FilePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    }
    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
    string FileName = GridView1.Caption;
    string Extension = Path.GetExtension(FileName);
    string FilePath = Server.MapPath(FolderPath + FileName);

    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
    }

    If anyone can provide code to simultaneously modify the access database, that will be of much help. 

      

    Saturday, January 10, 2015 5:40 AM

Answers

  • User1711366110 posted

    Hi Vishesh,
       As per your case, the following link which may guide you to get a solution :

    1. Read & Import Excel data into Dataset/Datatable. Then fill into GridView
           http://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx 

    2. Update datatable into MS Access Table
           http://stackoverflow.com/questions/20998803/c-sharp-datatable-update-access-database
       

     Disclaimer:
         This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Hopefully this will be helpful for you.
    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 12, 2015 4:30 AM