Pooled Connection Object (ODBC for Access)

Discussion in 'Databases' started by hridpath, Jun 12, 2003.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Hi all,
    I am having difficulty with the creation and persistance of an OdbcConnection object. On my local machine It works fine. but when I post to my site it fails.

    Specifically:

    in my Global.asx page I create a Connection object, open and store it in the Application collection as "conn". Then I just set the local object reference on each page to the Application["conn"]. This works just fine on my local machine but when I try to use the page connection object on the published page it fails with;
    System.NullReferenceException: Object reference not set to an instance of an object</font id="red">

    I am at a loss as to what the aaplication setting are concerning my web site.[8]
     
  2. It's a really bad idea to store objects in the session. I'm guessing the the reason you are getting this error is because IIS has been installed on Discountasp.net. IIS 6.0 "recycles" execution threads and will unceremoniously dump your session after a timeout period. By default, this timeout is 120 minutes but Discountasp.net has set it lower than that probably to conserve system resources.

    The reason it is a bad idea to store objects in session is manifold. First, it consumes a huge amout of system resources. Your site won't be able to support more than a very few users when you start doing that. Second, it locks the application down to one thread if the object is single-threaded (though the ADO objects aren't) and third, you can't be guaranteed to always have your session -- if a site, such as a hosting site, uses multiple machines and has any type of request load-balancer, you will never know which machine your code is executing on. One request may go to machine A on which you created your object and another to machine B which has no knowledge of your object.

    Now, connection pooling is free with ADO. If you use the same connection string to connect to your database every time you will be able to take advantage of this pooling. So simply create the ADO Connection object on each page that needs it and feed it the same connection string. When you call .Open() on it, ADO will search for an existing connection and when you call .Close(), it won't actually close the connection, it will add it back to the pool. You will find no significant difference in performance from storing your connection object in the session -- COM+ even caches your object (sort of...). the majority of the overhead in a connection comes from opening the connection, not creating the connection object.

    Good luck!

    Peter.
     
  3. NOTE: I assumed that you were using classic ASP above. I notice now from your post that you are not, so you can disregard the things about single-threading and object creation, but the rest still applies.
     
  4. Bruce

    Bruce DiscountASP.NET Staff

    System.NullReferenceException: Object reference not set to an instance of an object</font id="red">

    This error usually mean that you are trying to reference some variable that has not been initiatized.

    Try post your code and exact error message (with line number), we may be able to better help you.


    quote:Originally posted by hridpath

    Hi all,
    I am having difficulty with the creation and persistance of an OdbcConnection object. On my local machine It works fine. but when I post to my site it fails.

    Specifically:

    in my Global.asx page I create a Connection object, open and store it in the Application collection as "conn". Then I just set the local object reference on each page to the Application["conn"]. This works just fine on my local machine but when I try to use the page connection object on the published page it fails with;
    System.NullReferenceException: Object reference not set to an instance of an object</font id="red">

    I am at a loss as to what the aaplication setting are concerning my web site.[8]
    </blockquote id="quote"></font id="quote">
     
  5. I have just discovered that the reson I was getting the object reference error is that I am also getting an error when the Application is creating the Connection Object. Thusly, no object to refernece. It appears that my connection string had a typo and my local DSN had default user and pass allowing it to connect. I missed that at first.

    Thank you for your responses [8D]

    quote:Originally posted by bruce

    System.NullReferenceException: Object reference not set to an instance of an object</font id="red">

    This error usually mean that you are trying to reference some variable that has not been initiatized.

    Try post your code and exact error message (with line number), we may be able to better help you.


    quote:Originally posted by hridpath

    Hi all,
    I am having difficulty with the creation and persistance of an OdbcConnection object. On my local machine It works fine. but when I post to my site it fails.

    Specifically:

    in my Global.asx page I create a Connection object, open and store it in the Application collection as "conn". Then I just set the local object reference on each page to the Application["conn"]. This works just fine on my local machine but when I try to use the page connection object on the published page it fails with;
    System.NullReferenceException: Object reference not set to an instance of an object</font id="red">

    I am at a loss as to what the aaplication setting are concerning my web site.[8]
    </blockquote id="quote"></font id="quote">
    </blockquote id="quote"></font id="quote">
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page