?
Solved

The Dreaded Object Required Error

Posted on 2006-11-06
15
Medium Priority
?
484 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 12

Expert Comment

by:Preece
ID: 17885368
Which line do you get the error on?

Preece
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 17885391
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
ID: 17885463
The erroneous line is :

if dbConn.State <> adStateOpen Then

It say that Object Required: dbConn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:SquareHead
ID: 17885543
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
ID: 17885586
No, it gives me the object required error for that also.
0
 
LVL 2

Expert Comment

by:sonicysa
ID: 17885736
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 1500 total points
ID: 17885741
in other words remove this line from the CloseDB() sub.

Set dbConn = Nothing
0
 
LVL 58

Expert Comment

by:amit_g
ID: 17887430
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
ID: 17887619
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
ID: 17889532
Thanks, guys. You've helped out a lot.
0
 
LVL 58

Expert Comment

by:amit_g
ID: 17890566
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
ID: 17892782
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
ID: 17892819
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
ID: 17893842
You learn something new everyday. Thanks again.
0
 
LVL 2

Expert Comment

by:sonicysa
ID: 17894283
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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/…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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