Overflow exception from Access using Oledb query c#

[Adinb007]

Hi Folks,

I have a MS access table with columns such as [Date] (of data type 'Text'),[Age] (of data type 'Text') and total records are greater than 100k. I need to calculate date difference in Days in column [Age] by subtracting current date with date in column [Date].

Column [Date] has blank values as well.

Below is the code, that i have written but it is throwing "Overflow" exception.

public bool ComputeAge1(string DB, string table)
{
string _conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DB + ";Jet OLEDBatabase Password=";

OleDbConnection _connection = new OleDbConnection(_conn);
System.Data.DataTable DT = new System.Data.DataTable();

OleDbCommand _command = new OleDbCommand();

string _Date = string.Empty;
try
{
using (OleDbConnection sqlConn = new OleDbConnection(_conn))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandText = @"UPDATE [" + table + "] SET [Age]= CInt(Date() - CDate(IIF(ISNULL([Date]), 1/1/1900, [Date])))";
cmd.Connection = sqlConn;
sqlConn.Open();
cmd.ExecuteNonQuery();
sqlConn.Close();
}
}

return true;
}
catch (Exception ex)
{
return false;
}
}

Continue reading...