thedwalker
asked on
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 .Connectio n")
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?
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
Set dbRS_2 = Server.CreateObject("ADODB
dbRS.CursorType = 1
dbRS.LockType = 2
End If
If NOT IsObject(dbConn) Then
Set dbConn = Server.CreateObject("ADODB
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?
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...
ASKER
The erroneous line is :
if dbConn.State <> adStateOpen Then
It say that Object Required: dbConn
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
...
...
response.write "current state: " & dbConn.State
if dbConn.State <> adStateOpen Then
...
ASKER
No, it gives me the object required error for that also.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 .Connectio n")
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
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
Set dbRS_2 = Server.CreateObject("ADODB
dbRS.CursorType = 1
dbRS.LockType = 2
End If
If dbConn Is Nothing Then
Set dbConn = Server.CreateObject("ADODB
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
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.
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.
ASKER
Thanks, guys. You've helped out a lot.
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.
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.
ASKER
To be honest Amit_g, your answer is the one I meant to accept but I didn't notice until it was too late.
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.
ASKER
You learn something new everyday. Thanks again.
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.
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.
Preece