Can't update a record that has a null date value in Telerik Open Access

Question

I have a solution with a Telerik Data Access project. I encountered a problem when trying to update a record. I think the problem has to do with the fact that it is a date field with a null value in the database before trying to update it, but I'm not sure. Can anyone tell me how to work around it?

In the MVC project, we have a model called tblCoLocation. It is linked to our SQL database. The mode is defined in the Data Access project as

namespace Project.OpenAccess
{
public class tblCoLocation
 {
    public int LocID { get; set; }
    ...
    public DateTime ModOn { get; set; }
    public int ModBy { get; set; }
 }
}

To update the data, in a file called Repository.cs, I have this method:

public void UpdateCoLocation(tblCoLocation obj)
    {
        var upd = dat.tblCoLocations.FirstOrDefault(itm => itm.LocID == obj.LocID);

        if (upd != null)
        {
          upd.ModOn = obj.ModOn;
          upd.ModBy = obj.ModBy;
        }

        dat.SaveChanges();
    }

I have similar code for updating other models, but for tblCoLocation it throws an error. The field ModOn is null in the database (this is by the design of our client, null means the record hasn't been modified, and they want to keep the data that way). We're trying to set ModOn to today's date and I've verified that the model does have a value for ModOn when it hits the SaveChanges() command. If I manually put a value in ModBy in the database, the update works as expected. This is why I think having ModOn initially null may be the problem.

The error that occurs when it gets to this section (I've stepped through the code, and the error comes from dat.SaveChanges) is this:

Server Error in '/' Application. SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

[SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]

Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate(Nullable 1 commandTimeout) +785
OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate(Nullable`1 commandTimeout) +146
OpenAccessRuntime.Relational.RelationalStorageManager.generateUpdates(OID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, Boolean haveNewObjects, CharBuf s, BatchControlInfo batchControl, Boolean previousInserts) +3385

[DataStoreException: Update failed: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate(Nullable 1 commandTimeout) at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeUpdate(Nullable1 commandTimeout) at OpenAccessRuntime.Relational.RelationalStorageManager.generateUpdates(OID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, Boolean haveNewObjects, CharBuf s, BatchControlInfo batchControl, Boolean previousInserts) Row: GenericOID@91ae60ab tblCoLocation LocID=5 UPDATE [tblCoLocation] SET [Address2] = ?, [Country] = ?, [ModBy] = ?, [ModOn] = ? WHERE [LocID] = ? AND [Address2] = ? AND [Country] = ? AND [ModBy] = ? AND [ModOn] = ? (set event logging to all to see parameter values) System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at

Does it look like I am correct in guessing that it's the initial null value of ModOn in the database is causing the problem? If so, how can I save this record without changing the initial value of ModOn?

Answer 1

This means you never set the value ModOn which means it's value is 1/1/0001. this value can not be stored in a sql datetime field. you can either make sure the obj.ModOn has a valid date greater than 1/1/1753, or change the sql column to a datetime2 type.

You can also do this to change the value to SqlDateTime.MinValue before saving. But since you did not make the column optional by using DateTime? you may have a validation issue somewhere.

public void UpdateCoLocation(tblCoLocation obj) {
    var upd = dat.tblCoLocations.FirstOrDefault(itm => itm.LocID == obj.LocID);

    if (upd != null) {
        upd.ModOn = obj.ModOn < System.Data.SqlTypes.SqlDateTime.MinValue ? 
            System.Data.SqlTypes.SqlDateTime.MinValue : 
            obj.ModOn;
        upd.ModBy = obj.ModBy;
    }

    dat.SaveChanges();
}