PRB: Error 40069 Update Resultset Returned from Stored Procedure (177186)

MORE INFORMATION

In addition to steps to reproduce the errors, the following sample code demonstrates how to update resultsets returned from a stored procedure successfully using Client Batch cursor driver. The stored procedure, GetInfo, is created in SQL Server Pubs database, which retrieves information from the Authors table.

Command1 updates the resultset returned from the stored procedure without the SourceTable and KeyColumn information; therefore, errors occur. Command2 demonstrates how a successful update is done by providing the source table and key column information.

Step 1: Create the Stored Procedure

1. Open the SQL Server 6.5 ISQL utility and change the default database to Pubs.
2. Paste the following code in the Query window:

         CREATE PROCEDURE GetInfo
         AS
         Select au_id, au_lname From Authors
   
   						

3. Click Execute Query or press the CTRL+E keys to execute the Transact-SQL code.
4. The following confirmation message appears:
   
   "This command did not return data, and it did not return any rows"
   
   This indicates that the Transact-SQL code created the stored procedure correctly.

Step 2: Create the Visual Basic Code

1. Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default.
2. From the Project menu, select References, and then place a check next to Microsoft Remote Data Object 2.0.
3. Add 2 CommandButtons, Command1, and Command2, to Form1.
4. Paste the following code in the General Declarations section of Form1.
   
   Note You must change your Server, Uid, and Pwd parameters in the RDO connection string. Make sure that Uid has the appropriate permissions to perform this operation on the database.

         Dim cn As New rdoConnection
   
         Private Sub Command1_Click()
   
            Dim qy As New rdoQuery
            Dim rs As rdoResultset
   
            With qy
               Set .ActiveConnection = cn
               .SQL = "{Call GetInfo}"
            End With
            Set rs = qy.OpenResultset(rdOpenKeyset, rdConcurRowVer)
            rs.Edit
            rs!au_lname = "Weeks"
            rs.Update
            Debug.Print rs!au_lname
            rs.Close
            qy.Close
   
         End Sub
   
         Private Sub Command2_Click()
   
            Dim qy As New rdoQuery
            Dim rs As rdoResultset
   
            With qy
               Set .ActiveConnection = cn
               .SQL = "{Call GetInfo}"
            End With
            Set rs = qy.OpenResultset(rdOpenKeyset, rdConcurRowVer)
            rs("au_id").SourceTable = "Authors"
            rs("au_lname").SourceTable = "Authors"
            rs("au_id").KeyColumn = True
   
            rs.Edit
            rs!au_lname = "Weeks"
            rs.Update
            Debug.Print rs!au_lname
            rs.Close
            qy.Close
   
         End Sub
   
         Private Sub Form_Load()
   
            Dim strConnect As String
            Dim strSQL As String
   
            strConnect = "Driver={SQL Server}; _
            Server=MyServer;Database=Pubs;Uid=<username>;Pwd=<strong password>"
            With cn
               .CursorDriver = rdUseClientBatch
               .Connect = strConnect
               .EstablishConnection rdDriverNoPrompt
            End With
   
         End Sub
   
         Private Sub Form_Unload(Cancel As Integer)
            cn.Close
         End Sub
   
   						

5. Start the program or press the F5 key.