Link to home
Start Free TrialLog in
Avatar of Sorax
Sorax

asked on

OleDbDataAdapter not populating PrimaryKey with Oracle backend.

I'm using an OleDbDataAdapter with an Oracle backend and am receiving the following error when updating:
{"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."}

I believe I understand the cause of this error but the table does have a PRIMARY KEY defined. I've attempted to explicitly define the PrimaryKey with unchanged results. I also read that including ROWID in the SELECT statement will act as a PRIMARY KEY but that didn't work.
Below is the table creation SQL and the code using the DataAdapter. How can I resolve this error?

CREATE TABLE "PPMS_SYSTEM"."TU_REPORTTEST" ("REPORT_ID" NUMBER(10) DEFAULT 0
    NOT NULL, "TITLE" VARCHAR2(100 BYTE) DEFAULT ' ',
    "DEFINITION" LONG RAW, "CALENDARMODE" NUMBER(3) DEFAULT 0,
    "GROUPNAME" VARCHAR2(50 BYTE) DEFAULT ' ', "OWNER_ID"
    NUMBER(10) DEFAULT 0, "ACTIVE" NUMBER(2) DEFAULT 0,
    "MODIFIEDBY" NUMBER(10) DEFAULT 0, "MODIFIEDON" DATE DEFAULT
    SYSDATE, "CREATEDBY" NUMBER(10) DEFAULT 0, "CREATEDON" DATE
    DEFAULT SYSDATE, "CREATIONGUID" VARCHAR2(36 BYTE) DEFAULT '                 ',
    CONSTRAINT "TU_REPORT_PK_1" PRIMARY KEY("REPORT_ID"))

      string SQL;
      DataSet ds;
      OleDbDataAdapter myAdapter;

      SQL = "SELECT REPORT_ID, DEFINITION, TITLE, CALENDARMODE, GROUPNAME, OWNER_ID, CREATIONGUID FROM TU_REPORT WHERE REPORT_ID = 182"
      if (oleDbCon.State != ConnectionState.Open)
            oleDbCon.Open();
      myAdapter = new OleDbDataAdapter();
      OleDbCommand myCommand = new OleDbCommand(SQL, oleDbCon);
      myCommand.CommandType = CommandType.Text;
      myAdapter.SelectCommand = myCommand;

      myAdapter.Fill(ds);
      DataColumn[] keys = new DataColumn[1];
      DataColumn column = new DataColumn(); ;

      column = ds.Tables[0].Columns["REPORT_ID"];
      keys[0] = column;
      ds.Tables[0].PrimaryKey = keys;

      OleDbCommandBuilder b = new OleDbCommandBuilder(myAdapter);
      b.RefreshSchema();
      try
      {                    
            myAdapter.UpdateCommand = b.GetUpdateCommand();
            myAdapter.DeleteCommand = b.GetDeleteCommand();
            myAdapter.InsertCommand = b.GetInsertCommand();
      }
      catch (Exception e)
      { }
      
Your efforts are very appreciated.      
ASKER CERTIFIED SOLUTION
Avatar of p_davis
p_davis

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sorax
Sorax

ASKER

Sorry for the delayed response and thanks for the suggestion but it didn't work. I'm suspicious the problem is caused by the provider, based off this thread: [URL="https://www.experts-exchange.com/questions/22854610/error-using-OracleCommandBuilder-Dynamic-SQL-generation-for-the-UpdateCommand-is-not-supported.html"]https://www.experts-exchange.com/questions/22854610/error-using-OracleCommandBuilder-Dynamic-SQL-generation-for-the-UpdateCommand-is-not-supported.html[/URL].
I rewrote the code using an OleDbCommand with an OleDbParameter for the BLOB column(why I was using the DataAdapter in the first place) and it worked well.