How to insert a column in Excel by using OleDB

Hi all, I need to add a new column in an excel file, as I jus know the
query to add a new row in excel.
string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
" values ('"+name+"', '"+id+"')";
Anyone know the query to add a new column? Thankz...
Message posted via http://www.webservertalk.comYou might try the ALTER TABLE command. Not sure if it will work for a
linked Excel file, but it's standard SQL DDL to modify your table
structure - including adding a column.
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
> --
> Message posted via http://www.webservertalk.com|||Ya...but I duno the syntax of the ALTER TABLE....I have tried
string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
but it doesn't seems to work...can you please give an example of the Alter
Table SQL? Thankx...
Message posted via http://www.webservertalk.com|||> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
In SQL Server, you should omit COLUMN. I don't know if this will work with
Excel:
string strCom = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:14c9b951bfd14ffc93557f7c6d03b877@.SQ
webservertalk.com...
> Ya...but I duno the syntax of the ALTER TABLE....I have tried
> string strCom = " ALTER TABLE [Sheet1$] ADD COLUMN Phone INTEGER ";
> but it doesn't seems to work...can you please give an example of the
> Alter
> Table SQL? Thankx...
> --
> Message posted via http://www.webservertalk.com|||I'm using C# with OleDB, here is my code
========================================
===============================
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
fileName + ";Extended Properties=Excel 8.0" ; ;
OleDbConnection myConn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand = new OleDbDataAdapter();
string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
myConn.Open();
myCommand.Fill (myDataSet, "[Sheet1$]");
myConn.Close();
========================================
===============================
I got an error of "Invalid operation"...anyway, it works fine with
UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
ALTER TABLE syntax?
Message posted via http://www.webservertalk.com|||As I said, I'm not even sure ALTER TABLE is supported when accessing XLS
files via OLEDB. That was just something to try based on standard SQL DDL.
Here's a link that has a tip, although they say it's a little complex:
http://www.eggheadcafe.com/ng/micro...490.asp

"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||> anything wrong with the ALTER TABLE syntax?
This syntax is valid for Microsoft SQL Server. I don't know what syntax Jet
expects or if it is even possible to add a column to an existing sheet using
the Jet OleDb provider
You might try posting your question to the OleDb forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f1abb60e1ec74c2da572c3318607e310@.SQ
webservertalk.com...
> I'm using C# with OleDB, here is my code
> ========================================
===============================
> string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
> fileName + ";Extended Properties=Excel 8.0" ; ;
> OleDbConnection myConn = new OleDbConnection(strCon);
> OleDbDataAdapter myCommand = new OleDbDataAdapter();
> string strCom1 = " ALTER TABLE [Sheet1$] ADD Phone INTEGER ";
> myCommand.SelectCommand = new OleDbCommand(strCom1, myConn);
> myConn.Open();
> myCommand.Fill (myDataSet, "[Sheet1$]");
> myConn.Close();
> ========================================
===============================
> I got an error of "Invalid operation"...anyway, it works fine with
> UPDATE, SELECT and INSERT except ALTER TABLE...anything wrong with the
> ALTER TABLE syntax?
> --
> Message posted via http://www.webservertalk.com|||"yaya via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:15cfdd8a8a734a3e87f06370bc769573@.SQ
webservertalk.com...
> Hi all, I need to add a new column in an excel file, as I jus know the
> query to add a new row in excel.
> string strCom = "INSERT INTO [Sheet1$] (Name, ID)" +
> " values ('"+name+"', '"+id+"')";
> Anyone know the query to add a new column? Thankz...
>
are you forced to use "oledb"?
object automation is very flexible way of interfacing excel:
set oxls=createobject("excel.application")
set owbk=oxls.workbooks.open("mytable.xls")
set owsht=owbk.activesheet
owsht.activecell.entirecolumn.insert
...
with such object you may use any construct supported by excel macro.|||Thanks for the suggestion, so how could I know wherether the ALTER TABLE is
supported or not?
Message posted via http://www.webservertalk.com|||Ops...I just saw the ODBC and JDBC...may I know where is the OleDB forum ?
Thankz.
Message posted via http://www.webservertalk.com