none
Progrmmatocally Inserting Data to Excel RRS feed

  • Question

  • Hi All,
    I am inserting data to an Excel sheet pro grammatically.
    I use the following udpate command:

    da.UpdateCommand = new OleDbCommand(@"UPDATE [Sheet1$] SET [Description (QA)] = ? , [R&D Comments (Dev/Analysts)] = ? WHERE [Defect ID] = ?", conn);
    da.UpdateCommand.Parameters.Add("@Description (QA)", OleDbType.VarChar, 255, "Description (QA)");
    da.UpdateCommand.Parameters.Add("@R&D Comments (Dev/Analysts)", OleDbType.VarChar, 255, "R&D Comments (Dev/Analysts)");

    I cant use a value > 255.

    If I use parameter size > 256, I get the exception while updating :

    The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.


    Is there anyway to increase the limit?


    Tuesday, July 24, 2007 5:27 PM

All replies

  • Hi guys,
    I tried various options like using other OleDbTypes loke LongVarchar, BSTR, WChar.
    All give me the same error if I specify the length of parameter > 255.
    I feel this might be a restriction imposed by the Jet provider, as the types as such does not restrict it to 255.

    Here are more details on error.

    "The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data."

    Call Stack:

    at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
    at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
    at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
    at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
    at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
    at BugInjector.BugMigrator.Update(String dataFile)    

    ErrorCode    -2147217833

    Tuesday, July 24, 2007 10:16 PM
  • I believe it is limitation of Jet provider when it works with strings. Have you tried to use LongVarChar without specifying size of the parameter?
    Wednesday, July 25, 2007 10:26 AM
    Moderator
  • If I dont give the column name, then it complains that the default value for the parameter is missing while updating.
    So I did this to mention the default value, w/o source column name, but now only the default value goes to excel, not the value I set afterwards.

    da.UpdateCommand.Parameters.Add("@Description (QA)", OleDbType.LongVarChar).Value = "";

    I also tried using IMEX=1 in the connection string. No effect..Sad
                 
    Wednesday, July 25, 2007 4:54 PM