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.

BobbyJI thnk you want COALESCE ( expression [ ,...n ] ), it returns the first non-null argument or Null if all argumetns are null.|||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,' ')|||All-

Thanks for the replies. I will try it out this weekend.|||I tried this from a prompt and got the same results (1/1/1900).

Any other ideas?|||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:

None of the result expressions in a CASE specification can be NULL.

The test code I ran was:

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?|||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|||1.select isnull(isnull(A,B),null) works

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

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|||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|||Empty means NULL is SQL.|||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|||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.|||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??|||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|||Yes. It does allow nulls and I'll give that script a shot (maybe later tonight).

Thanks,|||I think I've figured out what's wrong. In Visual Studio .NET VB, it doesn't
set variables to NULL but something called NOTHING. When NOTHING
is passed to ISNULL, ISNULL thinks it's an empty string and not a NULL.

Do I need to declare my VS.NET variables as SQLTYPES in order to get
a true NULL? At first I thought this was a simple SQL issue but starting to
think otherwise.

BobbyJ|||Try
IIF(YourVar is nothing,"NULL","'+replace(YourVar,"'","''")+'")
for string variables to pass variable to sql.|||I will try this later today (I'm actually off today - after the SuperBowl)
when I remote in to check mail. We had system problems from the
worm virus that started out last Saturday (so hopefully the systems
are available - SQL).

Thanks,

BobbyJ|||I tried it but my compiler has issues with the syntax of the replace
statement. It's getting hung up on the single ' marks (thinks it's a comment of sorts). I really appreciate you and other taking time to
help with this. It's GREATLY appreciated.

BobbyJ|||Corrected in VB6, I dont know if this syntax can be used in .NET.

IIf(YourVar Is Nothing, "NULL", "'" + Replace(YourVar, "'", "''") + "'")|||Thanks. I'll try again.|||No matter what happens, the YOURVAR is always returned as NOTHING
and not NULL. It must be an issue with .NET. Your logic looks fine as did
my old code but I can't get a NULL for a return value. I think I need to
do some research on how to obtain a NULL value in the .NET. I suppose
I may have to look deeper into the SQLTYPES as I know there should be
a DBNULL.VALUE I can load into SQL in order to get a NULL in the database. This whole thing is really strange.

BobbyJ|||SOLVED! Code I used:

Dim strRANKDATE As String

If Me.txtGradeDate.Text <> String.Empty Then
strRANKDATE = "RANK = '" & Me.txtGradeDate.Text & "', "
Else
strRANKDATE = "RANK = NULL ,"
End If

'Building SQL string

strUpdateStatement = "UPDATE tblEMPLOYEE SET " & _
"FIRSTNAME = '" & Me.txtFirstName.Text & "', " & _
"MIDDLENAME = '" & Me.txtMiddle.Text & "', " & _
"LASTNAME = '" & Me.txtLastName.Text & "', " & _
"SUFFIX = '" & Me.ddlSuffix.SelectedItem.Text & "', " & _
"NICKNAME = '" & Me.txtNickname.Text & "', " & _
"SERVICE = '" & Me.ddlService.SelectedItem.Text & "', " & _
"GRADE = '" & Me.ddlGrade.SelectedItem.Text & "', " & _
strRANKDATE & _
"RANK = '" & Me.ddlRankTitle.SelectedItem.Text & "', " & _

etc. Now the NULL is properly added to the table when the user
removes the date from date fields on the webform. The If else
can be modied to a shorter IIF or a function can be made of it.

BobbyJ