I didn't close previous DataReader, but where?

Question

I have changed my previous code so I am not using 'using'. It work earlier, and code in different class basically represent the same thing, but its working.

I have stared at it for 2 hours now and I just can't figure out where the problems might be.

I have only one reader but each time I am using DisplayFileContent method I am getting the error: Error: There is already an open DataReader associated with this command which must be closed first.

// May be public so we can display
// content of file from different forms.
public void DisplayFileContent(string filePath)
{
    // Counting all entries.
    int countEntries = 0;

    // Encrypting/Decrypting  data.
    EncryptDecrypt security = new EncryptDecrypt();

    using (OleDbConnection connection = new OleDbConnection())
    {
        connection.ConnectionString =
            "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filePath + ";" +
            "Persist Security Info=False;" +
            "Jet OLEDB:Database Password=" + 
            hashPhrase.ShortHash(storedAuth.Password) + ";";

        using (OleDbCommand command = new OleDbCommand
            ("Select * FROM PersonalData", connection))
        {
            OleDbDataReader read;

            try
            {
                // Open database connection.
                connection.Open();

                // Create a data reader.
                read = command.ExecuteReader();

                // Clearing the textbox before proceeding.
                txtDisplay.Text = string.Empty;

                // Checking if there is any data in the file.
                if (read.HasRows)
                {
                    // Reading information from the file.
                    while (read.Read())
                    {
                        // Count all entries read from the reader.
                        countEntries++;

                        // Reading all values from the file as string.
                        // While each string is encrypted, we must decrypt them.
                        // User name and password is the same as user provided
                        // while authentication.
                        txtDisplay.Text += "=== Entry ID: " + read.GetValue(0) +
                            " ===" + Environment.NewLine;
                        txtDisplay.Text += "Type: " + security.Decrypt
                            (read.GetString(1), storedAuth.Password,
                            storedAuth.UserName) + Environment.NewLine;
                        if (!read.IsDBNull(2))
                            txtDisplay.Text += "URL: " +
                                security.Decrypt(read.GetString(2),
                                storedAuth.Password, storedAuth.UserName) +
                                Environment.NewLine;
                        if (!read.IsDBNull(3))
                            txtDisplay.Text += "Software Name: " +
                                security.Decrypt(read.GetString(3),
                                storedAuth.Password, storedAuth.UserName) +
                                Environment.NewLine;
                        if (!read.IsDBNull(4))
                            txtDisplay.Text += "Serial Code: " +
                                security.Decrypt(read.GetString(4),
                                storedAuth.Password, storedAuth.UserName) +
                                Environment.NewLine;
                        if (!read.IsDBNull(5))
                            txtDisplay.Text += "User Name: " +
                                security.Decrypt(read.GetString(5),
                                storedAuth.Password, storedAuth.UserName) +
                                Environment.NewLine;
                        if (!read.IsDBNull(6))
                            txtDisplay.Text += "Password: " +
                                security.Decrypt(read.GetString(6),
                                storedAuth.Password, storedAuth.UserName) +
                                Environment.NewLine;
                        txtDisplay.Text += Environment.NewLine;
                    }
                }
                else
                {
                    txtDisplay.Text = "There is nothing to display! " +
                        "You must add something before so I can display anything here.";
                }

                // Displaying number of entries in the status bar.
                tsslStatus.Text = "A total of " + countEntries + " entries.";

                // Selecting 0 character to make sure text
                // isn't completly selected.
                txtDisplay.SelectionStart = 0;

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
        }
    }
}

@BoltClock♦: It happens every time the method is called. But I am using it only 2 in my main form. — HelpNeeder, Dec 01 '11 at 23:54
It still display how it suppose to, but it gives me an error message each time I add or delete something from the file. Method is called every time I add/delete things from my program. — HelpNeeder, Dec 01 '11 at 23:56
@mellamokb: That's it! Thanks! I'm not sure when I should be calling this, so I am doing it every time I have similar connection string. Probably a bad idea. — HelpNeeder, Dec 01 '11 at 23:57
`command.ExecuteNonQuery()` is used for `INSERT/UPDATE/DELETE` statements. When you have a `SELECT` statement, you use a reader. It's either/or, never both. — mellamokb, Dec 01 '11 at 23:58

dash · Accepted Answer · 2011-12-02T12:58:16.817

You are calling command.ExecuteNonQuery just before the catch block. You'll need to close your DataReader first.

I'd recommend wrapping the code that utilizes the datareader in a using block anyway:

using(OleDbDatareader read = command.ExecuteReader())
{

   ...

}

As pointed out above, command.ExecuteNonQuery() is for executing commands that you don't expect a return result from. These typically are inserts, updates or deletes, but may also include stored proc calls that do the same, or where you don't care about the returned result

score 3 · Answer 2 · answered Dec 01 '11 at 23:56

3

Before your line

command.ExecuteNonQuery();

you need to:

read.Close();

In addition, it is critical to know that using a connection does NOT automatically close it. Therefore, before the end of your connection using statement, you need a

connection.Close();

answered Dec 01 '11 at 23:56

competent_tech

44,465
11
90
113

1

Technically, yes. But OP shouldn't be calling `command.ExecuteNonQuery()` in the first place with a `SELECT` statement. – mellamokb Dec 01 '11 at 23:59
As soon as the code hits the closing bracket of the connection using statement, it will call dispose. Part of dispose closes the connection - see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx "If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent." – dash Dec 02 '11 at 00:10

Filip Popović · Answer 3 · 2011-12-02T00:03:40.857

1

I see no code to close data reader.

Add finally after catch part:

   finally {
        if (read != null)
        {
            read.Close();
        }
}

EDIT#1: I made a mistake, You should close it before executing next command, so either delete last line (if appropriate) before catch block and add finally block or just add using to read variable.

edited Dec 02 '11 at 00:03

answered Dec 01 '11 at 23:57

Filip Popović

2,637
3
18
18

Well, since I am using 'using', some one pointed out that it will close and dispose things by it self, right? – HelpNeeder Dec 01 '11 at 23:59
@HelpNeeder, yes, either You should use 'using' or Close it. – Filip Popović Dec 02 '11 at 00:00
@HelpNeeder, please consider accepting dash's answer if it solved your problem completely. – Filip Popović Dec 02 '11 at 00:04

I didn't close previous DataReader, but where?

3 Answers3

Linked