Article ID: 306388
Article Last Modified on 5/8/2003
APPLIES TO
- Microsoft Visual Basic 6.0 Enterprise Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft SQL Server 2000 Service Pack 3a
- Microsoft SQL Server 2000 Service Pack 1
This article was previously published under Q306388
SYMPTOMS
If you request a static, server-side cursor from a SQL stored procedure, you cannot use MovePrevious or MoveFirst to move backward through the recordset. The cursor exhibits the behavior of a forward-only cursor.
NOTE: This problem only occurs if there are user-defined variables in the stored procedure or SQL commands such as SET NOCOUNT ON.
After you run MovePrevious or MoveFirst, you receive the following error message:
MORE INFORMATION
Steps to Reproduce Behavior
- Create a Data Source Name (DSN) named SQLPUBS that points to the SQL Server Pubs database.
- In the SQL Server Enterprise Manager, modify the ByRoyalty stored procedure. Add SET NOCOUNT ON immediately following the AS portion of the stored procedure, and save the changes.
NOTE: This step is important to reproduce the problem. - Create a new Visual Basic Standard EXE project.
- From the Project menu, click References, and then select the Microsoft Data Access Objects 2.x Library check box.
Paste the following code in the form load event:
Dim rs As ADODB.Recordset Dim comm As ADODB.Command Dim oCon As New ADODB.Connection Dim prm As ADODB.Parameter Dim StrOut As String StrOut = "" oCon.Open "DSN=SQLPUBS;uid=sa;password=Password1;" Set comm = New ADODB.Command Set comm.ActiveConnection = oCon comm.CommandText = "byroyalty" Set prm = comm.CreateParameter("@percentage", adInteger, adParamInput, , 25) comm.Parameters.Append prm Set rs = New ADODB.Recordset 'rs.CursorLocation = adUseClient rs.CursorLocation = adUseServer rs.Open comm, , adOpenStatic, adLockBatchOptimistic 'rs.Open "Select * from authors", oCon, adOpenKeyset, adLockBatchOptimistic Debug.Print "rs.CursorType = " & rs.CursorType rs.MoveNext rs.MovePrevious ' ERROR OCCURS HERE.
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
123008 HOWTO: Set Up ODBC Data Sources When Distributing Apps
171146 HOWTO: Create and Remove a DSN in Visual Basic
306385 PRB: CursorType Returns adOpenStatic When You Request adOpenDynamic from a Server-Side Recordset
Keywords: kbprb kbpending KB306388