#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Location
    São Paulo - Brazil
    Posts
    35
    Rep Power
    2

    Set a column value to null


    I can't remember how to do this. Sometime, somewhere I learned about this. But I forgot.

    Suppose my program is positioned in row of a table and I want to change the value of a FLOAT column from its value to null in a edit sequence like this:

    .
    .
    Query1.Edit;
    .
    .
    // Here I would like to change the float column value to null
    // Wich command I must use?
    .
    .
    Query1.Post;

    Suppose the float column has the value 50 before the Edit and I want it to contain null after the Post command. Lets call the float column "inventorycode".

    Greetings from Sao Paulo - Brazil

    Ricardo
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    350
    Rep Power
    7
    I usually do it via an sql update, and if you have the data being displayed via another query/datasource, just refresh that query to reflect the update.

    Drop another TQuery component on your form (call it updatequery), no need for a TDataSource.

    Set its SQL property to

    Update <table name> set <column name> = null where <where condition>

    replace table name, column name and make sure your where condition will only affect the correct record (the where clause can also have parameters)

    then execute the query, and refresh Query1
    Code:
    UpdateQuery.SQL.Text := 'Update ... set ... = null where ...';
    UpdateQuery.ExecSQL;
    Query1.Refresh;//ensures Query1 knows of the update.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    You can either call Clear method of the field, or simply set its value to Null.

    Using clear:
    Code:
      ...
      Query1.FieldByName('TheFloatFieldName').Clear;
      ...
      Query1.Post;
    Using Null (you need to use Variants unit):
    Code:
      ...
      Query1.FieldByName('TheFloatFieldName').Value := Variants.Null;
      ...
      Query1.Post;

IMN logo majestic logo threadwatch logo seochat tools logo