System.Data.OleDb.OleDbException: 'Cannot open any more tables'

Question

While my program was normally running in Visual Studio 2017 I closed it and reopened it and from that moment I am getting the error message "System.Data.OleDb.OleDbException: 'Cannot open any more tables.'" in the line with bold letters. My code is connected to a Microsoft Access database. Can you help me, please? It is for my thesis and I am in a real need for your help.

private void Grid_Loaded(object sender, RoutedEventArgs e) { label.Content = "Ερώτηση " + Question;

        var DBPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\Database\\Users.mdb";
        conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DBPath);
        conn.Open();

        id = MainWindow.id;
        OleDbDataReader dr1 = null;
        do
        {

            rInt = r.Next(1, 20);
            cmd = new OleDbCommand("SELECT * FROM TestQuestions WHERE Chapter='Eisagogi' AND ID="+rInt+"; ");
            cmd.Connection = conn;
            **dr1 = cmd.ExecuteReader();**

Is [this](https://stackoverflow.com/questions/1807934/ms-access-cant-open-any-more-tables) any use to you at all? Access is a bit vague with it's error messages. — Simon Wilson, Sep 21 '20 at 11:19
You probably need to explicitly close the connection when the app is closed because you may have run out of resources. Actually you should only open the connection when you need it and then close it when done by a `using` clause if possible. — Salik Rafiq, Sep 21 '20 at 11:42

score 0 · Answer 1 · answered Sep 22 '20 at 08:26

Based on your code, I think you are filtering the data from the database by using the radom number.

If you want to use OleDbDataReader to get data from database, I suggest that you use the while(dr1.read()) instead of do.. while.

I modify some code and here is a code example you can refer to.

Code:

            var conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DBPath);
            conn.Open();
            int rInt = 0;
            Random random = new Random();
            rInt = random.Next(1, 20);
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM TestQuestions WHERE Chapter='Eisagogi' AND ID=" + rInt + "; ",conn);
            OleDbDataReader dr1 = cmd.ExecuteReader();
            string result = string.Empty;
            while(dr1.Read())
            {
                result = dr1["Question"].ToString();
                MessageBox.Show(result);
            }
            conn.Close();

score 0 · Accepted Answer · answered Sep 23 '20 at 12:09

I want to thank you all for your solutions but the problem was here

OleDbCommand cmd = new OleDbCommand("SELECT * FROM TestQuestions WHERE Chapter='Eisagogi' AND ID=" + rInt + "; ",conn);

I had changed the name Eisagogi in the database so as a result, the 'do...while' command was never finishing.

System.Data.OleDb.OleDbException: 'Cannot open any more tables'

2 Answers2