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;
try
{
myfile = new FileInfo(filepath);
myfile.CopyTo(newFilePath, true);
pkg.ConnectToExcel(newFilePath);
pkg.WriteExcelFile(dtCurrentTable);
//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)
{
con.Open();
try
{
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();
com.Parameters.Add(id);
com.Parameters.Add(Desc);
com.Parameters.Add(UnitRateFormula);
com.Parameters.Add(AmountFormula);
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;
com.ExecuteNonQuery();
com.Parameters.Remove(id);
com.Parameters.Remove(Desc);
com.Parameters.Remove(UnitRateFormula);
com.Parameters.Remove(AmountFormula);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
