title: Executing Multiple SQL statements in a single command against SQLServer Database in vb6
body: <p>I am trying to execute multiple SQL statements against a SQL server database using ADODB and vb6.</p>
  
  <p>when I open a recordset the code stops with the following error code:</p>
  
  <p>Run-time error '3704':
  Operation is not allowed when object is closed.</p>
  
  <p>here is my code:</p>
  
  <pre><code>Dim sql As String
  sql = "WITH " &amp; vbCrLf &amp; _
        "q AS" &amp; vbCrLf &amp; _
        "(SELECT Item_No, Unit_OldQuantity" &amp; vbCrLf &amp; _
        "FROM dbo.The_Units),  sequenced AS" &amp; vbCrLf &amp; _
        "(SELECT ROW_NUMBER() OVER (PARTITION BY Item_No ORDER BY Unit_OldQuantity DESC) AS sequence_id,*" &amp; vbCrLf &amp; _
        "From q)" &amp; vbCrLf &amp; _
        "SELECT Item_No , Unit_OldQuantity" &amp; vbCrLf &amp; _
        "into #tmpTable" &amp; vbCrLf &amp; _
        "From sequenced" &amp; vbCrLf &amp; _
        "Where sequence_id = 1" &amp; vbCrLf &amp; _
        "SELECT sum(The_ItemDetails.Item_Cost / #tmpTable.Unit_OldQuantity * The_ItemDetails.Item_Quantity) AS Total" &amp; vbCrLf &amp; _
        "FROM  The_Items INNER JOIN The_ItemDetails ON The_Items.Item_No = The_ItemDetails.Item_No INNER JOIN #tmpTable ON The_ItemDetails.Item_No = #tmpTable.Item_No" &amp; vbCrLf &amp; _
        "Where the_items.Item_kind = 0"
  
  
  Dim connText As String
  connText = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AlmohasebSQL;Data Source=server-pc\SQLEXPRESS"
  Db_Almohaseb.ConnectionString = connText
  Db_Almohaseb.Open
  Dim RS_ItemDetails As New ADODB.Recordset
  RS_ItemDetails.Open sql, Db_Almohaseb, adOpenStatic, adLockOptimistic, adCmdText
  Text1.Text = RS_ItemDetails.RecordCount
  </code></pre>
  
  <p>I can run any other sql statement using the same connection but not this one.
  I can also run this command in .net but not in vb6.
  Right now I just need to know why this is happening, and I am feeling woozy from banging my head against the desk.
  please can someone shed some light on this.</p>
  
  <p>Thanks</p>