Import Data From Excel

Question

jhoop2002 0 Newbie Poster

19 Years Ago

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

.net:c# microsoft:access

5 Contributors
4 Replies
1K Views
4 Years Discussion Span
Latest Post 14 Years Ago Latest Post by ashutoshbajpaij

Dani AI

Generated 10 Months Ago

Short summary and root cause (what went wrong)
As discovered, pointing an OLE DB connection at the FileUpload/HttpPostedFile object (or at the client-side filename) doesn’t work — the OLE DB Excel drivers expect a physical file path (and will try to open/create lock/temp files), while the FileUpload control exposes a stream and the client filename. That is why the provider throws "Cannot update. Database or object is read-only." was right to suggest saving the file first. (learn.microsoft.com)

Practical options and server-side cautions

Save-to-disk + OLE DB (quick, compatible): Save the upload to a secure folder (App_Data or a temp folder) with a unique name, give the IIS app‑pool identity write permission to that folder, use the full physical path in the connection string, then delete the temp file when done. On 64-bit hosts remember: Jet 4.0 is 32‑bit only — either run the app pool in 32‑bit mode or install the ACE driver and use the ACE provider for .xlsx. File/folder permissions matter because the engine creates lock files. (learn.microsoft.com)

Better approach (recommended)
2) Read the upload from the request stream — no temp file, no ACE/JET dependency. Use a stream-capable library (ExcelDataReader for .xls/.xlsx, or EPPlus for .xlsx) and parse FileUpload1.FileContent or FileUpload1.FileBytes directly. This avoids app-pool/driver problems raised by and the incorrect use of PostedFile.FileName suggested by . Be careful with very large files (don’t stash big byte arrays in Session/ViewState on load‑balanced sites). Example (ExcelDataReader):

using (var stream = FileUpload1.PostedFile.InputStream)
using (var reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
{
    var ds = reader.AsDataSet(new ExcelDataSetConfiguration {
        ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true }
    });
    var table = ds.Tables[0];
    foreach (System.Data.DataRow row in table.Rows) {
        // process row[0], row[1], ...
    }
}

ExcelDataReader and EPPlus are well documented and easier to deploy than ACE/JET drivers. If OLE DB must be used, include proper Extended Properties (HDR/IMEX) and ensure filesystem permissions. (github.com)

All 4 Replies

nishithraj 0 Newbie Poster

16 Years Ago

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

Reply to this topic

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.

Ezzaral 2,714 Posting Sage Team Colleague Featured Poster · Answer 1 · 2009-12-04T00:52:05+00:00

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

snehal.deodhar 0 Newbie Poster · Answer 2 · 2011-08-25T11:15:24+00:00

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

ashutoshbajpaij 0 Unverified User · Answer 3 · 2011-12-02T17:21:20+00:00

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\"";
                 }

Import Data From Excel

Dani AI

Recommended Answers Collapse Answers

All 4 Replies

Recommended Answers