3

I am using OLE DB driver to insert more than 255 characters into an Excel sheet, but I get the error:

Exception Details: System.Data.OleDb.OleDbException: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Seems from this thread that it's an Excel limitation. Even the Microsoft site seems to say so here.

So does this mean I can't programmatically, but I can manually? Because I can enter more than 255 characters when I manually type them in Excel. So is it a Microsoft.ACE.OLEDB.12.0(AccessDatabaseEngine.exe) driver limitation?

Community
  • 1
  • 1
Joseph
  • 2,155
  • 6
  • 20
  • 32

3 Answers3

5

My workaround to this issue is to add a text which its length is longer than 255 characters on the first row of the column. OleDB will treat this column as MEMO type and then it can insert more than 255 characters in a cell.

kingkong0924
  • 301
  • 2
  • 5
  • 1
    Thank you so much! You are awesome! It works like a charm, I've just hided the second line and it looks great! – nelucon Mar 30 '16 at 21:01
4

I found a more efficient way to handle this problem, my references are the 2 links below

http://support.microsoft.com/kb/316934

http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled

Basically do not use data adapter, data set and parameters to insert rows in the Excel (this never worked for me) instead run SQL insert from the code directly using command object

Here what you have to do

  1. Declare the wide field as MEMO instead of string or CHAR "CREATE TABLE WorkSheetName ( field1 INT, field2 MEMO, field3 ... );"
  2. Build the insert statement like "INSERT INTO WorkSheetName (field1, field2, ...) VALUES (1234, 'Any long string here...', ...)"

  3. Declare a DbCommand to run the Create table and Insert statements above, here I am using the command obj from enterprise library

    DbCommand dbCommand = m_dbConnection.CreateCommand(); dbCommand.CommandText = "Create Table ..."; dbCommand.CommandType = CommandType.Text; dbCommand.ExecuteNonQuery();

    dbCommand.CommandText = "Insert Into ..."; dbCommand.CommandType = CommandType.Text; dbCommand.ExecuteNonQuery();

Waleed
  • 41
  • 2
  • This did not work for me. Fortunately, the `CREATE TABLE` statement creates a named range of the same name (only without the $). If I `INSERT INTO` the range, then it works. – Slogmeister Extraordinaire Nov 14 '18 at 18:34
2

The link you are refering to, is about 256 columns and not characters. The 256 characters problem is described here with a workaround: http://support.microsoft.com/kb/213841

jvdbogae
  • 1,241
  • 9
  • 15
  • Nope - read carefully. On the MS site, see 3rd row. It says: Column width: 255 characters Btw, thank you for the workaround! – Joseph Dec 22 '10 at 04:53
  • 1
    Link only answers are strongly discouraged in SO -- for good reasons. – ndemou Jun 10 '18 at 13:26