OleDb Excel 12.0 bug

[Anton]

Hi,

I seem to have found a bug for which there seems not to be a workaround. The
following code will throw an exception, but if you change and use xls (even
using the new driver), it works.
Is there a workaround?

string constring = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=d:\\inetpub\\test.xlsx;Extended Properties=\"Excel 12.0
Xml;HDR=YES;\"";
//string constring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\\inetpub\\test.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\"";

OleDbConnection oleDBConnection = new OleDbConnection(constring);
oleDBConnection.Open();

OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
try
{
string createTable = "CREATE TABLE [Export] (test1 LONGTEXT,test2
LONGTEXT);";
OleDbCommand comOleDBCommand = new OleDbCommand(createTable,
oleDBConnection);
comOleDBCommand.ExecuteNonQuery();

string insertData = "INSERT INTO [Export$] (ddddd, test2)
VALUES('Florida','Share ideas, and get advice and assistance from fellow
Microsoft Office userss your community, and its all here. Make Office work
for you and interact with other users by participating in these
newsgroups.Share ideas, and get advice and assistance from fellow Microsoft
Office users—its your community, and its all here. Make Office work for you
and interact with other users by participating in these newsgroups.')";
OleDbCommand command = new OleDbCommand(insertData, oleDBConnection);

command.ExecuteNonQuery();
}
finally
{
oleDBConnection.Dispose();
}

 

[Anton]

Make that:
string constring = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=d:\\inetpub\\test.xlsx;Extended Properties=\"Excel 12.0
Xml;HDR=YES;\"";
//string constring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\\inetpub\\test.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\"";

OleDbConnection oleDBConnection = new OleDbConnection(constring);
oleDBConnection.Open();

OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
try
{
string createTable = "CREATE TABLE [Export] (test1 LONGTEXT,test2
LONGTEXT);";
OleDbCommand comOleDBCommand = new OleDbCommand(createTable,
oleDBConnection);
comOleDBCommand.ExecuteNonQuery();

string insertData = "INSERT INTO [Export$] (test1, test2)
VALUES('Florida','Share ideas, and get advice and assistance from fellow
Microsoft Office userss your community, and its all here. Make Office work
for you and interact with other users by participating in these
newsgroups.Share ideas, and get advice and assistance from fellow Microsoft
Office users—its your community, and its all here. Make Office work for you
and interact with other users by participating in these newsgroups.')";
OleDbCommand command = new OleDbCommand(insertData, oleDBConnection);

command.ExecuteNonQuery();
}
finally
{
oleDBConnection.Dispose();
}