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();
}

Select/Insert/Update Excel 2007 File using c# in .net

//insert data into excel sheet

private void btnSave_Click(object sender, EventArgs e)
{
using (OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEMP\\Testing.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"))
{
excelConnection.Open();

OleDbCommand command = new OleDbCommand("INSERT INTO [Sheet1$] ([Id],[Name]) VALUES(@value1, @value2)", excelConnection);
command.Connection = excelConnection;

command.Parameters.AddWithValue("@value1", txtId.Text);
command.Parameters.AddWithValue("@value2", txtName.Text);

command.ExecuteNonQuery();
}

btnGetData_Click(null, null);
}

//get data from excel sheet
private void btnGetData_Click(object sender, EventArgs e)
{
using (OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEMP\\Testing.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"))
{
excelConnection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("select Id, Name from [Sheet1$]", excelConnection);
DataSet ds= new DataSet();
adapter.Fill(ds);

dgvExcelData.DataSource = ds.Tables[0];
}
}

//update data of excel sheet
private void btnUpdate_Click(object sender, EventArgs e)
{
using (OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEMP\\Testing.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"))
{
excelConnection.Open();

string sqlQuery = "update [Sheet1$] set [Name] = '" + txtName.Text + "' where [Id] = " + txtId.Text;

OleDbCommand command = new OleDbCommand(sqlQuery, excelConnection);
command.Connection = excelConnection;


command.ExecuteNonQuery();
}
}



Note: If the "id" column is numeric:

If the "id" column in the sheet is of "General" type, the Sql statement is:

UPDATE [Sheet1$] SET Name ='Nameddd' WHERE id=1

If the "id" column in the sheet is of "Text" type, the Sql statement is
UPDATE [Sheet1$] SET Name ='Nameddd' WHERE id="1"