OleDbConnection - ExecuteNonQuery requires an open and available Connection. The connection's current state is closed

Question

What am I doing wrong here?

using System.Data;
using System.Data.OleDb;

namespace myProject.Account
{
    public class DbManager
    {

        private OleDbConnection OpenDbConnection()
        {
            string connectionString = GetConnectionString();
            return new OleDbConnection {ConnectionString = connectionString};
        }

        private string GetConnectionString()
        {
            return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\myDataBase.accdb";
        }

       public void InsertUser(string name, string loginName, string password)
       {
            OleDbConnection conn = OpenDbConnection();

            OleDbCommand command = new OleDbCommand(
                 "INSERT INTO tblUser (UserName, LoginName, Password) VALUES (@name,@login,@pwd)",
                 Conn);

            command.Parameters.Add("@name", OleDbType.VarChar).Value = name;
            command.Parameters.Add("@login", OleDbType.VarChar).Value = loginName;
            command.Parameters.Add("@pwd", OleDbType.VarChar).Value = password;
            command.ExecuteNonQuery();
       }
   }
}

.

I got this error:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
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.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Source Error:

Line 31: command.ExecuteNonQuery();

I have tried to look at some other threads but none have helped:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed

MS Access DB doesnt save changes after execution (C#)

I recommend placing the connection inside a `using` block to free resources. — Uwe Keim, Dec 03 '11 at 21:15
Seems very "dangerous" to me to call a method `OpenDbConnection` when it really only creates the db connection, but **doesn't** actually **open** it! Kind goes against the "Principle of least surprise" .... either actually **open** the connection in that method, or call it `CreateDbConnection` or something.... — marc_s, Dec 03 '11 at 21:24
Thanks Uwe Keim - I'm pretty new in C# For others that are interested in why using "using" see thread: http://stackoverflow.com/questions/75401/uses-of-using-in-c-sharp — Jedi, Dec 04 '11 at 09:41
Hmm.. it seems that I did not read the tutorial correctly. Some examples on the web did not use Conn.Open() when you do the "SELECT" statement. I think it is just bad programming style. So thanks for the help to all you guys :-) — Jedi, Dec 04 '11 at 09:43

dash · Answer 1 · 2011-12-03T22:04:07.583

4

You appear to be forgetting to open your connection (and are assigning the wrong connection to the command). Try:

       using(OleDbConnection conn = OpenDbConnection())
        {
          using(OleDbCommand command = new OleDbCommand( 
               "INSERT INTO tblUser (UserName, LoginName, Password) VALUES (@name,@login,@pwd)")) 
          {
          command.CommandType = CommandType.Text;
          command.Parameters.Add("@name", OleDbType.VarChar).Value = name; 
          command.Parameters.Add("@login", OleDbType.VarChar).Value = loginName; 
          command.Parameters.Add("@pwd", OleDbType.VarChar).Value = password; 
          command.Connection = conn; 

          conn.Open();

          command.ExecuteNonQuery();
          } 
        }

Instead. I'd recommend using the using statement, too. It will manage your connection.close for you.

edited Dec 03 '11 at 22:04

answered Dec 03 '11 at 21:16

dash

89,546
4
51
71

Thanks for the answer. I used the competent_tech, but this one is also a good example :-) – Jedi Dec 04 '11 at 09:47
Be sure to accept their answer then! Personally, I'd change the name of your method to GetDbConnection, and only Open() the connection when I'm about to use it as per the above. I'd also wrap it in a using statement to make sure the connection gets closed and disposed even if there is an exception. – dash Dec 04 '11 at 11:47

score 3 · Answer 2 · answered Dec 03 '11 at 21:13

3

After

OleDbConnection conn = OpenDbConnection();

add

conn.Open();

Alternatively, modify OpenDbConnection as follows:

    private OleDbConnection OpenDbConnection()
    {
        string connectionString = GetConnectionString();

        OleDbConnection conn = new OleDbConnection {ConnectionString = connectionString};

        conn.Open();

        return conn;
    }

answered Dec 03 '11 at 21:13

competent_tech

44,465
11
90
113

After some debugging I figured out that "Password" in my INSERT statement is a reserved word in SQL or in ACCESS. I got a syntax error when the statement was executed. When I changed the "Password" to "UserPwd", the statement went through :-) – Jedi Dec 04 '11 at 09:37
@Jedi: That's great news! Remember, if an answer solved or help you solve your problem, it is helpful to click the checkmark and up arrow next to the question so that future visitors to the question will know this is what solved your issue. – competent_tech Dec 04 '11 at 16:31

OleDbConnection - ExecuteNonQuery requires an open and available Connection. The connection's current state is closed

2 Answers2