Search This Blog

Wednesday, September 10, 2014

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data, OledbException in c# .net

/*If you are facing issues with saving large data into excel
* u may get oledbexception like
* "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data"
* below is the solution for insert and updation of data
*/
  private void btnSaveExcel_Click(object sender, EventArgs e)
{

  string path = @"C:\TEMP\Testing.xls";
oXL =
new Microsoft.Office.Interop.Excel.Application();oXL.Visible =
false;oXL.DisplayAlerts =
false;mWorkBook = oXL.Workbooks.Open(path, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);


//Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets;
//Get the sheet which was already existsmWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("FAS");Microsoft.Office.Interop.Excel.
Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;mWSheet1.Cells[rowCount + 1, 1] = txtId.Text;
mWSheet1.Cells[rowCount + 1, 2] = txtName.Text;

mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.
XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
  //cleanup workbook and sheet
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);mWSheet1 =
null;mWorkBook =
null;oXL.Quit();

GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}


private void btnUpdateExcel_Click(object sender, EventArgs e){

  string path = @"C:\TEMP\Testing.xls";
oXL =
new Microsoft.Office.Interop.Excel.Application();oXL.Visible =
false;oXL.DisplayAlerts =
false;mWorkBook = oXL.Workbooks.Open(path, 0,
false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets;
//Get the sheet which was already existsmWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("FAS");Microsoft.Office.Interop.Excel.
Range range = mWSheet1.get_Range("A1").Find(txtId.Text);

if (range != null){

//assume unique records are available for column A1int rowCount = range.Rows.Row;mWSheet1.Cells[rowCount, 2] = txtName.Text;
}

mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.
XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
  //cleanup workbook and sheet
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);mWSheet1 =
null;mWorkBook =
null;oXL.Quit();

GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}

No comments:

Popular Posts