Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 286245 - Last Review: February 16, 2004 - Revision: 3.1
PRB: Error Updating Records Using DB2OLEDB and Server-Side ADO Cursor
This article was previously published under Q286245
When you use an ActiveX Data Objects (ADO) server-side cursor with the Microsoft OLE DB Provider for DB2 (DB2OLEDB) that comes with Host Integration Server or with SNA Server 4.0 SP3 or SP4, the following error may occur when you try to update a recordset:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
You have not included the FOR UPDATE option in your SQL statement. For example, you may have opened the ADO Recordset with a SELECT statement like the following:
SELECT * FROM SCHEMANAME.TABLENAME
To allow updates to an ADO Recordset with the DB2OLEDB provider and a server-side cursor, you must use the FOR UPDATE option; for example:
SELECT * FROM SCHEMANAME.TABLENAME FOR UPDATE
This behavior is by design.
- Starting with SNA Server 4.0 SP3, DB2OLEDB supports updateable Recordset objects either through an ADO client-side cursor or through a server-side cursor. If you are using a client-side cursor, you do not need to use the FOR UPDATE clause in the SELECT statement. However, if you are using a server-side cursor, this clause is required.
- If you use Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) and the Microsoft ODBC Driver for DB2, you do not need to use the FOR UPDATE clause in the SELECT statement.
- The FOR UPDATE clause is only required when you are directly using the DB2OLEDB provider with a server-side ADO cursor and you are trying to update a record.
- FOR UPDATE is not required if you are trying to insert a new record.
- When connecting to DB2 for MVS V4R1, V5R1, or V6R1 and DB2 for OS/400 V3R2, there are further requirements to indicate the columns that you intend to update. For example, to update the COL1_NAME and COL2_NAME columns in the SCHEMANAME.TABLENAME table, the following SQL syntax must be used:
SELECT * FROM SCHEMANAME.TABLENAME FOR UPDATE OF COL1_NAME, COL2_NAME
Steps to Reproduce the Behavior
- Use the DRDA_VB ADO sample that comes with the SNA/Host Integration Server SDK. By default, this sample uses a server-side cursor.
- Run the sample and connect to your DB2.
- Open a Recordset with a SELECT statement that resembles the following:
SELECT * FROM SCHEMANAME.TABLENAME
- Click the Edit button and change some values.
- Click the Update button. You receive the error that is listed in the "Symptoms" section of this article.
For additional information about DB2OLEDB and cursors, click the following article numbers to view the articles in the Microsoft Knowledge Base:
269597Â
(http://kbalertz.com/Feedback.aspx?kbNumber=269597/
)
PRB: Limitations of ADO Client Cursor and Data-Bound Controls with DB2OLEDB
271131Â
(http://kbalertz.com/Feedback.aspx?kbNumber=271131/EN-US/
)
Use the DRDA_VB Sample for DB2OLEDB
218590Â
(http://kbalertz.com/Feedback.aspx?kbNumber=218590/EN-US/
)
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
216810Â
(http://kbalertz.com/Feedback.aspx?kbNumber=216810/EN-US/
)
Creating Packages for Use with ODBC Driver for DB2 and OLE DB Provider for DB2
269626Â
(http://kbalertz.com/Feedback.aspx?kbNumber=269626/EN-US/
)
INFO: Permissions Needed to Create Packages Using DB2OLEDB on DB2 and DB2/400
APPLIES TO
- Microsoft OLE DB Provider for DB2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Host Integration Server 2000 Standard Edition
- Microsoft SNA Server 4.0 Service Pack 3
- Microsoft SNA Server 4.0 Service Pack 4
| kb3rdparty kbdb2 kbdriver kbprb kbprovider KB286245 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate