Article ID: 246636
Article Last Modified on 5/8/2003
APPLIES TO
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft OLE DB Provider for SQL Server 7.0
- Microsoft OLE DB Provider for SQL Server 7.01
- Microsoft Visual Basic 5.0 Learning Edition
- Microsoft Visual Basic 6.0 Learning Edition
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
This article was previously published under Q246636
SYMPTOMS
Using ActiveX Data Objects (ADO) and the Microsoft OLEDB Provider or ODBC Driver for SQL Server, to retrieve a recordset from a stored procedure using Server-side Cursors, returns a Forward-only/Read-only recordset if that stored procedure contains anything other than a single SELECT statement. When the recordset is edited, the following run-time error occurs:
CAUSE
SQL Server cannot provide schema information necessary for updates if the stored procedure contains more than just a SELECT statement.
RESOLUTION
You can use client-side cursors in order to get a read-only Static cursor, which allows scrolling and bookmarks. You will have to manage updates yourself.
MORE INFORMATION
Steps to Reproduce Behavior
Use these steps to reproduce the problem:
Create the following Stored Procedure in SQL Server 7.0 under the Pubs database.
CREATE procedure Test @f int as if @f = 2 begin Select * from authors End
- Start a new Visual Basic Standard EXE project. Form1 is added by default.
- From the Project menu, click References, and then select Microsoft ActiveX Data Objects.
Place the following code in Form1:
Private Sub Form_Load() Dim strSQL As String Dim cn as ADODB.Connection Dim rst As ADODB.Recordset Set cn = New ADODB.Connection Set rst = New ADODB.Recordset cn.ConnectionString = "Provider=SQLOLEDB;Data Source=Server;User ID=UserName;Password=Password;Initial Catalog=pubs" cn.Open strSQL = "exec Test 2" rst.Open strSQL, cn, adOpenKeyset, adLockBatchOptimistic, adCmdText rst("au_lname") = "xxxxx" 'Error Occurs Here rst.UpdateBatch rst.MoveNext End Sub
- Run the Project.
RESULT: This error occurs:
Keywords: kbstoredproc kbprb KB246636