Import Data From Excel

Hi jhoop,

I've gone through your code.
Problem exists with your connection. That is you are trying to access a posted file. That will be read only by default. Try to save it somewhere and then access it.

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+postedFile+";Extended Properties=Excel 8.0;";

Change the connection string with saved file information.

I am trying to create a web app using C# 2005 where I can upload an excel document, store it in memory and read the contents. I have my code working if the file is saved on the hard drive, but i don't want to have to save the file to the hard drive.

Here is my code where i try to access the posted file from a upload file control. Problem is i get this error but have no idea what to do about it.

Cannot update.  Database or object is read-only. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Cannot update.  Database or object is read-only.

Source Error: 


Line 40: 
Line 41:         oConn.ConnectionString = strConn;
Line 42:         oConn.Open();
Line 43: 
Line 44:         //OleDbDataReader oReader = new OleDbDataReader("SELECT * FROM [" + sList + "$]", oConn);

protected void btnUpload_Click(object sender, EventArgs e)
    {
        HttpPostedFile postedFile = this.FileUpload1.PostedFile;
        string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+postedFile+";Extended Properties=Excel 8.0;";

        string sList = "Sheet1";

        OleDbConnection oConn = new OleDbConnection();

        oConn.ConnectionString = strConn;
        oConn.Open();

        //OleDbDataReader oReader = new OleDbDataReader("SELECT * FROM [" + sList + "$]", oConn);
        OleDbCommand oSelect = new OleDbCommand("SELECT * FROM [" + sList + "$]", oConn);
        oSelect.CommandType = CommandType.Text;
        OleDbDataReader oReader = oSelect.ExecuteReader();
        
        while (oReader.Read())
        {
            MyPeople.Add(new Person(oReader[0].ToString() + " " + oReader[1].ToString(), oReader[2].ToString(), oReader[3].ToString(), oReader[4].ToString()));           
        }

        gv.DataSource = MyPeople;
        gv.DataBind();       
    }

    public People MyPeople
    {
        get
        {
            if (this.ViewState["MyPeople"] == null)
            {
                this.ViewState["MyPeople"] = new People();
            }
            return this.ViewState["MyPeople"] as People;
        }
    }

    [Serializable()]
    public class People : List<Person>
    {
        public People()
            : base()
        {
        }
    }

    [Serializable()]
    public struct Person
    {
        private string m_Name;
        private string m_DOB;
        private string m_Lic;
        private string m_SSN;

        public string Name
        {
            get { return m_Name; }
        }

        public string DOB
        {
            get { return m_DOB; }
        }

        public string Lic
        {
            get { return m_Lic; }
        }

        public string SSN
        {
            get { return m_SSN; }
        }

        public Person(string Name, string DOB, string Lic, string SSN)
        {
            m_Name = Name;
            m_DOB = DOB;
            m_Lic = Lic;
            m_SSN = SSN;
        }
    }

I'm sure that jhoop would have appreciated this information two years ago.

Hi, This code works on 32bit machine but not on 64 bit machine. Do you have any solution for 64 bit machine?

Try this:

if (strFileType.Trim() == ".xls")
                 {
                     strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + [B]postedFile.FileName [/B]+ ";Extended Properties=Excel 8.0;"; 
                     //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + postedFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                 }
                 else if (strFileType.Trim() == ".xlsx")
                 {
                     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + [B]postedFile.FileName [/B]+ ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                 }