Hello,
Using Delphi7 with SDAC 6.9.17
Recently we've noticed some change in behavior between SDAC & DBExpress
We have a table and a field with decimal(28,9) type & default 0 value & allow null values
when we create a new row for this table with the SDAC components and we set the value of the field to be Null
for example:
DataSet.FieldByName('field1').Value := Null;
or
DataSet.FieldByName('field1').Clear();
in both cases after the Dataset.Post() action the field is being ignored in the insert query which is incorrect because we've set the value to be Null
this results with a new row being inserted with the default value which is 0 and not Null as it was intended
this behavior works fine when we are using the DBExpress components
we've also noticed that in DBExpress the field's new value property is set to unassigned and when we set it to Null it is included in the insert query as it should.
Thanks,
Insert data with Null value instead of default value
Re: Insert data with Null value instead of default value
Hello,
Such behavior is due to that fields with NULL value are not included to the INSERT SQL query.
To solve the problem, it is enough to set the TMSQuery.Options.UpdateAllFields option to True.
In this case, all the fields will be included to the INSERT SQL query.
You can find more details in the SDAC help http://www.devart.com/sdac/docs/index.h ... fields.htm
Such behavior is due to that fields with NULL value are not included to the INSERT SQL query.
To solve the problem, it is enough to set the TMSQuery.Options.UpdateAllFields option to True.
In this case, all the fields will be included to the INSERT SQL query.
You can find more details in the SDAC help http://www.devart.com/sdac/docs/index.h ... fields.htm
Re: Insert data with Null value instead of default value
Hi,
we have the same problem. We are migrating from ADO to SDAC and in generally fields with NULL value shouldn't be in the INSERT-Statement, because we are using SQL Server constraints to set default values.
But in some cases we explicitly want to set the value to NULL.
So we can not use TMSQuery.Options.UpdateAllFields.
ADO collects all modified fields in TCustomADODataSet.SetFieldData FModifiedFields.
Could you extend SDAC's behaviour with such a mechanism?
we have the same problem. We are migrating from ADO to SDAC and in generally fields with NULL value shouldn't be in the INSERT-Statement, because we are using SQL Server constraints to set default values.
But in some cases we explicitly want to set the value to NULL.
So we can not use TMSQuery.Options.UpdateAllFields.
ADO collects all modified fields in TCustomADODataSet.SetFieldData FModifiedFields.
Could you extend SDAC's behaviour with such a mechanism?
Re: Insert data with Null value instead of default value
We will consider the possibility to change this behavior of SDAC.
If you want us to implement the feature faster, please post it at our user voice forum: https://devart.uservoice.com/forums/104 ... sql-server If the suggestion gets a lot of votes, we will consider the possibility to implement it.
If you want us to implement the feature faster, please post it at our user voice forum: https://devart.uservoice.com/forums/104 ... sql-server If the suggestion gets a lot of votes, we will consider the possibility to implement it.