Not displaying null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not displaying null

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-15 : 11:18:26

I have null data in the database for the datefield. When I run this stored procedure in my .net program 01/01/00 displays in the textbox. How can I get just the textbox to not have anything in it if null is in the database?


ALTER procedure [dbo].[GetClaimant

@Claim char(6),
@PIC char(2)

As


SELECT    Claim, PIC, DocPmt, LAF, PCInd, FinalLaf, convert(varchar, DteIn,1) as DteIn, Remarks, 
BicAdj,NoBIC, IA, FD, convert(varchar, DteToFO,1) as DteToFO, convert(varchar, DteBack,1) as DteBack,
Results, Amt
FROM       TestData
WHERE     claim = @claim and pic = @pic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 11:25:30

quote:
Originally posted by JJ297

I have null data in the database for the datefield. When I run this stored procedure in my .net program 01/01/00 displays in the textbox. How can I get just the textbox to not have anything in it if null is in the database?

ALTER procedure [dbo].[GetClaimant

@Claim char(6),
@PIC char(2)

As

SELECT Claim, PIC, DocPmt, LAF, PCInd, FinalLaf, convert(varchar, DteIn,1) as DteIn, Remarks,
BicAdj,NoBIC, IA, FD, convert(varchar, DteToFO,1) as DteToFO, convert(varchar, DteBack,1) as DteBack,
Results, Amt
FROM TestData
WHERE claim = @claim and pic = @pic

not to select null values in the table for a column.

add this condition in your whare statements.

claim = @claim and pic = @pic
and datefieldcolumn is not null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 11:29:36

quote:
Originally posted by haroon2k9

quote:
Originally posted by JJ297

I have null data in the database for the datefield. When I run this stored procedure in my .net program 01/01/00 displays in the textbox. How can I get just the textbox to not have anything in it if null is in the database?

ALTER procedure [dbo].[GetClaimant

@Claim char(6),
@PIC char(2)

As

SELECT Claim, PIC, DocPmt, LAF, PCInd, FinalLaf, convert(varchar, DteIn,1) as DteIn, Remarks,
BicAdj,NoBIC, IA, FD, convert(varchar, DteToFO,1) as DteToFO, convert(varchar, DteBack,1) as DteBack,
Results, Amt
FROM TestData
WHERE claim = @claim and pic = @pic

not to select null values in the table for a column.

add this condition in your whare statements.

claim = @claim and pic = @pic
and datefieldcolumn is not null

what if he wants other fields which do have values?

actually that should be handled at your front end. the solutions in sql would involve changing datatype which is ugly. so look for default date value at front end and replace with your preffered value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-15 : 11:35:44

Tried that but it doesn't bring up any data

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-15 : 11:37:37

You are correct Visakhm I do have some dates in that column and want it displayed when it's there and nothing if it's not.

How would I do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 11:39:14

quote:
Originally posted by JJ297

You are correct Visakhm I do have some dates in that column and want it displayed when it's there and nothing if it's not.

How would I do this?

did you try like this

NULLIF(datefield,'19000101')

or are you doing convertion in .net?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-15 : 11:49:47

I tried this the first one works (I have to convert to other fields) the other two come up as 1/1/2000 12:00:00 AM

How do I get rid of the time too?



SELECT    Claim, PIC, DocPmt, LAF, PCInd, FinalLaf, NULLIF(dteIn,'19000101') as dteIn, Remarks, 
BicAdj,NoBIC, IA, FD, NULLIF(dtetofo,'19000101') as dteToFO, NULLIF(DteBack,'19000101') as DteBack,
Results, Amt
FROM       TestData
WHERE     Claim = @Claim and pic = @pic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:08:41

quote:
Originally posted by JJ297

I tried this the first one works (I have to convert to other fields) the other two come up as 1/1/2000 12:00:00 AM

How do I get rid of the time too?

SELECT Claim, PIC, DocPmt, LAF, PCInd, FinalLaf, NULLIF(dteIn,'19000101') as dteIn, Remarks,
BicAdj,NoBIC, IA, FD, NULLIF(dtetofo,'19000101') as dteToFO, NULLIF(DteBack,'19000101') as DteBack,
Results, Amt
FROM TestData
WHERE Claim = @Claim and pic = @pic

for stripping off time see

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

are you doing any casting converting at front end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-15 : 12:24:30

No I was only doing it in the stored procedure. Where should I do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:26:37

if its for formatting then do it at front end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-03-15 : 12:44:50

Okay I will thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-16 : 03:16:31

Why are you converting date to varchar?



ALTER procedure [dbo].[GetClaimant

@Claim char(6),
@PIC char(2)

As


SELECT    Claim, PIC, DocPmt, LAF, PCInd, FinalLaf, convert(varchar, DteIn,1) as DteIn, Remarks, 
BicAdj,NoBIC, IA, FD, DteToFO, DteBack,
Results, Amt
FROM       TestData
WHERE     claim = @claim and pic = @pic

Use the above and do the formation at front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources