Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

The Dreaded Object Required Error

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
thedwalker
Asked:
thedwalker
  • 5
  • 3
  • 3
  • +3
1 Solution
 
PreeceCommented:
Which line do you get the error on?

Preece
0
 
SquareHeadCommented:
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
 
thedwalkerAuthor Commented:
The erroneous line is :

if dbConn.State <> adStateOpen Then

It say that Object Required: dbConn
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SquareHeadCommented:
Can you determine the state of dbConn just before that point?

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

if dbConn.State <> adStateOpen Then
...
0
 
thedwalkerAuthor Commented:
No, it gives me the object required error for that also.
0
 
sonicysaCommented:
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
 
sonicysaCommented:
in other words remove this line from the CloseDB() sub.

Set dbConn = Nothing
0
 
amit_gCommented:
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
 
ksbhatCommented:
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
 
thedwalkerAuthor Commented:
Thanks, guys. You've helped out a lot.
0
 
amit_gCommented:
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
 
thedwalkerAuthor 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.  
0
 
amit_gCommented:
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
 
thedwalkerAuthor Commented:
You learn something new everyday. Thanks again.
0
 
sonicysaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now