Handling of NULL fields in Insert/Update Queries

Home Forum Integrating PerfectForms Handling of NULL fields in Insert/Update Queries

This topic contains 0 replies, has 0 voices, and was last updated by  ijobling 9 years, 7 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #183

    ijobling
    Participant

    If in your insert/update actions where there is no data for a field , the way a database works is that it converts the user input to the correct dataType set for that column. In the case of a datetime field, it converts “” (empty) to the default datetime value so it can be inserted into the database. (eg 1900-01-01 00:00:00.000)

    If you want to insert NULL into the datetime column, you don’t send anything for that column, you insert into all the other columns but not the datetime one.

    In practical terms, you create 2 INSERT actions – one that includes the datetime field and another that does NOT refer to this datetime field… and use a Simple Branch to choose the desired one based on the datefield value: (ie is empty/not empty depending on the practicalities of the state of the field)

    Then if ‘is empty’ call the insert action that does not refer to the datetime field (your query in the log file may look something like this: INSERT INTO [yourtable] ([numeric], [normal]) VALUES (‘1234’, ‘text’)

    And if it is not empty, call the original insert action (your query would then look something like this : INSERT INTO [yourtable] ([Date], [numeric], [normal]) VALUES (‘2009-05-11 00:00:00’, ‘1234’, ‘text’)

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.