
	Search KbAlertz Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.

Click here to create a
FREE account

Already have an account?
[Click here to Login]

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

Ads by Lake Quincy Media

Article ID: 178044 - Last Review: October 31, 2003 - Revision: 3.0

FIX: Oracle Errors When Using Query-Based Updates Through ADO

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q178044

SYMPTOMS

When using Query-Based Updates against Oracle databases within ActiveX Data Objects (ADO), such as Visual InterDev's Data Form Wizard, only forward-only cursors work without error. If you use Static, Keyset, or Dynamic cursors and the ADO Update method, you receive the following error:

Microsoft][ODBC driver for Oracle]Degree of derived table does not match
column list
Source Microsoft OLE DB Provider for ODBC Drivers
SQLState 21S02
NativeError 0

Back to the top

CAUSE

A bug in the Microsoft Oracle ODBC driver version 2.0 caused any fields with quotes around them to fail with the error shown in the SYMPTOMS section. Internally, ADO was putting quotes around each field name when using Static, Keyset, or Dynamic cursor types.

Back to the top

RESOLUTION

Use forward-only cursors, or obtain the Microsoft ODBC for Oracle Driver build 2.573.2927 or later. You can download the latest version of Microsoft ODBC for Oracle as part of Microsoft Data Access Components from the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess (http://msdn.microsoft.com/dataaccess)

Back to the top

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. Microsoft has confirmed that this is a problem in Microsoft ODBC Driver for Oracle, version 2.573.2927 or later.

Back to the top

MORE INFORMATION

The example below demonstrates the effect of using different cursors on Oracle and SQL Server tables.

Back to the top

To run the sample

Create an .asp file named "cursor_test" in one of your Webs.

Copy and Paste the ASP/HTML code below into the file created in the previous step.

Note The reader must point to his or her own database servers and modify the connect string to use the correct userid (UID) and password (PWD) arguments.

     <%@ LANGUAGE="VBSCRIPT" %>

     <HTML>
     <HEAD>
     <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">
     <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-
     1">
     <TITLE>Document Title</TITLE>
     </HEAD>
     <BODY>
     <%
     On Error Resume Next
     intCursorType=Request.Form("intCursorType")
     strServer=Request.Form("radServer")

     If intCursorType="" Then%>
     <FORM ACTION="cursor_test.asp" METHOD=POST>
     Select a cursor type:<br>
     <SELECT NAME="intCursorType" SIZE="4">
     <OPTION VALUE=0 SELECTED>Forward Only
     <OPTION VALUE=1>Keyset
     <OPTION VALUE=2>Dynamic
     <OPTION VALUE=3>Static
     </SELECT>
     <P>
     <INPUT TYPE="RADIO" NAME="radServer" VALUE="oracle" CHECKED>Oracle
     <INPUT TYPE="RADIO" NAME="radServer" VALUE="sql">SQL Server
     <P>
     <INPUT TYPE=SUBMIT VALUE="Test">
     </FORM>
     <%Else
     Set objConn = Server.CreateObject("ADODB.Connection")
     If strServer="oracle" Then
     objConn.Open "DRIVER={Microsoft ODBC for
     Oracle};UID=you;PWD=me;SERVER=dseOracle;ConnectString=dseOracle;"
     Else
     objConn.Open
     "DSN=LocalServer;SERVER=(local);UID=<username>;PWD=<strong password>;APP=Microsoft (R)
     Developer
     Studio;WSID=MAGISTER;DATABASE=pubs;OemToAnsi=Yes"
     End If
     Set cmdTemp = Server.CreateObject("ADODB.Command")
     Set objRst = Server.CreateObject("ADODB.Recordset")
     cmdTemp.CommandText = "SELECT * FROM colleague"
     cmdTemp.CommandType = 1
     Set cmdTemp.ActiveConnection = objConn
     objRst.CursorType=intCursorType
     objRst.Open cmdTemp, , , 3
     Response.Write("You selected a cursortype of " & intCursorType
     &";<BR>")
     Response.Write(strServer & " used cursortype=" & objRst.CursorType)

     Randomize
     pk=cint(Rnd()*100)
     Response.Write "<P>Adding record with pk of " & pk
     objRst.AddNew
     objRst(0)=pk
     objRst(1)="michael"
     objRst(2)="corning"
     objRst(3)=intCursorType
     objRst.Update

     If objConn.Errors.Count>0 Then%>
     <P>
     <HR>
     A table with the following definition returns the error below when a
     query-based update is attempted against <%=strServer%>
     with cursortype = <%=objRst.CursorType%>.
     <P>
     <FONT COLOR=GREEN>
     <PRE>
     CREATE TABLE colleague
     (
     id               INTEGER,
     firstname      VARCHAR2(35),
     lastname         VARCHAR2(35),
     address         VARCHAR2(33),
     PRIMARY KEY(id)
     )
     </PRE>

     </FONT>
     <%For Each objErr in objConn.Errors
     Response.Write(objErr.Description)
     Response.Write("<BR> Source &#xa0; &#xa0;" & objErr.Source)
     Response.Write("<BR> SQLState &#xa0; &#xa0;" & objErr.SQLState)
     Response.Write("<BR> NativeError &#xa0; &#xa0;" &
     objErr.NativeError)
     Response.Write("<HR>")
     Next
     Else
     Response.Write("<P>Record added successfully.")
     End If
     objRst.Close
     Set objRst=Nothing
     Set cmdTemp=Nothing
     objConn.Close
     Set objConn=Nothing
     End If%>
     </BODY>
     </HTML>

Back to the top

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

190934Â (http://kbalertz.com/Feedback.aspx?kbNumber=190934/EN-US/ ) FIX: Degree of Derived Table Error When Updating Oracle Data

190475Â (http://kbalertz.com/Feedback.aspx?kbNumber=190475/EN-US/ ) INFO: Understanding Microsoft's Oracle ODBC Driver Versions

Back to the top

APPLIES TO

Microsoft OLE DB 2.0
Microsoft ActiveX Data Objects 2.7
Microsoft Data Access Components 1.5
Microsoft Open Database Connectivity 2.0

Back to the top

kbbug kbdatabase kbdriver kbfix kboracle KB178044

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top

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

Ads by Lake Quincy Media