Microsoft KB Archive/178044

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

Article ID: 178044

Article Last Modified on 10/31/2003

-

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

-



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



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.



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



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.



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

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" %>

    Document 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: <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= ;PWD= ;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> </li></ol>

<div class="references_section">