check if string is null in sql

Question

User-1599356091 posted

i have a listview with items and i can check some category, some item details, or returns a null image link a load a default image but i miss something because command is wrong

SELECT IIF(ISNULL(Atleta.url_imagem), 'no_photo.jpg', Atleta.url_imagem) as URLImagem, Atleta.id, Atleta.nome,  Categoria.nome AS Expr1, Atleta.id_categoria FROM Atleta INNER JOIN Categoria ON Atleta.id_categoria = Categoria.id WHERE ((? = 0) or (Atleta.id = ?)) and ((? = 0) or (Atleta.id_categoria = ?)) order by Atleta.nome

Answer 1

User-422529730 posted

try this

SELECT ISNULL(Atleta.url_imagem,'no_photo.jpg') as urlimagem, Atleta.id, Atleta.id_categoria, Categoria.nome AS Expr1, Atleta.id_categoria 
FROM Atleta INNER JOIN Categoria ON Atleta.id_categoria = Categoria.id WHERE 
((? = 0) or (Atleta.id = ?)) and ((? = 0) or (Atleta.id_categoria = ?)) 
order by Atleta.nome

No need to use IIF

Answer 2

User-1716253493 posted

You can simply replace the null image in ASP.NET page

<asp:Image ID="Image1" runat="server" 
      ImageUrl='<%# isdbnull(Eval("url_imagem")) ? "no_photo.jpg" : Eval("url_imagem") %>' />

Answer 3

User-1599356091 posted

try this

SELECT ISNULL(Atleta.url_imagem,'no_photo.jpg') as urlimagem, Atleta.id, Atleta.id_categoria, Categoria.nome AS Expr1, Atleta.id_categoria 
FROM Atleta INNER JOIN Categoria ON Atleta.id_categoria = Categoria.id WHERE 
((? = 0) or (Atleta.id = ?)) and ((? = 0) or (Atleta.id_categoria = ?)) 
order by Atleta.nome

No need to use IIF

 

it givies me syntax error near ?

Answer 4

User3866881 posted

it givies me syntax error near ?

Hi,

Please use your suitable field or column name propertly instead of "?", because answerer doesn't know you real columnName.

Answer 5

User-1599356091 posted

what i need is a querystring thats why i use ?

Answer 6

User3866881 posted

what i need is a querystring thats why i use ?

Please use parameter token and QueryStringParameter, and here's a sample:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.aspx

Answer 7

User-1599356091 posted

i'm using this command to check if is dbnull , but i also need to check if its empty... how can i do that here in image and link eval's

and also this is not loading the default no_photo image...

 <a href='<%# Eval("photo") == DBNull.Value ? "/images/no_photo.png" : Eval("photo") %>' rel="lightbox[on]" title="+ <a href='<%# Eval("Link") %>'>Info</a>"><asp:Image id="img" runat="server" ImageUrl='<%#Eval("photo", "~/images/uploads/{0}") %>' /> </a>

Answer 8

User3866881 posted

Hi,

for multiple checkings, I think you can just write down a function to cope with this:

<a href='<%# MyFunc(Eval("photo"))%>

And then

protected string MyFunc(object obj)
{
 if(obj==null || obj==DbNull.Value)
 return something;
 else if (obj.ToString().Trim()=="")
 return something;
 else
 return obj.ToString();
}