Error ExecuteNonQuery: Connection property has not been initialized C# (Access)

Question

I have some code like this:

var queryIns = "...";

try
{
    var rowInsert = u.insert(queryIns);
}
catch (Exception)
{
    var msg = "Error";
    MessageBox.Show(msg,...);
}

and my command is:

public int Command(string queryCommand)
{
    using var conn = openConnection(); //private method that returns the connection with connection string
    using OleDbCommand cmd = getCommand(queryCommand, conn); //private method that returns the command with connection and query
    return cmd.ExecuteNonQuery();
}

Finally these are the methods:

private OleDbCommand getCommand(string queryString, OleDbConnection conn)
{
    using var cmd = new OleDbCommand(queryString, conn);
    return cmd;
}

private OleDbConnection openConnection()
{
    using var conn= new OleDbConnection(connString);
    conn.Open();
    return conn;
}

The problem is that all this throws an exception

ExecuteNonQuery: Connection property has not been initialized

Could someone help me T_T?

The problem is that you have `using var conn` in that method and it will close the connection at the end of the method meaning it returns a closed connection. Instead you should keep the code that creates the connection and does the query together in one method. — juharr, Nov 30 '21 at 19:17
Simplify your life: https://stackoverflow.com/questions/69712612/how-can-i-update-database-after-changes-in-datagridview — Caius Jard, Nov 30 '21 at 19:38

score 0 · Answer 1 · answered Nov 30 '21 at 19:24

Using will close everything you use with using, connection, command, httprequest, etc.

 using (var conn= new OleDbConnection(connString)) {
       using (var cmd = new OleDbCommand(queryString, conn)) {
              conn.Open();
               return cmd.ExecuteNonQuery();
        } //private method that returns the command with connection and query
  }; //private method that returns the connection with connection string

Dmitry Bychenko · Accepted Answer · 2021-11-30T19:53:41.177

You use disposed (closed with all resources released) connection:

private OleDbConnection openConnection()
{
    // you create the connection
    using var conn= new OleDbConnection(connString); 
    conn.Open();
    return conn;
}                                                    // <- and here you dispose it

The very same problem with Command: the cmd you return is disposed.

private OleDbCommand getCommand(string queryString, OleDbConnection conn)
{
    using var cmd = new OleDbCommand(queryString, conn); // <- command created 
    return cmd;
}                                                        // <- and disposed

You can drop using in both methods:

private OleDbConnection openConnection()
{
    // we create connection
    conn = new OleDbConnection(connString); 

    try { 
      // try it to open  
      conn.Open(); 
      // return opened (and not disposed!) connection
      return conn;
    }
    catch {
      // on open failure we dispose conenction (to prevent resources leakage) 
      conn.Dispose();
      // and rethrow the exception (to know the exact problem)
      throw; 
    }
} 

// Here we just return a command
private OleDbCommand getCommand(string queryString, OleDbConnection conn) =>
  new OleDbCommand(queryString, conn);

Or may be merge methods into one:

public int Command(string queryCommand) 
{
  using var conn = new OleDbConnection(connString); 
  conn.Open();

  using var cmd = new OleDbCommand(queryString, conn);

  return cmd.ExecuteNonQuery();
} // here both conn and cmd will be disposed

ok it worked, many thanks. So the problem was in the using in the getCommand() method, right? — galidan, Nov 30 '21 at 20:10
@galidan: the problem was in `using` *misusing*: `using` disposed both connection and comamnd. It is `Command` method that should `Dispose` connection and command — Dmitry Bychenko, Nov 30 '21 at 20:12

Error ExecuteNonQuery: Connection property has not been initialized C# (Access)

2 Answers2