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 rst.movefirst.
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 views 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).
Anyway, this used to be the working 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] <> ? 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)
And the recordset handling was as follows:
Do While Not rst.EOF OR rst.BOF
'----Do something with the recordset-----
rst.movenext
loop
rst.MoveFirst
Since the migration to SQL Server, I am getting the error:
Line 110 Rowset position cannot be restarted.. .
Which is on the rst.MoveFirst
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.
So first I tried to set Nocount on in my query:
strSQL = "Set Nocount on "
strSQL = strSQL + "MyQuery"
strSQL = strSQL + " set nocount off"
This didn't had any effect. Secondly I tried to modify my record set like so:
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
This also didn't had any effect.
In the end I just tried to do it without a stored procedure like the following:
strSQL = "MyQuery above"
set rst=conSQL.execute(strSQL)
Though this gave me the error:
Line 106 Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.. .
on the MoveFirst
P.S. the connection is being opened though a sub reference before the stored procedure like so:
OpenSQL conSQL
In this sub the connection is being opened:
Sub OpenSQL(ByRef conSQL)
Set conSQL = Server.CreateObject("ADODB.Connection")
conSQL.connectionTimeout = 60
ConSQL.CommandTimeout = 0
conSQL.Open "my driver / database"
End Sub
So now I am stuck with what to do next. Any ideas how to fix this?
If you can only move forwards in the recordset then you might be opening a forwards-only recordset. The cursor setting affects the manner in which you may traverse the recordset returned. See the following for further information:
Cursor Location: https://msdn.microsoft.com/en-us/library/ms675802(v=vs.85).aspx
Recordset Object: https://msdn.microsoft.com/en-us/library/ms681510(v=vs.85).aspx