Error tells me I haven't close the connection, but haven't I?

Question

I must have missed something here. I am trying to create a table but I am getting an error telling me that connection is still open. But where??? I have re-read the code but I can't find where the connection is still open...

Problem lays here: objOleDbConnection.Open()

Error say:

You attempted to open a database that is already opened by user 'Admin' on machine 'machine'. Try again when the database is available.

private void sfdNewFile_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
{
    // Creating a ADOX object needed to create
    // new MS Access file.
    ADOX.Catalog createMSFile = new ADOX.Catalog();
    // Creating an object for a table.
    Table nTable = new Table();

    // Creating an object allowing me connecting to the database.
    OleDbConnection objOleDbConnection = new OleDbConnection();
    objOleDbConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + sfdNewFile.FileName + ";Persist Security Info=False;Mode=12";
    // Creating command object.
    OleDbCommand objOleDbCommand = new OleDbCommand();
    objOleDbCommand.Connection = objOleDbConnection;

    try
    {
        // Created a new MS Access 2007 file with specified path.
        createMSFile.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
            sfdNewFile.FileName);

        objOleDbConnection.Open();
        objOleDbCommand.CommandText = "CREATE TABLE PersonalData (" +
            "[DataID] AUTOINCREMENT NOT NULL PRIMARY KEY ," +
            "[Type] VARCHAR(40) NOT NULL ," +
            "[URL] VARCHAR(40) NOT NULL ," +
            "[SoftwareName] VARCHAR(40) NOT NULL ," +
            "[SerialCode] VARCHAR(40) NOT NULL ," +
            "[UserName] VARCHAR(40) NOT NULL ," +
            "[Password] VARCHAR(40) NOT NULL";

        objOleDbCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        // Displaying any errors that 
        // might have occured.
        MessageBox.Show("Error: " + ex.Message);
    }
    finally
    {
        // It is importnat to release COM object, in this very order
        // otherwise we eill end up with an error.
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(createMSFile);

        // Closing the connection to the database.
        objOleDbConnection.Close();
    }
}

What error **exactly** are you getting? Verbatim. Also... `using` blocks would make this a lot clearer. — Marc Gravell, Nov 25 '11 at 08:19
Could you post the *exact* error, and preferably the stacktrace too? (If not, then at least tell us to which line the error message seems to be referring.) — LukeH, Nov 25 '11 at 08:20

score 3 · Accepted Answer · answered Nov 25 '11 at 08:40

It seems that the ADOX object should close its connection as well. Look at the following sample from Microsoft: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681562(v=vs.85).aspx

Their ADOX object is named cat. They have the following:

cat.ActiveConnection = Nothing

Which will probably translate to:

createMSFile.ActiveConnection = null

In your code (probably in the first Finally block). I think it's the ADOX object that makes the difference, before dispoing it, try to set it's ActiveConnection to null.

This seems great and it closes the connection. I guess I have forgotten to close active connections. — HelpNeeder, Nov 25 '11 at 08:55

score 2 · Answer 2 · answered Nov 25 '11 at 08:41

I must admit that I thought that ADO was really for C++ and VB not .NET technologies.

To quote from here

Caution ADO and ADO MD have not been fully tested in a Microsoft .NET Framework environment. They may cause intermittent issues, especially in service-based applications or in multithreaded applications. The techniques that are discussed in this article should only be used as a temporary measure during migration to ADO.NET. You should only use these techniques after you have conducted complete testing to make sure that there are no compatibility issues. Any issues that are caused by using ADO or ADO MD in this manner are unsupported. For more information, see the following article in the Microsoft Knowledge Base: 840667 You receive unexpected errors when using ADO and ADO MD in a .NET Framework application

On the same page you'll see the following code:

using System;
using ADOX;

namespace ConsoleApplication1
{
    class Class1
    {
        [STAThread]
        static void Main(string[] args)
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();

            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
                   "Data Source=D:\\AccessDB\\NewMDB.mdb;" +
                   "Jet OLEDB:Engine Type=5");

            Console.WriteLine("Database Created Successfully");

            cat = null;

        }
    }
}

I note in yours that you're not using the ADOX object to do anything and instead attempting to use OleDBCommands instead. If you're going to use ADOX then you should be creating the table using the ADOX object.

Well, I couldn't figure out how to create a primary key from ADOX so I used OleDB. I mainly used adox to create my empty file. Then use OleDB to create a table. — HelpNeeder, Nov 25 '11 at 08:46

score 1 · Answer 3 · answered Nov 25 '11 at 08:38

1

You should only open the connection if it is not already opened. Try this:

if (objOleDbConnection.State != ConnectionState.Open)
{
    objOleDbConnection.Open()
}

answered Nov 25 '11 at 08:38

Fischermaen

12,238
2
39
56

Syntax doesn't work. 'ConnectionState.Open' doesn't exist in current state. – HelpNeeder Nov 25 '11 at 08:43
It does, you have to include `using System.Data;` so the `ConnectionState` is known by the compiler. – Fischermaen Nov 25 '11 at 08:44
Ok, well this still doesn't fix my problem. – HelpNeeder Nov 25 '11 at 08:52

score 1 · Answer 4 · answered Nov 25 '11 at 08:40

1

i don't know if this suits you (never used ADOX) but: http://www.pcreview.co.uk/forums/closing-access-file-created-adox-catalogclass-t1384766.html

tldr:

Marshal.ReleaseComObject(cat);
cat = null;

answered Nov 25 '11 at 08:40

fnurglewitz

2,097
14
21

tried to call the garbage collector directly too? that guy on the linked page said to use GC.Collect(); if that wasn't working – fnurglewitz Nov 25 '11 at 08:52

Error tells me I haven't close the connection, but haven't I?

4 Answers4

Linked