Display Null from database in asp.net

Question

I want to display every thing in database to asp.net. But some of my value are null and some not. When I load to the page that null it will cause error. May I know how can I fix this problem? My mean is to display everything from database doesn't care it is NULL or having content.

SELECT m.PersonID, m.Picture,m.PersonName, t.title, t.Fileupload, t.contentBody,      t.dateInserted FROM person m, thread t  WHERE m.PersonID = t.PersonID AND t.threadID = @TID

The t.FileUpload in my database some of row are null and some contain contain. And the error is

system.invalidcastexception unable to cast object of type 'system.dbnull' to type 'system.string'

You may find this useful: dev.mysql.com/doc/refman/5.0/en/working-with-null.html — Wjdavis5
– Wjdavis5, Commented Nov 21, 2013 at 15:48

speti43 · Accepted Answer · 2013-11-21 16:11:46Z

1

Use Standard SQL coalesce function.

SELECT a.PersonID, 
Coalesce (a.Picture, othercolumn, anothercolumn, 'if all are null') as Picture
from tableA a

it will return the first not null value.

Coalesce in mySQL

or just simply:

Coalesce (a.Picture, '') as Picture

This will prevent invalidcastexception

edited Nov 21, 2013 at 16:11

answered Nov 21, 2013 at 15:57

speti43

3,0622 gold badges22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2769165 Over a year ago

but then now having another problem System.IndexOutOfRangeException. Could not find specific column......

speti43 Over a year ago

use alias: Coalesce (a.Picture, '') as Picture

speti43 Over a year ago

No probs, enjoy it ;)

speti43 Over a year ago

Accept the answers in your questions, if you are satisfied with them please.

Collectives™ on Stack Overflow

Display Null from database in asp.net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related