Deafult NULL not working

I am using SQL Server 2000. I have a Column with DataType int and default value specified as (null). But, With Insert or Update if the column value is Blank, 0 is getting inserted instead of the desired NULL.

Thanks

Is there, by chance, a trigger on this table?|||

NO. There is no trigger on this Table.

|||Well... create a complete DDL script for this table and post it here - something must be there.

Also, how you make a insert / update - directly or via some kind of stored proc? There may be a preprocessing in there that you miss, for example.|||

What exactly do you mean by "column value is blank". If you are explicitly trying to force a blank into the field then yes, it is going to get assigned as zero:

create table dbo.testo
( rid int,
x int default (null)
)

insert into dbo.testo select 1, ' '

insert into dbo.testo (rid) select 1

select * from dbo.testo

/*
rid x
-- --
1 0
1 NULL
*/

If, however, you are wanting to insert a row and allow the default to occur you must do something similar to what I hilighted in red

If you want to UPDATE to the default value, you can use syntax something like this:

update dbo.testO
set x=default
where rid =1

|||

YES. The Column Value is getting evaluated to '' as the user did not enter anything for the field on the form. Is there any way '' can be evaluated to NULL instead of 0.

- vmrao

|||declare @.p1 varchar(255)

set @.p1 = ''

insert into Mytable (rid, myintcol)
select 10, nullif(@.p1, '')
|||Thanks. NULLIF worked.

Deafult data for Image field

Hi,

I am storing my upoaded images in sql server. However, if the user does not upload an image, a broken link is shown in the page.
All that shows up in the DB field is <Binary>.

Can we set this field to a default value?
Is it possible to view this binary data.

I would like a default blank gif to be returned if no image is uploaded.

Regards,
JBISNULL(yourField, @.Default) ? I rarely use the image field, so i'm not sure if this works.|||What if an image is removed at a later date? What would you like to display then?

My gut feeling is that it would be best to not handle this through the database, but through one of your business objects.

With the database, you can make use of the ISNULL, but you cannot use an image data type for a local variable, so KraGiE's suggestion won't work as written. I was messing around with the NorthWind database and came up with this sort of thing:

-- this is for testing, should have a default photo sitting in a table somewhere and that table should be used instead of the @.myTest table
DECLARE @.myTest table (DefaultPhoto image)
INSERT INTO @.myTest SELECT photo from employees WHERE employeeID = 1
SELECT
photo,
DefaultPhoto,
ISNULL(photo,DefaultPhoto) AS DisplayPhoto
FROM
employees
LEFT OUTER JOIN
@.myTest ON employees.photo IS NULL

I'm sure there are 100 better ways to do this :-)

Terri|||ISNULL(Photo, SELECT Photo From Employees Where EmployeeID = @.DefaultPhotoID)

:)