Calling ADO.NET gurus

[hx009]

I'm hoping someone here has even a remote clue how to do the following, because I've spent three days off and on looking at this and can't find a definitive answer. I know the Software forum is the appropriate place for this question, but it seems to not see even half the traffic that Off Topic does. In any case, does anyone know if it's possible to add a column to an Excel spreadsheet using JUST ADO.NET (absolutely no Office automation)? The following C# code throws an "Invalid Operation" error:

OleDbConnection conExcel = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myexcelfile.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"");

conExcel.Open();

OleDbCommand cmdExcel = new OleDbCommand("ALTER TABLE [Sheet1$] ADD columnname VARCHAR(20)", conExcel);

cmdExcel.ExecuteNonQuery();

 

[hx009]

Nobody here knows about ADO.NET?

 

[torpid]

I know a lot about ado.net. Using excel to add columns from ado.net is about as fringe as it gets. If you do a google on this topic, it would seem that it may not be possible using this technique. Maybe you can create a new table and copy all the data over and then drop the old one.

 

[hx009]

Originally posted by: torpid
I know a lot about ado.net. Using excel to add columns from ado.net is about as fringe as it gets. If you do a google on this topic, it would seem that it may not be possible using this technique. Maybe you can create a new table and copy all the data over and then drop the old one.

That doesn't work. Dropping a table in Excel merely wipes the data out of the sheet... the sheet itself is left behind. If you then attempt to SELECT INTO or INSERT INTO the wiped out sheet, the driver creates a NEW sheet anyways or complains about invalid columns respectively.