Dropdown 'BLANK IS NULL' when prechoices are used.

[aburningflame]

Dropdown 'BLANK IS NULL'

I have attached a screenshot.
In the prechoices I have defined this combobox as:
Unassigned

Ive also tried
Unassigned|
and
Unassigned|0

None of these send the proper 'NULL' back to the database.

110429 16:49:03	 2003 Connect	billing_admin@localhost on billing
		 2003 Query	SET character_set_results=utf8
		 2003 Query	SET character_set_client=utf8
		 2003 Query	UPDATE project SET client_id = 0  WHERE prjID = 38  AND ( client_id = 12  OR client_id IS NULL  AND 12 IS NULL )

The grid is sending back a 0 - it should be setting client_id to NULL. Looks like I have to handle onbeforesqlcommandexecute and manually replace client_id=0 with client_id=NULL.

:( sometimes i wish things would just work.

[Davidk]

Re: Dropdown 'BLANK IS NULL' when prechoices are used.

I tried a few things as well for the pre choices but nothing seemed to catch. However, how about trying it from the other end.

In your pre-choice set Unassigned|Unassigned

And then in the server set a trigger to changed 'Unassgined' to NULL.

In MySQL the triggers for Insert and Update are the following. These are my very first Triggers so please be kind.

Kobe is the name of my database.

-- Trigger DDL Statements
DELIMITER $$

USE `kobe`$$

CREATE
DEFINER=`Admin`@`%`
TRIGGER `kobe`.`mytrigger`
BEFORE INSERT ON `kobe`.`tblproducts`
FOR EACH ROW
BEGIN
	if new.PRODNAME='Unassigned' then 
		set new.PRODNAME=NULL;
	end if;
END$$

CREATE
DEFINER=`Admin`@`%`
TRIGGER `kobe`.`myUPDtrigger`
BEFORE UPDATE ON `kobe`.`tblproducts`
FOR EACH ROW
BEGIN
	if new.PRODNAME='Unassigned' then 
		set NEW.PRODNAME=NULL;
	end if;
END$$

[aburningflame]

Re: Dropdown 'BLANK IS NULL' when prechoices are used.

Too dirty of a workaround for my liking.
I used Xbasic in the OnBeforeSQLCommandExecute to get around this. Will show code in a bit.

[Davidk]

Re: Dropdown 'BLANK IS NULL' when prechoices are used.

Have to respectfully disagree on this one... kinda elegant I thought since it seems that passing a null value (outside of a SQL statement) isn't the easiest thing to do.

[aburningflame]

Re: Dropdown 'BLANK IS NULL' when prechoices are used.

Theres some issues with triggers and exporting/importing between workbench and navicat etc etc

[aburningflame]

Re: Dropdown 'BLANK IS NULL' when prechoices are used.

Have to respectfully disagree on this one... kinda elegant I thought since it seems that passing a null value (outside of a SQL statement) isn't the easiest thing to do.

Youd have to create 1 update trigger and 1 insert trigger per grid - I dont know - I dont like this approach.
Here is the approach I used
OnBeforeSQLCommandExecute
In my dropdown I use

Select a value|0

if args.ArgumentNumber("new_client_id")>-1 then
	     if convert_type(args.Find("new_client_id").data,"N")==0 then
       		 args.set("new_client_id",0,sql::argumentusage::inputargument,.t.)
    	end if
	end if
  
    if args.ArgumentNumber("new_main_contact_id")>-1 then
	     if convert_type(args.Find("new_main_contact_id").data,"N")==0 then
	        args.set("new_main_contact_id",0,sql::argumentusage::inputargument,.t.)
	    end if
    end if
    
     if args.ArgumentNumber("new_main_invoice_profile")>-1 then
	     if convert_type(args.Find("new_main_invoice_profile").data,"N")==0 then
	        args.set("new_main_invoice_profile",0,sql::argumentusage::inputargument,.t.)
	    end if
	 end if

The last parameter in args.set is an "IsNull" optional parameter.