none
OraOLEDB.Oracle RRS feed

  • Question

  •  

    This is my code

     

    REM -- set file

    Dim objDa As New OleDb.OleDbDataAdapter("select * from Table where id = 1", cnnXn)

    Dim objCB As New OleDb.OleDbCommandBuilder(objDa)

    Dim objDS As New Data.DataSet

    Dim objRow As Data.DataRow

    objDa.Fill(objDS)

     

    .

    .

    .

    objDa.Update(objDS)

     

    on update i' ve got error

     

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

    primary key is on id column.

     

    Please help!

    Friday, January 18, 2008 2:10 PM

Answers

  • Thank you for your help. I have found the solution; the problem was the connection string. The right one is:

    "Provider=MSDATASHAPE;Data Provider=OraOLEDB.Oracle;Data Source=ORCL;User ID=system;password=password;"

     

    Wednesday, January 23, 2008 7:36 PM

All replies

  • What's the exact error message you received? I'm guessing you posted that error message from memory because it contains a typo and when I plugged it into a search engine no results came back other than this forum.

     

    Friday, January 18, 2008 6:06 PM
  • connection string is "Provider=OraOLEDB.Oracle;Data Source=ORCL;User ID=system;password=password;"


    Dim objDa As New OleDb.OleDbDataAdapter("select * from Table where id= 1", cnnXn)
    Dim objCB As New OleDb.OleDbCommandBuilder(objDa)
    Dim objDS As New Data.DataSet
    Dim objRow As Data.DataRow objDa.Fill(objDS)

    If objDS.Tables(0).Rows.Count = 0 Then 'insert
    REM -- insert
    objRow = objDS.Tables(0).NewRow()
    .
    .
    REM -- add
    objDS.Tables(0).Rows.Add(objRow)
    objDa.Update(objDS)
    else
    REM -- update
    objRow = objDS.Tables(0).Rows(0)
    .
    .
    REM -- update
    objDa.Update(objDS)
    End If

    id column is primary key.
    insert works fine but on update I' ve got error

    System.InvalidOperationException: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at WindowsApplication1.Form1.setFile(Int64 nDocId, XmlDocument xmlDoc) in ..

    Saturday, January 19, 2008 3:01 AM
  • Well, I believe something is wrong with the way your table was created. For example, if you were to have created a table without a primary key I would expect this error (although you mentioned your table has one). Thus, becaues I believe this to be a problem with your DDL statements, I think this is the wrong forum for you. Oracle has a forum that might be more appropriate, the Oracle Provider for OLE DB forum:

     

    http://forums.oracle.com/forums/forum.jspa?forumID=148&start=0

    Wednesday, January 23, 2008 12:40 AM
  • If the issue is not the primary key then I would try using Microsoft's OLEDB Provider for Oracle to see if the problem is resolved. If it is then I would suspect a support issue with respect to the .NET OLEDB library CommandBuilder and Oracle's OLEDB Provider.

     

    If possible you may want to use Microsoft's native .NET library for Oracle instead of OLEDB, or Oracle's .NET Provider (ODP).

     

     

     

    Wednesday, January 23, 2008 2:11 PM
  • Thank you for your help. I have found the solution; the problem was the connection string. The right one is:

    "Provider=MSDATASHAPE;Data Provider=OraOLEDB.Oracle;Data Source=ORCL;User ID=system;password=password;"

     

    Wednesday, January 23, 2008 7:36 PM