Microsoft KB Archive/313544

= BUG: You receive an incorrect number for affected rows when you run a stored procedure that inserts, updates, or deletes =

Article ID: 313544

Article Last Modified on 9/15/2005

-

APPLIES TO


 * Microsoft ODBC for Oracle version 2.7 Build 2.573.7713.2
 * Microsoft Visual Studio .NET 2002 Professional Edition

-



This article was previously published under Q313544



SYMPTOMS
When you run a stored procedure that inserts, updates, or deletes, you receive an incorrect number (-1) for the number of rows that are affected.

This problem also occurs in Microsoft Visual Basic 6.0 applications that use the Microsoft ODBC for Oracle driver.



RESOLUTION
To work around this problem, use the OLE DB managed provider with Microsoft OLE DB Provider for Oracle.

For Visual Basic 6.0 applications that use the ODBC for Oracle driver, use Microsoft ActiveX Data Objects (ADO) with Microsoft OLE DB Provider for Oracle.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  In Oracle SQL*Plus, create the following table: Create table TestProc(ID number(2,0), Col1 Char(50)); insert into Testproc values(1, 'Row1'); insert into testproc values(2, 'Row2');   In SQL*Plus, create the following stored procedure: create procedure testprocproc as begin Update testproc set col1 = 'Row1Updated' where col1 = 'Row1'; end;  If you have not already done so, download and install the ODBC .NET managed provider from the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess

 Start Microsoft Visual Studio .NET, and then create a new Windows Application project in Visual Basic .NET. Add a reference to the System.Data.Odbc namespace in the project.  Add the following statements to the top of the Code window: Imports System.Data.Odbc Imports System.Data.OleDb </li> Drag two Button controls from the toolbox, and then drop these controls onto the default form.</li>  Add the following code after the &quot;Windows Form Designer Generated Code&quot; section: Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim Mycon As New Odbc.OdbcConnection(&quot;Driver={Microsoft ODBC For Oracle};&quot; & _                                            &quot;Server=OracleServer;UID=MyUserID;PWD=MyPwd&quot;) Dim Mycmd As New Odbc.OdbcCommand(&quot;{Call TestProcProc}&quot;) Dim x As Integer

Mycon.Open Mycmd.Connection = Mycon x = Mycmd.ExecuteNonQuery MessageBox.Show(x & &quot; rows affected&quot;) Mycon.Close Mycon = Nothing End Sub

Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load Button1.Text = &quot;ODBC&quot; Button2.Text = &quot;OLEDB&quot; End Sub

Private Sub Button2_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles Button2.Click Dim Mycon As New OleDbConnection(&quot;Provider=MSDAORA.1;Password=MyPwd;&quot; & _                                        &quot;User ID=MyUserID;Data Source=OracleServer&quot;) Dim Mycmd As New OleDbCommand(&quot;{call TestProcProc}&quot;) Dim x As Integer

Mycon.Open Mycmd.Connection = Mycon x = Mycmd.ExecuteNonQuery MessageBox.Show(x & &quot; rows affected&quot;) Mycon.Close Mycon = Nothing End Sub </li> Change the connection strings as necessary to connect your Oracle database.</li> Press the F5 key to run the application.</li> Click ODBC. Notice that -1 is returned instead of 1 for the updated row.</li> Click OLEDB. Notice that 1 is returned as expected.</li></ol>

<div class="references_section">