title: Handling ADODB connections in classic ASP
body: <p>I'm an ASP.NET C# guy who has to go back to classic ASP, and need some help with it.</p>
  
  <p>First, look at this two functions (I know in VBScript the comments are declared by <code>'</code> and not by <code>//</code> but the syntax highlighter here messes up with <code>'</code>).</p>
  
  <p><strong>First version:</strong></p>
  
  <pre><code>Function DoThing
  
      Dim Connection, Command, Recordset
  
      Set Connection = Server.CreateObject("ADODB.Connection")
      Set Command = Server.CreateObject("ADODB.Command")
      Set Recordset = Server.CreateObject("ADODB.Recordset")
  
      Connection.Open "blah blah blah"
      Command.CommandText = "blah blah blah"
      Recordset.Open Command, Connection
  
      If Recordset.EOF = False Then
  
          While Recordset.EOF = False Then
  
              // Do stuff.
  
              Recordset.MoveNext
  
          WEnd
  
      Else
  
          // Handle error.
  
      End If
  
      Recordset.Close
      Connection.Close
  
      Set Recordset = Nothing
      Set Command = Nothing
      Set Connection = Nothing
  
  End Function
  </code></pre>
  
  <p><strong>Second version:</strong></p>
  
  <pre><code>Function DoAnotherThing
  
      Dim Connection, Command, Recordset
  
      Set Connection = Server.CreateObject("ADODB.Connection")
      Set Command = Server.CreateObject("ADODB.Command")
  
      Connection.Open "blah blah blah"
  
      Command.ActiveConnection = Connection
      Command.CommandText = "blah blah blah"
  
      Set Recordset = Command.Execute
  
      If Recordset.EOF = False Then
  
          While Recordset.EOF = False Then
  
              // Do stuff.
  
              Recordset.MoveNext
  
          WEnd
  
      Else
  
          // Handle error.
  
      End If
  
      Recordset.Close
      Connection.Close
  
      Set Recordset = Nothing
      Set Command = Nothing
      Set Connection = Nothing
  
  End Function
  </code></pre>
  
  <p>Now, let's start with the questions:</p>
  
  <h2>Question #1:</h2>
  
  <p>What's best,</p>
  
  <pre><code>Connection.Open "blah blah blah"
  Command.CommandText = "blah blah blah"
  Recordset.Open Command, Connection
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Connection.Open "blah blah blah"
  
  Command.ActiveConnection = Connection
  Command.CommandText = "blah blah blah"
  
  Set Recordset = Command.Execute
  </code></pre>
  
  <p>?</p>
  
  <h2>Question #2:</h2>
  
  <p>When doing SELECTs should I use disconnected recordsets like</p>
  
  <pre><code>Set Recordset = Command.Execute
  
  Command.ActiveConnection = Nothing
  
  Connection.Close
  
  Set Connection = Nothing
  </code></pre>
  
  <p>?</p>
  
  <h2>Question #3:</h2>
  
  <p>Do I have to call</p>
  
  <pre><code>Recordset.Close
  Connection.Close
  </code></pre>
  
  <p>even though I do</p>
  
  <pre><code>Set Recordset = Nothing
  Set Command = Nothing
  Set Connection = Nothing
  </code></pre>
  
  <p>right below (i.e. the garbage collector when invoked by the <code>Set Stuff= Nothing</code> also <code>.Close</code> them before the destruction)?</p>
  
  <h2>Question #4:</h2>
  
  <p>Do I have to include the</p>
  
  <pre><code>Recordset.Close
  Connection.Close
  
  Set Recordset = Nothing
  Set Command = Nothing
  Set Connection = Nothing
  </code></pre>
  
  <p>stuff even though the function ends there, and these objects goes out of scope, and the garbage collector (should) takes care of them?</p>
  
  <h2>Question #5:</h2>
  
  <p>Do classic ASP have pooled connection, so I can open and close them with every command, or should I pass a common connection object around?</p>
  
  <p><br/></p>
  
  <p>Thanks in advance for your help, Andrea.</p>