Setting initial NULL values to false for checkbox

[TomLeMes]

I have a SQL Server backend and use SPT to create buffered local cursors (bit shakey on some of the terminology there, but I think that's about right). When adding a new record I use: APPEND BLANK and this works fine. I can then use either TABLEUPDATE() or TABLEREVERT() to update the backend or not. Again, this works fine.

The problem I have is that when I create a new record the default values (when updated to the server) are <NULL>. Not a problem for fields that the user changes, but a blank checkbox can often stay blank, so the user doesn't touch it. But when the record is retrieved later the checkbox shows NULL (shaded). I found some code somewhere that attempts to loop through all the fields in the new record and kind of initialise them according to their datatype:

FOR i = 1 TO FCOUNT(ALIAS())
   fldType = VARTYPE(EVALUATE(FIELD(i)))

   DO case
      CASE fldType = 'C'      && Character field set to empty string
          fld = FIELD(i) 
          &fld = ''
      CASE fldType = 'L'      && Logical field set to false
         fld = FIELD(i)
         &fld = .F.            
   ENDCASE
NEXT

I guess the idea being that then the user can, say leave a checkbox off and the SQL database will result in a 0, rather than a <NULL>. The above doesn't work (fields still show up null). Is there a better way of dealing with this?

Tom

 

[bborissov]

The above didn't works because you can not assign a value to the field, you must REPLACE it.
One possible solution is to set all fields of remote DB not to accept NULLs and set DEFAULT values to them according to their types and the on creating new recors just insert only these values the user changes. Other way when you create a new record (no metter when in CA or in RV) to put all values accordingly:

INSERT INTO YourRVorCA VALUES (ISNULL(Field1,.f.), ISNULL(Field2,""),ISNULL(Field3,0)...)

Borislav Borissov

 

[Mike Lewis]

Tom,

Do you have control over the table definition on the server? If so, an easy solution would be to give the field in question a default constraint of 0. I think you'd have to leave it set to nullable (i.e. include the keyword NULL in the field definition), otherwise you'll get an error when you try to send the new record.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TomLeMes]

Excellent - thank you both for the suggestions. I have control of the table so I've just set it to default to 0. Thanks!

Tom