Dynamic SQL Generation is not supported against a SelectCommand that does not return and base table information

[Dave White]

I am getting the exception in the subject line whenever I try to execute the
following code:
string sqlMAD = string.Format("SELECT ARCHIVE_ID, DICTIONARY_ID, OWNER_ID,
NAME, DATA_TYPE, DESCRIPTION "
+ "FROM MERAK_ATTRIBUTE_DICT "
+ "WHERE DICTIONARY_ID = {0} "
+ " AND ARCHIVE_ID = 0 ",
oDictionary.ID.ToString());

OleDbDataAdapter daMAD = new OleDbDataAdapter();
daMAD.SelectCommand = new OleDbCommand(sqlMAD, _connOleDb);
DataSet dsMAD = new DataSet();
dsMAD.CaseSensitive = true;
OleDbCommandBuilder cbMAD = new OleDbCommandBuilder(daMAD);
daMAD.Fill(dsMAD, _dataTableMAD);

// Create CommandText with CommmandBuilder object for each Dataset
daMAD.InsertCommand = cbMAD.GetInsertCommand();
daMAD.UpdateCommand = cbMAD.GetUpdateCommand();
daMAD.DeleteCommand = cbMAD.GetDeleteCommand();

The cold below works when I connect to a SQL Server 2000 database, but not
when I connect to Oracle 8.1.7.

Has anyone had this problem before, or have an idea on how to make this code
work on Oracle? The script used to create the table as it is in Oracle is:

CREATE TABLE merak_attribute_dict (
archive_id NUMBER(10,0) NOT NULL ,
dictionary_id NUMBER(10,0) NOT NULL ,
owner_id NUMBER(10,0) NULL ,
name VARCHAR2(50) NOT NULL,
data_type NUMBER(10,0) NOT NULL ,
description VARCHAR2(250)
)

Thanks in advance,
Dave White
Schlumberger Information Solutions

[Marina]

I think the problem is that you need to have a primary key. You do not seem
to have one defined.

"Dave White" <da...@merak.com> wrote in message
news:ezn3qmkF...@TK2MSFTNGP12.phx.gbl...

[Dave White]

I'm sorry that I forgot to mention that there is a primary key on the table.
Here is the script that would add the primary key.

alter table merak_attribute_dict
add constraint pk_merak_attribute_dict PRIMARY KEY
(archive_id,dictionary_id)
USING INDEX TABLESPACE &INDX_TABLESPACE
STORAGE( initial 40K next 20K pctincrease 0 )

Thanks in advance,
Dave

"Marina" <zlat...@nospam.hotmail.com> wrote in message
news:#WBzESlF...@TK2MSFTNGP12.phx.gbl...

[Emmet Gray]

I realize that this "thread" is kinda old by now... but I had a similar
problem and *finally* was able to come up with a solution

To make the *CommandBuilder work properly, I often had to simplify the
commandtext so that the "base table" was easier for it to figure out. You
don't actually do another fill() or execute() with that commandtext in
place... it's just to help CommandBuilder do it's job. If you need to use
that command object again... I guess you could just switch back to the
orginal commandtext afterwards.

So, in this case... right before

OleDbCommandBuilder cbMAD = new OleDbCommandBuilder(daMAD);

Add this:
daMAD.SelectCommand.CommandText = string.format("select * from
merak_attribute_dict");

"Dave White" <da...@merak.com> wrote in message
news:ezn3qmkF...@TK2MSFTNGP12.phx.gbl...