title: Keep getting errors on recordset.Movefirst since SQL migration - ASP Classic
body: <p>We are moving an ASP classic application with its old MS Access 2000 database to a more recent language in phases. We just finished up migrasting all the Access data into a SQL Server 2012 database. Most things seem to work except for the following: the <code>rst.movefirst</code>. </p>
  
  <p>The code on which it fails is some sort of special case. This part of the application, where the SQL statement was in, always ran on a SQL Server before, though some of the data it queried in the SQL Server database also used <code>views</code> through the access linked server in the SQL Server database (so the SQL Server had views which were from the Access database). The only thing what was changed with the SQL Server migration was the fact that those views, which were used, changed from the linked access server to regular SQL Server views (which are in the same server now).</p>
  
  <p>Anyway, this used to be the working code:</p>
  
  <pre><code>Set spSQL = Server.CreateObject("ADODB.Command")
  Set spSQL.ActiveConnection=conSQL
  
  strSQL = "SELECT [tblMMMemos].[EindDatum], [tblMMMemos].[Ontvanger], [tblMMMemos].[Name], [tblMMMemos].[Contact], [tblMMMemos].[Onderwerp], [vwOPSRelaties].[RelatieNaam] FROM [tblMMMemos] INNER JOIN [vwOPSRelaties] ON CONVERT (INT, Substring([tblMMMemos].[recode], 2, Len([tblMMMemos].[recode]))) = [vwOPSRelaties].[RelatieNummer] WHERE [tblMMMemos].[Status] &lt;&gt; ? ORDER BY (CASE WHEN [tblMMMemos].[Name] = ? THEN 1 ELSE 2 END), [tblMMMemos].[Einddatum]"
  
  spSQL.commandtext= strSQL
  
  spSQL.Parameters.Append spSQL.CreateParameter("@Status", adVarWChar,adParamInput,10)
  spSQL.Parameters.Append spSQL.CreateParameter("@Name", adVarWChar,adParamInput,50)
  
  spSQL.Parameters("@Status").Value = "Closed"
  spSQL.Parameters("@Name").Value = "SYSTEM"
  
  set rst=spSQL.execute(strSQL)
  </code></pre>
  
  <p>And the recordset handling was as follows:</p>
  
  <pre><code>Do While Not rst.EOF OR rst.BOF
  
  '----Do something with the recordset-----
  
   rst.movenext
  
   loop
  
   rst.MoveFirst
  </code></pre>
  
  <p>Since the migration to SQL Server, I am getting the error: </p>
  
  <blockquote>
    <p>Line 110 Rowset position cannot be restarted.. .</p>
  </blockquote>
  
  <p>Which is on the <code>rst.MoveFirst</code> </p>
  
  <p>So I start reading and found out it can be due to multiple problems.
  So I started to adjust the code a bit with some answers on the net on the same problem.</p>
  
  <p>So first I tried to set Nocount on in my query:</p>
  
  <pre><code>strSQL = "Set Nocount on "  
  strSQL = strSQL + "MyQuery"
  strSQL = strSQL + " set nocount off"
  </code></pre>
  
  <p>This didn't had any effect. Secondly I tried to modify my record set like so:</p>
  
  <pre><code>Set cmd = Server.CreateObject("ADODB.Command")
  Set cmd.ActiveConnection = conSQL
        With cmd
          .Commandtext = strSQL
          .CommandType = adCmdStoredProc
          .Parameters.Append .CreateParameter("@Status", adVarWChar,adParamInput,10)
          .Parameters.Append .CreateParameter("@Name", adVarWChar,adParamInput,50)
          .Parameters("@Status").Value = "Closed"
          .Parameters("@Name").Value = "SYSTEM"
        End With
  
        rst.Open cmd, conSQL, adOpenStatic, adLockBatchOptimistic
  </code></pre>
  
  <p>This also didn't had any effect.</p>
  
  <p>In the end I just tried to do it without a stored procedure like the following:</p>
  
  <pre><code>strSQL = "MyQuery above"  
  set rst=conSQL.execute(strSQL)
  </code></pre>
  
  <p>Though this gave me the error:</p>
  
  <blockquote>
    <p>Line 106 Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.. .</p>
  </blockquote>
  
  <p>on the <code>MoveFirst</code></p>
  
  <p>P.S. the connection is being opened though a sub reference before the stored procedure like so:</p>
  
  <pre><code>OpenSQL conSQL
  </code></pre>
  
  <p>In this sub the connection is being opened:</p>
  
  <pre><code>Sub OpenSQL(ByRef conSQL)
  
  Set conSQL = Server.CreateObject("ADODB.Connection")
      conSQL.connectionTimeout = 60
      ConSQL.CommandTimeout = 0
      conSQL.Open "my driver / database"
  
  End Sub
  </code></pre>
  
  <p>So now I am stuck with what to do next. Any ideas how to fix this? </p>