3 Replies - 504 Views - Last Post: 02 October 2012 - 09:57 AM Rate Topic: -----

#1 mdavies1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 27-June 12

Intermittent OleDb Problem

Posted 02 October 2012 - 06:56 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Intermittent OleDb Problem

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1012
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: Intermittent OleDb Problem

Posted 02 October 2012 - 07:50 AM

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?
Was This Post Helpful? 0
  • +
  • -

#3 mdavies1983  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 27-June 12

Re: Intermittent OleDb Problem

Posted 02 October 2012 - 08:12 AM

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! :stupid:
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 3621
  • View blog
  • Posts: 11,278
  • Joined: 05-May 12

Re: Intermittent OleDb Problem

Posted 02 October 2012 - 09:57 AM

One way to avoid that problem of forgetting to close is to use the using keyword:
using (OleDbReader reader = GetLocalDataReader(...))
{
    :
}


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1