oledbCommandBuilder does not work with Excel (could be a bug)

  • Thread starter Thread starter Steve Drake
  • Start date Start date
S

Steve Drake

All,

I am writting an app pre load excel spreadsheets with lots of data, I want
to be abil to use the command builder for easy coding, but it dont seam to
work with Excel, I have the following code :


OleDbDataAdapter myAd = new OleDbDataAdapter();
myAd.SelectCommand = new OleDbCommand( @"select FIELD_1,FIELD_2,FIELD_3
from [TABLE$]",myCon);
OleDbCommandBuilder cb = new OleDbCommandBuilder(myAd);

/* IF I UNCOMMENT THIS IT WORKS
myAd.InsertCommand = new OleDbCommand( "INSERT INTO [TABLE$] (FIELD_1,
FIELD_2, FIELD_3) VALUES (?, ?,?)", myCon );
myAd.InsertCommand.Parameters.Add("@FIELD_1", OleDbType.VarChar,
255,"FIELD_1");
myAd.InsertCommand.Parameters.Add("@FIELD_2",
OleDbType.VarChar,255).SourceColumn ="FIELD_2";

myAd.InsertCommand.Parameters.Add("@FIELD_3",OleDbType.VarChar,255).SourceCo
lumn = "FIELD_3";

*/

myAd.Update(DataSetWithDataIn);
 
It is not a bug it is by design.
(You may not like or agree with the desing, but that doesn't make it a bug!)

The command builder queries the database for schema information, etc. to
build the appropriate commands.

Since Excel is not a database, why would you expect it to work?
 
I would expect it to work, and since I posted this I have seen examples of
this working, but I haven't had the time to try them.

I think you have to reference via a table, eg command type of named table.

I was thinking of writing my own command builder, as technical its not that
hard as the DATASET has all the info needed to generate the insertSQL.

but..... most OLEDB sources are strictly not a database and if they are they
normally have a native .net managed provider, when you write a OLEDB
datasource you have a choice off how much you implement, so I would expect
and error, like 'provider not supported', it would depend on how it gets the
schema, does it try to get the schema from the provider (eg if the provider
implements it) or can it build a schema from the dataset?

Steve


Joe Fallon said:
It is not a bug it is by design.
(You may not like or agree with the desing, but that doesn't make it a bug!)

The command builder queries the database for schema information, etc. to
build the appropriate commands.

Since Excel is not a database, why would you expect it to work?
--
Joe Fallon



Steve Drake said:
All,

I am writting an app pre load excel spreadsheets with lots of data, I want
to be abil to use the command builder for easy coding, but it dont seam to
work with Excel, I have the following code :


OleDbDataAdapter myAd = new OleDbDataAdapter();
myAd.SelectCommand = new OleDbCommand( @"select FIELD_1,FIELD_2,FIELD_3
from [TABLE$]",myCon);
OleDbCommandBuilder cb = new OleDbCommandBuilder(myAd);

/* IF I UNCOMMENT THIS IT WORKS
myAd.InsertCommand = new OleDbCommand( "INSERT INTO [TABLE$] (FIELD_1,
FIELD_2, FIELD_3) VALUES (?, ?,?)", myCon );
myAd.InsertCommand.Parameters.Add("@FIELD_1", OleDbType.VarChar,
255,"FIELD_1");
myAd.InsertCommand.Parameters.Add("@FIELD_2",
OleDbType.VarChar,255).SourceColumn ="FIELD_2";
Click to expand...
myAd.InsertCommand.Parameters.Add("@FIELD_3",OleDbType.VarChar,255).SourceCo
lumn = "FIELD_3";

*/

myAd.Update(DataSetWithDataIn);
Click to expand...
Click to expand...
 
Back
Top