Microsoft KB Archive/177186

= PRB: Error 40069 Update Resultset Returned from Stored Procedure =

Article ID: 177186

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q177186



SYMPTOMS
When you are updating a recordset returned from a stored procedure using ClientBatch cursor driver, and you do not specify the source table and the key column, the following errors occur:

Run-time error '40069':

Client Cursor: 39 - One of the modified columns has no source table defined



CAUSE
When you perform a SELECT query or a join to build the resultset using a stored procedure, the source table and key column information is not filled in. This occurs because the ODBC layers cannot parse the SQL query to point back to the table and key column from where the chosen column draws its data.



RESOLUTION
You have to provide SourceTable and KeyColumn properties in code in order to update resultsets returned from the stored procedure using ClientBatch cursor driver.



STATUS
This behavior is by design.



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
 Open the SQL Server 6.5 ISQL utility and change the default database to Pubs.  Paste the following code in the Query window: CREATE PROCEDURE GetInfo AS     Select au_id, au_lname From Authors

 Click Execute Query or press the CTRL+E keys to execute the Transact-SQL code. 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
 Start a new project in Visual Basic and choose "Standard EXE." Form1 is created by default.</li> From the Project menu, select References, and then place a check next to Microsoft Remote Data Object 2.0.</li> Add 2 CommandButtons, Command1, and Command2, to Form1.</li>  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= ;Pwd= " With cn           .CursorDriver = rdUseClientBatch .Connect = strConnect .EstablishConnection rdDriverNoPrompt End With

End Sub

Private Sub Form_Unload(Cancel As Integer) cn.Close End Sub

</li> Start the program or press the F5 key.</li></ol>

<div class="references_section">