You must Sign In to post a response.
  • Category: ASP.NET

    Excel oledb error :The field is too small to accept the amount of data you attempted to add

    Are you looking for a way to export data to excel using OLEDB ? want to resolve error 'The field is too small to accept the amount of data you attempted to add' ? then read this thread to know how to resolve it

    When ever user is click on Download button then it is giving error message:
    OleDbException was caught

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

    I have create Fix Template and code on my page:

    string filepath = Server.MapPath("/AllFileReadWrite/ContractorRate.xls");
    string newFilePath = Server.MapPath("/AllFileReadWrite/" + pkgName + "-ContractorRate.xls");

    System.IO.FileInfo myfile = null;
    System.IO.FileInfo myNewfile = null;
    myfile = new FileInfo(filepath);
    myfile.CopyTo(newFilePath, true);


    //To Get the physical Path of the file(ContractorRate.xls)
    //filepath = Server.MapPath("/AllFileReadWrite/ContractorRate.xls");

    // Create New instance of FileInfo class to get the properties of the file being downloaded
    myNewfile = new System.IO.FileInfo(newFilePath);

    public void WriteExcelFile(DataTable dtCurrentTable)

    for (int i = 0; i < dtCurrentTable.Rows.Count; i++)
    OleDbParameter id = new OleDbParameter("@ID", OleDbType.BigInt);
    id.Value = Int64.Parse(dtCurrentTable.Rows[i][1].ToString());

    OleDbParameter Desc = new OleDbParameter("@Desc", OleDbType.LongVarWChar,100000);
    Desc.Value = dtCurrentTable.Rows[i][2].ToString();

    OleDbParameter UnitRateFormula = new OleDbParameter("@UnitRateFormula", OleDbType.LongVarChar);
    UnitRateFormula.Value = dtCurrentTable.Rows[i][8].ToString();

    OleDbParameter AmountFormula = new OleDbParameter("@AmountFormula", OleDbType.LongVarChar);
    AmountFormula.Value = dtCurrentTable.Rows[i][9].ToString();


    string strSqlCommand = "Insert into [BOQ_Contractor_Rate$] values('" + dtCurrentTable.Rows[i][0].ToString() + "', @ID, @Desc , '" + dtCurrentTable.Rows[i][3].ToString() + "','" + dtCurrentTable.Rows[i][4].ToString() + "','" + dtCurrentTable.Rows[i][5].ToString() + "','" + dtCurrentTable.Rows[i][6].ToString() + "','" + dtCurrentTable.Rows[i][7].ToString() + "',@UnitRateFormula, @AmountFormula)";

    com.CommandText = strSqlCommand;

    catch (Exception ex)
    throw ex;
  • #734364
    There are couple of workaround here you can check them below
    1. I think, There is limitatio of 255 characters to OLEDB provider and hence it can not insert large values to excel sheet
    2. OLEDB is the auto parsing provider for excel. when we insert data from any source to excel, the provider scan first 3 or 4 rows and create datatype as per its length. so, when a large data come after the datatype creation it gives above error. To resolve it, keep large size data first in source and then keep small data. it will resolve your issue.
    ask me if you have any doubt

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #734377
    If any one of column values is more than 262 characters long then you will get error message like this "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." I don't know how to solve this problem we will wait some one will give solution for this.

    Thanks & Regards
    Nothing is mine ,Everything is yours!!!

  • #734391

    Try to debug the code and see in which line you are getting this error.

    As the error clearly suggests, you are trying to insert data more than what you have declared in the Database.

    Asheej T K

  • #769885
    I'm facing same error. what is the final solution???

    Inorder to insert larger rows what kind of changes can be done

  • Sign In to post your comments