Default value for mysqlDataTable column

Default value for mysqlDataTable column

Postby oleengvoll » Fri 04 Nov 2011 11:53

Hi! When using a sql datatable for updating the db, it is possible to set default value for each field in the table (columns collection property in designer). But what is syntax for insert the system current datetime? As default it is . Thanx. Ole
oleengvoll
 
Posts: 12
Joined: Tue 27 Sep 2011 08:37

Postby Pinturiccio » Mon 07 Nov 2011 17:03

-You can set the DefaultValue property to a string of DateTime format at design-time.

-You can initialize the DefaultValue property with System.DateTime.Now at run-time. In this case the DefaultValue property will be equal to the time of the initialization.

- You can use parameters in your query. Than you can change the appropriate parameter value to System.DateTime.Now before using the insert command to set the current system DateTime.

- You can create a trigger on the server that will insert the default value that equals to the current time on the database server.

Code: Select all
-- Clean up before starting.
    DROP TABLE IF EXISTS `test_tbl`;
    DROP TRIGGER IF EXISTS `test_tbl_before_insert`;
     
    -- Create the table.
    CREATE TABLE `test_tbl` (
        `id` Int Auto_Increment Not Null Primary Key,
        `random_value` VarChar(255) Not Null Default 'value',
        `created` DateTime Not Null
    );
     
    -- Create a trigger to update the created value.
    DELIMITER //
    DROP TRIGGER IF EXISTS `test_tbl_before_insert`//
    CREATE TRIGGER `test_tbl_before_insert`
    BEFORE INSERT ON `test_tbl`
    FOR EACH ROW
    BEGIN
        -- Only set the default value if it's empty
        IF NEW.created = '0000-00-00 00:00:00' THEN
            SET NEW.created = NOW();
        END IF;
    END;//
    DELIMITER ;
     
    -- INSERT using the default value.
    INSERT INTO `test_tbl`(`random_value`)
    VALUES ('First'), ('Second'), ('Third');
     
    -- INSERT explicit values
    INSERT INTO `test_tbl`(`random_value`, `created`)
    VALUES ('Fourth', '2010-01-01'), ('Fifth', '2009-12-24');
     
    -- Select stuff
    SELECT * FROM `test_tbl`;
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44

Postby oleengvoll » Tue 08 Nov 2011 14:16

Splendid! I'll go with suggestion nr 2!
Thanx. Ole
oleengvoll
 
Posts: 12
Joined: Tue 27 Sep 2011 08:37


Return to dotConnect for MySQL