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_REPORTTE ST" ("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["REPO RT_ID"];
keys[0] = column;
ds.Tables[0].PrimaryKey = keys;
OleDbCommandBuilder b = new OleDbCommandBuilder(myAdap ter);
b.RefreshSchema();
try
{
myAdapter.UpdateCommand = b.GetUpdateCommand();
myAdapter.DeleteCommand = b.GetDeleteCommand();
myAdapter.InsertCommand = b.GetInsertCommand();
}
catch (Exception e)
{ }
Your efforts are very appreciated.
{"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_REPORTTE
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["REPO
keys[0] = column;
ds.Tables[0].PrimaryKey = keys;
OleDbCommandBuilder b = new OleDbCommandBuilder(myAdap
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.