We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

The Dreaded Object Required Error

thedwalker
thedwalker asked
on
Medium Priority
553 Views
Last Modified: 2008-02-01
I am at wits end with the stupid error. I have a couple of pages that are being consolidated into one. They both include a common file that contains the following:


Dim strSQL, dbConn, dbRS, dbRS_2

' Sub routines for opening and closing the database connection.  It
' requires a global variable named dbConn for proper functionality.
'Const adStateOpen = 1
'Const adStateClosed = 0

Sub OpenDB()

      If NOT IsObject(dbRS) Then
            
            Set dbRS = Server.CreateObject("ADODB.Recordset")
            Set dbRS_2 = Server.CreateObject("ADODB.Recordset")

            dbRS.CursorType = 1
            dbRS.LockType = 2

      End If
      
      If NOT IsObject(dbConn) Then
            Set dbConn = Server.CreateObject("ADODB.Connection")
      End If
      'If IsObject(dbConn) then
      '      Response.Write("dbConn is an ADO Connection. It will connect to " & cfg_CONNECTION_STRING)
      'End If
      With dbConn
      If .State <> adStateOpen Then
            .ConnectionString = cfg_CONNECTION_STRING
            .Open
      End If
      end with
End Sub

Sub CloseDB()
      
      ' This sub closes the database connection

      ' Check to see if the either of the recordset variables
      ' are open and close them first if they are.
      If IsObject(dbRS) = True Then
            If dbRS.State = adStateOpen Then
                  dbRS.Close
            End If
            Set dbRS = Nothing
      End If
      
      If IsObject(dbRS_2) = True Then
            If dbRS_2.State = adStateOpen Then
                  dbRS_2.Close
            End If
            Set dbRS_2 = Nothing
      End If

      ' Close the DB connection if it is open and release
      ' the dbConn variable from memory.  Just to be
      ' clean, make sure it exists first
      If IsObject(dbConn) = True Then
            If dbConn.State = adStateOpen Then
                  dbConn.Close
            End If
            Set dbConn = Nothing
      End If

End Sub

Now when the OpenDB subroutine is called the first time it works. Then CloseDB is called. Later OpenDB is called again. That is when I get the Object Required error. I have tried everything I can think of to get this to work. I have looked over the other question on this same topic and none seem to work. So I am wondering can someone please tell me what is wrong?
Comment
Watch Question

Commented:
Which line do you get the error on?

Preece
Hmm, one thing you could do to troubleshoot would be to NOT use the subroutines and instead do all the opening and closing in the code. You may be able to better pinpoint the part that is failing that way...

Author

Commented:
The erroneous line is :

if dbConn.State <> adStateOpen Then

It say that Object Required: dbConn
Can you determine the state of dbConn just before that point?

...
response.write "current state: " & dbConn.State

if dbConn.State <> adStateOpen Then
...

Author

Commented:
No, it gives me the object required error for that also.

Commented:
It doesn't make sense to set the dbconn var/object = nothing when you are going to need to use it again.
Because you state up front that there needs to exist a global object "dbconn" then that means that you cannot destroy it if you are going to call the open again.

connection pooling should take care of the objects existence. I wouldn't even worry about setting it to nothing.
Commented:
in other words remove this line from the CloseDB() sub.

Set dbConn = Nothing

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2006

Commented:
Once the variable is set to an object IsObject would always return true even if the object is later set to Nothing. So do it this way

Dim strSQL, dbConn, dbRS, dbRS_2

'***** Make them objects
Set dbConn = Nothing
Set dbRS = Nothing
Set dbRS_2 = Nothing

' Sub routines for opening and closing the database connection.  It
' requires a global variable named dbConn for proper functionality.
'Const adStateOpen = 1
'Const adStateClosed = 0

Sub OpenDB()

     If dbRS Is Nothing Then
         
          Set dbRS = Server.CreateObject("ADODB.Recordset")
          Set dbRS_2 = Server.CreateObject("ADODB.Recordset")

          dbRS.CursorType = 1
          dbRS.LockType = 2

     End If
     
     If dbConn Is Nothing Then
          Set dbConn = Server.CreateObject("ADODB.Connection")
     End If

     With dbConn
     If .State <> adStateOpen Then
          .ConnectionString = cfg_CONNECTION_STRING
          .Open
     End If
     end with
End Sub

Sub CloseDB()
     
     ' This sub closes the database connection

     ' Check to see if the either of the recordset variables
     ' are open and close them first if they are.
     If Not dbRS Is Nothing Then
          If dbRS.State = adStateOpen Then
               dbRS.Close
          End If
          Set dbRS = Nothing
     End If
     
     If Not dbRS_2 Is Nothing Then
          If dbRS_2.State = adStateOpen Then
               dbRS_2.Close
          End If
          Set dbRS_2 = Nothing
     End If

     ' Close the DB connection if it is open and release
     ' the dbConn variable from memory.  Just to be
     ' clean, make sure it exists first
     If Not dbConn Is Nothing Then
          If dbConn.State = adStateOpen Then
               dbConn.Close
          End If
          Set dbConn = Nothing
     End If

End Sub

Commented:
If the page calls OpenDB multiple times, then it might be better to create the object at the start of the page and discard it only at the end.
Only in cases were you are not certain if you would need the connection object at all, then it makes sense to create the object only if it is required.

Author

Commented:
Thanks, guys. You've helped out a lot.
CERTIFIED EXPERT
Top Expert 2006

Commented:
Even though, your problem is solved by removing

Set dbConn = Nothing

you now have closed connection object sitting in IIS's memory - one per request. This might not be an issue for a low traffic website but if you get moderate traffic and don't want to reboot your production webserver every few days, you should always set all objects to nothing before the page ends. You should always destroy what you create in every page. Sooner or later you will face a much bigger problem (lke IIS crashing or stop responding or heavy memory usage) and that one would be much harder to find and solve.

Author

Commented:
To be honest Amit_g, your answer is the one I meant to accept but I didn't notice until it was too late.  
CERTIFIED EXPERT
Top Expert 2006

Commented:
You can always post a question in http:Community_Support/ with a link to this question and a moderator would help you. You could also accept more than one comment as your solutions by using a split.

Author

Commented:
You learn something new everyday. Thanks again.

Commented:
check out this article on the subject: http://www.15seconds.com/Issue/970531.htm
it states for example: "Sometimes creating and tearing down the connection is more costly then executing the command."
Guess it depends on the connection type you are making though.
it also states: "By default, connection pooling in IIS 3.0 is turned off (set to 0). " but that is an old IIS instance.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.