Solved

The Dreaded Object Required Error

Posted on 2006-11-06
15
470 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?
0
Comment
Question by:thedwalker
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 12

Expert Comment

by:Preece
Comment Utility
Which line do you get the error on?

Preece
0
 
LVL 18

Expert Comment

by:SquareHead
Comment Utility
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...
0
 
LVL 1

Author Comment

by:thedwalker
Comment Utility
The erroneous line is :

if dbConn.State <> adStateOpen Then

It say that Object Required: dbConn
0
 
LVL 18

Expert Comment

by:SquareHead
Comment Utility
Can you determine the state of dbConn just before that point?

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

if dbConn.State <> adStateOpen Then
...
0
 
LVL 1

Author Comment

by:thedwalker
Comment Utility
No, it gives me the object required error for that also.
0
 
LVL 2

Expert Comment

by:sonicysa
Comment Utility
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.
0
 
LVL 2

Accepted Solution

by:
sonicysa earned 500 total points
Comment Utility
in other words remove this line from the CloseDB() sub.

Set dbConn = Nothing
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 58

Expert Comment

by:amit_g
Comment Utility
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

0
 
LVL 6

Expert Comment

by:ksbhat
Comment Utility
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.
0
 
LVL 1

Author Comment

by:thedwalker
Comment Utility
Thanks, guys. You've helped out a lot.
0
 
LVL 58

Expert Comment

by:amit_g
Comment Utility
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.
0
 
LVL 1

Author Comment

by:thedwalker
Comment Utility
To be honest Amit_g, your answer is the one I meant to accept but I didn't notice until it was too late.  
0
 
LVL 58

Expert Comment

by:amit_g
Comment Utility
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.
0
 
LVL 1

Author Comment

by:thedwalker
Comment Utility
You learn something new everyday. Thanks again.
0
 
LVL 2

Expert Comment

by:sonicysa
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now