[Advanced Filter Builder] INSERT/UPDATE not inserting NULL using CRUD

Forge Component
(26)
Published on 2019-01-15 by João Pêgas
26 votes
Published on 2019-01-15 by João Pêgas

Hello,

I am creating an internal CRUD (create, read, update, delete) system, and on the detail screen where you can edit or add new entries to the table (external SQL table), I leave the value blank for the datetime column and when I save and run the query from SQL Server Management Studio, instead of the datetime being NULL it is coming up as the Outsystems default: #1900-01-01 00:00:00#. This is doing it for all columns depending on the datatype (0.0 for decimal etc)

Currently I am doing the update and creates inside a new screen using a form with labels and inputs inside per column. 


Any tips would be greatly appreciated! 

Hi Edward,

OutSystems doen't have the NULL concept. For example:  NullDate is not NULL , it's 1900-01-01 00:00:00.

When you don't have a value on creating or updating a record, OutSystems give that value automatically.

All the best

Hi Edward,

As Outsystems based on .net & MS-SQL so it don't support NULL values in database, like it possible in mysql.

So it will always store default values. If you don't want to show default values to user, you need to remove them by comparing to default values in expression.

Thanks.

Vikas Sharma wrote:

Hi Edward,

As Outsystems based on .net & MS-SQL so it don't support NULL values in database, like it possible in mysql.

So it will always store default values. If you don't want to show default values to user, you need to remove them by comparing to default values in expression.

Thanks.

Hi Edward,

are you saying that a MSSQL database doesn't support NULL values? Are you sure about that?

As far as I know @Ricardo Cruz is right. You can check this post for more info about null values on blank inputs. 


Solution
Processing Upload...

Hi Edward, 

If your Date field does not contain any values, Outsystems will assign "1900-01-01" i.e. the default value of "Date" data type.

However if you don't want to get default value of date in your db and prefer to keep the field as " " when there is no input in date field of your form, you can do this.

Change the data type of your date attribute to text. On the function where you are inserting the value, just check if 

DateToText(NullDate())

If it's true insert "" in your db else insert the below value to your db. 

TextToDate(DataForm.Record.Data.Date)


This is not a recommended practice but if this solves you problem please mark it as solution.

I have also attached an oml file for your reference.


Thanks

Solution