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

Back to Forums

Edward Lee

Solved

Question

SQL

Forge

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!

06 Feb 2020

Angel Saxena

Solution

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

See solution in context

test.oml

08 Feb 2020

Ricardo Cruz

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

06 Feb 2020

vikas sharma

Champion

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.

06 Feb 2020

1 reply

07 Feb 2020

Show thread

Hide thread

André Siébra

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.

07 Feb 2020

Angel Saxena

Solution

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

test.oml

08 Feb 2020

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines