Click here to Skip to main content
16,019,983 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    // Connection Establishment to the admin database 
    SqlConnection sqlcAdminConn = new SqlConnection(ConfigurationManager.ConnectionStrings["adminConnectionString"].ConnectionString);
    // variable for Excel Connection
    string connectionString = "";

   
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    // Button Click Event   
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        // Variables to throw Exceptions manually 
        int a,b=100,c=0;    
        // Condition to check File availability
        if (FileUpload1.HasFile)
        {
            // Do not display warnibg when file is available
            lblWarning.Visible = false;
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/App_Data/" + fileName);
            FileUpload1.SaveAs(fileLocation);
            //Check whether file extension is xls or xslx
            try
            {
                // Condition to check File extension/version for 1997-2003
                if (fileExtension == ".xls")
                {
                    // Excel Connection string 
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                    GridBinding();
                }
              
                // Condition to check File extension/version for slsx
                else if (fileExtension == ".xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    GridBinding();
                }
                // File Extension other than xls/xlsx
                else
                {
                    // Exprssion to throw Exception
                    a = b / c;
                }
                //String strConnection = "Data Source=172.16.100.42;Initial Catalog=MySamplesDB;Integrated Security=True";
                sqlcAdminConn.Open();
                //file upload path
                string path = FileUpload1.PostedFile.FileName;
                //Create connection string to Excel work book
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                //Create Connection to Excel work book
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                //Create OleDbCommand to fetch data from Excel [StaffCode],[Date],[FirstIn],[LastOut],[TotalHrsFILO]
                OleDbCommand cmdExcel = new OleDbCommand("Select *  from [Sheet2$] where [Language]='EN' ", excelConnection);

                try
                {   
                    //Reader to load content from Excel sheet
                    OleDbDataReader dReader;
                    excelConnection.Open();
                    dReader = cmdExcel.ExecuteReader();
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlcAdminConn);
                    //Give your Destination table name
                    sqlBulk.DestinationTableName = "admin.dbo.SwipeCardData";
                    sqlBulk.WriteToServer(dReader);
                    excelConnection.Close();
                    lblWarning.Text = "File Uploaded Successfully";
                    lblWarning.Visible = true;

                }
                // if the data is already available
                catch (Exception ex)
                {
                    lblWarning.Text = ex.Message.ToString();
                        //"Data Already Uploaded for the Day";
                    lblWarning.Visible = true;
                }
            }
            // if the file format is other than xls/xlsx
            catch (Exception exFileFormat)
            {
                 lblWarning.Text = "File Compatibility Issue Select xls/xlsx format";
                 lblWarning.Visible = true;
            }
        }
        // If the file is not prasent file upload control
        else
        {
            // warning message to display  if file is not available
            lblWarning.Text = "* Select one Excel File to be Uploaded ";
            lblWarning.Visible = true;
        }
    }

    // Code to bind the grid view if data is available in xlx/xlsx file
    public void GridBinding()
    {
        //Create OleDB Connection and OleDb Command
        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Connection = con;
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
        DataTable dtExcelRecords = new DataTable();
        con.Open();
        DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
        cmd.CommandText = "SELECT * FROM [Sheet2$]";
        dAdapter.SelectCommand = cmd;
        dAdapter.Fill(dtExcelRecords);
        con.Close();
        GridView1.DataSource = dtExcelRecords;
        GridView1.DataBind();
    }
}


/// if i use local host to run and trace , data in file gets uploaded to database
when i access the application using web server its showing the error E_FAIL(0x80004005)


[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 25-Jul-12 20:37pm
v2
Comments
OriginalGriff 26-Jul-12 2:37am    
Where?
ZurdoDev 26-Jul-12 16:52pm    
Most likely a security issue then accessing the file.
Aditya Mangipudi 2-Aug-12 9:44am    
I am pretty sure you have debugged through it, but can you check this part again. Specially, the path is PostedFile.FileName whereas it should be something like complete path.

string path = FileUpload1.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

1 solution

It seems like 64 bit issue, ACE or JET does not support on 64 bit process/programs. Force your program to run 32 bit mode and try.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900