Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

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.      
0
Sorax
Asked:
Sorax
1 Solution
 
p_davisCommented:
try instead of this
 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;

this:

myAdapter.Fill(ds);
DataColumn[] keys = {ds.Tables[0].Columns["REPORT_ID"]};
ds.Tables[0].PrimaryKey = keys;
0
 
SoraxAuthor Commented:
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="http://www.experts-exchange.com/Microsoft/Development/.NET/Visual_CSharp/Q_22854610.html"]http://www.experts-exchange.com/Microsoft/Development/.NET/Visual_CSharp/Q_22854610.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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now