Inserting NULL value in SQL Server not working

BIZ TMS Aurelius
1

Hello,


I have a table in SQL Server 2008 R2 that has a few nullable float fields. The Aurelius data type is Nullable<double> but no matter what I try I can't seem to create a new record in the table with these fields set to NULL. 

When I create a new object of this type and insert it to the database the values in the DB are 0 even though the objects value's HasValue property is false. If I try to update the object and set the object's value to SNull it still won't be NULL in the DB.

Any ideas on how to resolve this?

After experimenting a bit I see that if the default value of the field in SQL Server is NULL it becomes NULL when inserting a new record without specifying any values for this field (as expected). But still, I can't set it to a float value and then back to NULL (by setting the Aurelius object's property to SNull).

Thanks for any suggestions!
 
2

Hello,


Can you please use the command listener (like the one provided in music library demo) and inform the SQL generated by Aurelius in both Insert and Update commands?
3

Hello

When I use the CommandListener I get the following SQL statements:


================================================
INSERT INTO VesselHistory (
  ExportDate, ImportDate, VesselId, VoyageId)
VALUES (
  :A_ExportDate, :A_ImportDate, :A_VesselId, :A_VoyageId);

A_ExportDate = "30/12/1899" (ftDateTime)
A_ImportDate = "30/12/1899" (ftDateTime)
A_VesselId = "2" (ftInteger)
A_VoyageId = "8" (ftInteger)

================================================
================================================
UPDATE VesselHistory SET
  GpsSpeed = :A_GpsSpeed, 
  LogSpeed = :A_LogSpeed, 
  DraftMean = :A_DraftMean, 
  ExportDate = :A_ExportDate, 
  ImportDate = :A_ImportDate, 
  Trim = :A_Trim, 
  DraftMeanLogVariableItemId = :A_DraftMeanLogVariableItemId, 
  GpsSpeedLogVariableItemId = :A_GpsSpeedLogVariableItemId, 
  LogSpeedLogVariableItemId = :A_LogSpeedLogVariableItemId, 
  TrimLogVariableItemId = :A_TrimLogVariableItemId
WHERE VesselHistoryId = :p_10;

A_GpsSpeed = "6.63633489608765" (ftFloat)
A_LogSpeed = "4.57855653762817" (ftFloat)
A_DraftMean = "12" (ftFloat)
A_ExportDate = "10/12/2013 14:58:00" (ftDateTime)
A_ImportDate = "20/02/2014 13:42:01" (ftDateTime)
A_Trim = "0.500000000000007" (ftFloat)
A_DraftMeanLogVariableItemId = "61" (ftInteger)
A_GpsSpeedLogVariableItemId = "80" (ftInteger)
A_LogSpeedLogVariableItemId = "81" (ftInteger)
A_TrimLogVariableItemId = "85" (ftInteger)
p_10 = "276" (ftInteger)

================================================

The fields that are NULL are not included in the INSERT or UPDATE statement, which I guess makes sense if they are not modified. The problem is that in the MSSQL server, the default values are not set to NULL for these fields, and thus we have a mismatch between value in SQL server and Aurelius object.

I will do more tests to see what happens when I try to change a field value to NULL.

Thanks,

4

Hi again,

Testing by changing the values from 0 to NULL gives this result

================================================
UPDATE VesselHistory SET
  Latitude = :A_Latitude, 
  Longitude = :A_Longitude
WHERE VesselHistoryId = :p_2;

A_Latitude = NULL (ftFloat)
A_Longitude = NULL (ftFloat)
p_2 = "281" (ftInteger)

================================================

So it manages to update the fields and set them to NULL if they are not NULL from before.

I guess the problem is that there is a mismatch between Aurelius objects and SQL Server data after doing an insert.

5

Yes, that's the problem. If the SQL Server has some fields which default is not null, then that's what they will have. For Aurelius, the fields were unmodified. That's an expected behavior, actually.