CODEWITHSUNDEEP
c#oledb
HelpNeeder
HelpNeeder

Reputation: 6480

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

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

Upvotes: 1

Views: 523

Answers (4)

ChrisBD
ChrisBD

Reputation: 9209

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.

Upvotes: 2

Pavel Donchev
Pavel Donchev

Reputation: 1889

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.

Upvotes: 3

fnurglewitz
fnurglewitz

Reputation: 2127

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;

Upvotes: 1

Fischermaen
Fischermaen

Reputation: 12458

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

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

Upvotes: 1

Related Questions