OleDbException was unhandled; System resource exceeded

Question

public void LoadExcel_Click(object sender, EventArgs e)
{
    OpenFileDialog fileDLG = new OpenFileDialog();
    fileDLG.Title = "Open Excel File";
    fileDLG.Filter = "Excel Files|*.xls;*.xlsx";
    fileDLG.InitialDirectory = @"C:\Users\...\Desktop\";

    if (fileDLG.ShowDialog() == DialogResult.OK)
    {
        string filename = System.IO.Path.GetFileName(fileDLG.FileName);
        string path = System.IO.Path.GetDirectoryName(fileDLG.FileName);
        excelLocationTB.Text = @path + "\\" + filename;
        string ExcelFile = @excelLocationTB.Text;
        if (!File.Exists(ExcelFile))
            MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));

        OleDbConnection theConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0;");
        theConnection.Open();
        OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);
        DataSet DS = new DataSet();
        theDataAdapter.Fill(DS, "ExcelInfo");
        dataGridView1.DataSource = DS.Tables["ExcelInfo"];
        formatDataGrid();
        MessageBox.Show("Excel File Loaded");
        toolStripProgressBar1.Value += 0;
    }
}

Ok so I got this code off of Microsoft.

theDataAdapter.Fill(DS, "ExcelInfo");

This is the line that gave me the error.

Basically this code is supposed to use a dialog box to open the file and display it on the form. Whenever I opened an Excel file, it would give me this error. I even tried creating a blank excel file and it still gave me this.

I am facing similar issue, just want to share an observation. When I open the excel file, click Enable editing and hit save again. The same file is read without the error. — Mr.Hunt, Mar 11 '14 at 09:58

Biby Augustine · Answer 1 · 2014-01-14T05:10:31.900

1

Modified your code. added OleDbCommand to do the query selection. just try.

OleDbConnection theConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Projects\Demo\Demo.xls;Extended Properties=Excel 8.0;");
        theConnection.Open();
        OleDbCommand theCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", theConnection);
        OleDbDataAdapter theDataAdapter = new OleDbDataAdapter(theCmd);
        DataSet DS = new DataSet();
        theDataAdapter.Fill(DS);
        theConnection.Close();

edited Jan 14 '14 at 05:10

answered Jan 14 '14 at 04:07

Biby Augustine

425
1
3
16

did you checked your connection? is it configured properly? – Biby Augustine Jan 14 '14 at 04:11
How do you check the connection? – puretppc Jan 14 '14 at 04:12
i updated the connection string in above code.. please check using that connection string... :) – Biby Augustine Jan 14 '14 at 04:16
`theDataAdapter.Fill(DS, "ExcelInfo");` is still giving this error. I copied this edited code just now :( – puretppc Jan 14 '14 at 04:27
hi there... i used "Microsoft.Jet.OLEDB.4.0" as provider and "Extended Properties=Excel 8.0", its working perfectly for me... just try again using new connection string please..... :) – Biby Augustine Jan 14 '14 at 04:56
Well I don't know why it's not working for me. It said Excel does not exist in current context. I suppose you mean "Excel" (with quotes) so I changed it. Then I ran it again but it's not working. My version is VS 2012. I'll try it on another computer maybe tomorrow. – puretppc Jan 14 '14 at 05:04
i added my excel in my project root directory and provided connection string like this... "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Projects\Demo\Demo.xls;Extended Properties=Excel 8.0;" just try providing datasource manually like above...... and i also modified coding for just import excel data to dataset..... :) – Biby Augustine Jan 14 '14 at 05:07
Aww it's still giving me the same error. I even tested it on another machine. Oh well I'll give you upvote for trying. – puretppc Jan 14 '14 at 21:31

score 0 · Answer 2 · answered Oct 10 '19 at 13:10

I have seen this error before and it might not have anything to do with your code. The code below works fine, but if you are getting your source that has blocked the file, make sure you right-click the file and go to properties and check unblock. This could be if you are downloading the file from some source etc. A good test is to just open the exported excel file and save it and try again. Or copy the contents into a new excel file.

Again the code below works fine, but when I tried to import without unblocking or going into the file and saving it I was getting the same error. The error message is deceiving.

string excelconString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""", filePath);
string excelQuery = "select col1 from [Sheet1$]";

DataSet ds = new DataSet();
DataTable dt = new DataTable();

using (var excelConn = new OleDbConnection(excelconString))
{
    excelConn.Open();
    using (var oda = new OleDbDataAdapter(excelQuery, excelConn))
    {
        oda.Fill(ds);
        dt = ds.Tables[0];
    }
}

OleDbException was unhandled; System resource exceeded

2 Answers2