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

Last reviewed: December 22, 1997
Article ID: Q178044

The information in this article applies to:

Microsoft OLE DB, versions 1.0, 2.0
Active Server Pages
ActiveX Data Objects (ADO)
Microsoft Active Server Pages, versions 1.0, 1.0b
Microsoft Data Access Components, version 1.5
Microsoft Internet Information Server version 3.0
Microsoft ODBC for Oracle, version 2.0 (Build 2.73.7269)

SYMPTOMS

When using Query-Based Updates in ActiveX Data Objects (ADO), such as Visual InterDev's Data Form Wizard, only forward-only cursors are supported. If you use Static, Keyset, or Dynamic cursors and the ADO Update method, you raise 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

Oracle offers limited support for server-side cursors.

RESOLUTION

Use forward-only cursors.

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

     <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=sa;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 colleague.* 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 &nbsp; &nbsp;" & objErr.Source)
     Response.Write("<BR> SQLState &nbsp; &nbsp;" & objErr.SQLState)
     Response.Write("<BR> NativeError &nbsp; &nbsp;" &
     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>

Technology        : kbInetDev
Version           : WINDOWS:1.0,1.5,2.0; WINNT:1.0,1.0b,3.0
Platform          : WINDOWS winnt
Issue type        : kbprb

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 22, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.