FIX: Microsoft .NET Managed Provider for Oracle Does Not Update Against a Synonym Automatically (329794)

SYMPTOMS

When you use OracleCommandBuilder against an Oracle synonym to try to generate Insert, Update or Delete commands automatically in Microsoft .NET Managed Provider for Oracle, you receive the following System.InvalidOperationException error message:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

RESOLUTION

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem.

To resolve this problem, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

    Date            Version       Size           File name     
   -------------------------------------------------------------------------
   18-Oct-2002     1.0.1087.0    290,816 bytes   System.Data.OracleClient.dll

MORE INFORMATION

Steps to Reproduce the Behavior

Create an Oracle table that is named SynTest_Tab:

CREATE TABLE SynTest_Tab(i int PRIMARY KEY, j int)

Create a synonym for the SynTest_Tab table as follows:
```
CREATE SYNONYM SynTest_Syn for SynTest_Tab
```
Create a new Microsoft Visual Basic .NET Windows Application project. To do this, follow these steps :
1. Start Microsoft Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual Basic Projects, and then under Templates, click Windows Application. By default, Form1 is added to the project.
On the Project menu, click Add Reference, and then set a reference to the System.Data.OracleClient namespace.
Drag a Button control from the toolbox to the form.
Add the following code at the top of the Code window:
```
 using System.Data.OracleClient 
```

Add the following code to the Button1_Click event of Form1:

Dim cn As New OracleConnection("server=MyOraSrv;user id=scott;password=tiger;")
        Dim da As New OracleDataAdapter("select * from SynTest_Syn", cn)
        Dim cmdbldr As New OracleCommandBuilder(da)
        Dim ds As New DataSet()
        Try
            cn.Open()
            da.Fill(ds)
            ds.Tables(0).Rows(0)(1) = 200
            da.Update(ds)
        Catch OraEx As OracleException
            MessageBox.Show(OraEx.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Modify the OracleConnection string as appropriate for your environment.
Press F5 to compile and to run the application.