Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Question Unanswered: Can someone post a working sample of this?

    I need to write an UPDATE using a SET where I fill in a NULL if a default
    field is blank. Like the below:


    UPDATE table SET birthdate = { expression | default | null }

    I simply don't know the correct syntax (even after reading the online
    books). I really need a working sample with anything close to this. I
    can't get it to work and end-up with the birthdate pulling from a text
    field and only placing a null in the table when no birthday is given.


    Thanks in advance.


    BobbyJ

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I thnk you want COALESCE ( expression [ ,...n ] ), it returns the first non-null argument or Null if all argumetns are null.
    Last edited by Paul Young; 01-24-03 at 14:23.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    5

    Re: Can someone post a working sample of this?

    Originally posted by BobbyJ
    I need to write an UPDATE using a SET where I fill in a NULL if a default
    field is blank. Like the below:


    UPDATE table SET birthdate = { expression | default | null }

    I simply don't know the correct syntax (even after reading the online
    books). I really need a working sample with anything close to this. I
    can't get it to work and end-up with the birthdate pulling from a text
    field and only placing a null in the table when no birthday is given.


    Thanks in advance.


    BobbyJ
    Hi

    Try
    UPDATE table
    set birthdate = isnull(yourtextitem,' ')

  4. #4
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Smile

    All-

    Thanks for the replies. I will try it out this weekend.

  5. #5
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Question

    I tried this from a prompt and got the same results (1/1/1900).

    Any other ideas?

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Which one did you try? For sure walshx's will not work. Inserting a '' value into a date/time field results in the '1/1/1900' that you see. I thought Paul's suggestion would work, but I get the following error when doing a test:

    Code:
    None of the result expressions in a CASE specification can be NULL.
    The test code I ran was:

    Code:
    declare @temp varchar(20)
    
    select @temp = coalesce(null,null,null)
    
    select @temp as results
    I even tried it with ansi_warnings off with no luck. I ran into a similar issue with a vb script i was running. My solution (bad as it is) was to append an update script that searched for 1/1/1900 and changed those entries to null.

    I am sorry, but I don't have any good answers right now.

    Hugh Scott

    Originally posted by BobbyJ
    I tried this from a prompt and got the same results (1/1/1900).

    Any other ideas?

  7. #7
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Arrow

    It's funny you should mention that. I was thinking the exact same thing
    before I started doing any of this. I figured I could write a server service
    to periodically check the table and replace empty values or 1/1/1900 with nulls. I'll do this for now and keep searching for a simpler way as
    I go. Thanks.

    BobbbyJ

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    1.select isnull(isnull(A,B),null) works

    2.Look at this code. Periodic running of code is not needed.

    Code:
    
    create table XXXX
    (
     idX int identity(1,1) primary key 
    ,X int null
    )
    GO
    
    create trigger ti_XXXX_I on XXXX 
    instead of insert as 
    insert XXXX(X) 
    select isnull(inserted.X,0) from inserted
    GO
    create trigger ti_XXXX_U on XXXX 
    instead of update as 
    update t set
    t.X=isnull(i.X,0)
    from XXXX t
    join inserted i on t.idx=i.idx
    GO
    
    insert XXXX values(NULL) 
    insert XXXX values(3) 
    select * from XXXX
    update XXXX set X=NULL where X=3
    select * from XXXX
    GO
    
    drop table XXXX
    GO
    
    

  9. #9
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24
    I'm sorry, I forgot to mention I'm doing this all within Visual Studio .NET (VB) and SQL2000 standard calls. I'm not too familiar with straight SQL
    without referring to a book. Can I save a null in an

    UPDATE table column = value and if value is empty save it as a null?

    BobbyJ

  10. #10
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Empty means NULL is SQL.

  11. #11
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Wink

    I'm referring to empty as VB sees a textbox with nothing typed in it or
    where the length is 0 bytes.

    All the coding I've done always places a 1/1/1900 in SQL. I don't have
    the code in front of me (it's at work but looks like this - from memory).


    UPDATE tblEMPLOYEE SET BIRTHDATE = ISNULL(txtBIRTH.text,'') WHERE EMPLOYEEID = form.EMPLOYEEID


    There are more fields being update, I just selected one for this sample
    in VB coding. The second half of the ISNULL I even replaced with
    system.dbnull.value and get the same result. Can't figure out what's
    wrong. I suppose VB never makes it a null as SQL needs to see it (just
    an empty string coming in - at times).

    BobbyJ

  12. #12
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I am not familiar with VB(.NET). In VB(6) Textbox property Text cannot store NULL values. If you want to use NULL, try variable for example Text1IsNull as boolean or Textbox.BackColor indication.

  13. #13
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24

    Cool

    I follow you (I think). Is this what you're saying:

    Example:

    Dim xBIRTHDATE as string (strings can be null if I recall)

    'Birthdate is a textbox on a webform
    if BIRTHDATE.TEXT <> STRING.EMPTY then
    xBIRTHDATE = BIRTHDATE.TEXT
    endif

    'So at this point if the textbox is empty xBIRTHDATE is still set to null
    'and it is safe to save.

    UPDATE table SET dbBIRTHDATE = ISNULL(xBIRTHDATE,'')

    Correct??

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bobbyj, can you have a look at the table definition please

    if the birthdate column is defined NOT NULL you will never get a null in there

    alternatively, it may have DEFAULT 0 which would explain the 1/1/1900 (this is the date that a day number of 0 converts to)

    so before you write any weird script, check whether the database will even let you put a null in there

    as for the syntax, try this --

    script logic to generate update statement:
    update table
    set foo ='bar'
    if birthdate form field is empty
    , birthdate = null
    else
    , birthdate = form field value
    endif

  15. #15
    Join Date
    Jan 2003
    Location
    Washington, DC
    Posts
    24
    Yes. It does allow nulls and I'll give that script a shot (maybe later tonight).

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •