[mdavies1983]

Hi, I'm currently experiencing an intermittent error when dealing with an oledb connection.

I have multiple forms which all use the same section of code and it works fine one moment but not the next, for example I can load a form which fires the code fine, then select another form which again uses the code without any problems. At some point this changes, I could select the first form again and now I receive an error.

My connection string is set in the app.config as follows:

<connectionStrings>
    <add name="msAccessDB" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\app_data\edb.mdb;Persist Security Info=True;Jet OLEDB:Database Password=somepassword" providerName="System.Data.OleDb" />
</connectionStrings>

My business layer is calling the data access layer with the following (this is just one basic example):

	public static int getUserCount()
	{
	    intUserCount = 0;

            //get record count
            string strSQL = "SELECT COUNT(*) FROM users WHERE active = true;";

            OleDbDataReader dr = GetLocalDataReader(strSQL);
            while (dr.Read())
            {
                userCount = Convert.ToInt32(dr[0]);
            }

	    return userCount;
	}

and my data access layer contains the following:

        public static OleDbDataReader GetLocalDataReader(string cmdSQL)
        {
            OleDbCommand cm = GetLocalCommand(cmdSQL);
            try
            {
                cm.Connection.Open();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            return cm.ExecuteReader(Commandbehavior.CloseConnection);
        }

        #region Private Members

        //these members are not exposed
        private static OleDbCommand GetLocalCommand(string cmdSQL)
        {
            return GetLocalCommand(cmdSQL, GetLocalConnection());
        }

        //an overload of the GetCommand method which allows for reused connection object
        private static OleDbCommand GetLocalCommand(string cmdSQL, OleDbConnection cn)
        {
            return new OleDbCommand(cmdSQL, cn);
        }

        private static OleDbConnection GetLocalConnection()
        {
            string cs = ConfigurationManager.ConnectionStrings["msAccessDB"].ConnectionString;
            return new OleDbConnection(cs);
        }

As I say this works fine most of the time, but then after several calls to the database I get a 'OleDbException: Unspecified error' on

cm.Connection.Open();

Any help will be greatly received.

[Momerath]

According to MS it could be lots of things. Did you do a search on the error? Did you check the inner exception? Do you ever close the old connections?

[mdavies1983]

Thanks for the quick reply, I was simply missing dr.Close() from a couple of the calling methods...

Slightly embarrassed yet relieved at the simplicity of the solution!

[Skydiver]

One way to avoid that problem of forgetting to close is to use the using keyword:

using (OleDbReader reader = GetLocalDataReader(...))
{
    :
}