ADO Command Close - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

ADO Command Close

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-24 : 16:43:59

Good coding practice - closing an open connection.

DIM cmdSP
SET cmdSP = Server.CreateObject("ADODB.Command")
WITH cmdSP
.ActiveConnection = application("DBaddr")
.CommandText = "dbo.FE_RandomQuiz"
.CommandType = adcmdstoredproc
.Parameters.Append .CreateParameter ("@NextModuleFilename", adVarChar, adParamOutput, 100)
.Execute ,,adExecuteNoRecords
If CheckADOErrors(.ActiveConnection) Then
' Nothing to do
End If
NextModuleFilename = .Parameters("@NextModuleFilename")
End With
cmdSP.Close
SET cmdSP = Nothing

The ADO above works, and returns the output paramter, but the cmdSP.Close returns an error

Object doesn't support this property or method: 'close'

If I write out the state of the command object, it's already closed.

Is this unique to stored procedures that do not return recordsets?

Sam

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-03-24 : 17:43:01

I think you have to close the connection, and not the command object.

Try
cmdSP.ActiveConnection.close
set cmdSp = nothing

BTW, you are storing an ADO connection Object in the Application Variables? I'm pretty sure that's a big no-no. I KNOW it's a no-no to put it in Session, but I'm not 100% on the application variables.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-25 : 22:57:05

Thanks Michael,

Session variables are sent back to the client. Application are resident on the server. I'd figured application variable to be a reliable safe location for the connection object string.

Can anyone can offer a reason or experience with why an application variable would be a bad choice for storing the connection object string?

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-26 : 02:15:16

Sam, I beleive Michael was refering to storing the actual connection (as opposed to just the connection string) inside the application variable. And this is what your code implies:

quote:

DIM cmdSP
SET cmdSP = Server.CreateObject("ADODB.Command")
WITH cmdSP
.ActiveConnection = application("DBaddr")
.CommandText = "dbo.FE_RandomQuiz"
.CommandType = adcmdstoredproc

Storing the connection string inside an application object is perfectly fine, but a non-free-threaded object such as an ADO Connection is a big no-no.

OS

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-12 : 18:31:41

Yes, it's a very bad idea to store the actual connection in the application object. Storing the connection string is fine, but really unnecessary; since it's a constant, why not just include it in an include file? Here's what I do:


---------- db.asp ------------

Const szConnString="Proivider=..."

Function GetDB()
  Dim oDB

  Set oDB=Server.CreateObject("ADODB.Connection")
  On Error Resume Next
  oDB.Open szConnString
  if Err.Number>0 Then
    Response.Write("Error.") ' Other error handling goes here
    Set GetDB=nothing
  Else
    Set GetDB=oDB
  End IF
End Function

...then it's just a matter of using GetDB everywhere it's needed.

As for the command object, it has no "close" method. Just setting it to nothing does the trick. Do be sure that you close both recordsets and connections, and set them both to nothing.

Also, to correct a minor point, session variables are *not* sent back to the client. They are independent per session, but neither application nor session variables are ever sent to the client. Instead, a GUID is sent in a cookie that identifies the session, and the browser supplies that for each page request. Thankfully, as both performance and security would suffer if the contents of the session object were all sent to and from the client.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources