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

Sandberg

Does it also happen the first time your method is called? – Paulitapaulk

@BoltClock♦: It happens every time the method is called. But I am using it only 2 in my main form. – Sandberg

Why do you call command.ExecuteNonQuery(); at the end? – Duello

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. – Sandberg

@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. – Sandberg

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. – Duello

Answer 1

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

Studhorse

Answer 2

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();

Porta

Technically, yes. But OP shouldn't be calling command.ExecuteNonQuery() in the first place with a SELECT statement. – Duello

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 msdn.microsoft.com/en-us/library/… "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." – Studhorse

Answer 3

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.

Debus

Well, since I am using 'using', some one pointed out that it will close and dispose things by it self, right? – Sandberg

@HelpNeeder, yes, either You should use 'using' or Close it. – Biddick

@HelpNeeder, please consider accepting dash's answer if it solved your problem completely. – Biddick