in Search

Do you Twitter? Follow us @CodeBetter

Peter's Gekko

public Blog MyNotepad : Imho { }

System.DBNull.Value != null

Spent the most of easter digging through heaps of sql. So here's another “in-stinker” which almost had me fooled again.

Inside of a component I'm not afraid to work directly with sqlcommands. Setting up a datadapter and a dataset would be an overkill when the database will return just a scalar value. The result returned by the ExecuteScalar method can be somewhat misleading at first sight. Take this code

public string CustomerName(int custId)
{
SqlCommand cmd = new SqlCommand(string.Format("SELECT Name FROM Customers WHERE idCust = {0}", custId), sqlConnectionToSchaakBondDB);
object result = cmd.ExecuteScalar();

   if (result == null)
      return "Customer not found";
   if (result == System.DBNull.Value)
      return "Customer found but name is null";
   return (string) result;

Testing the result only against null could give a quite misleading result. In the second case the method does return a value but the content of this value is null. Which is not the same as not returning a value at all.

Peter

Published Apr 12 2004, 12:41 PM by pvanooijen

Comments

Michael Russell said:

By the way, you also forgot the closing parenthesis on your WHERE clause.

By the way, if this was just example code, please ignore this next portion. My question here is why you're returning strings instead of throwing exceptions. You say that you don't mind working with SQL inside of components, then you're obviously assuming that you are going to be getting passed valid information (in this case, a valid custId). If the record doesn't exist, then either the input was incorrect or you have a data integrity issue. Either way, this is an unexpected event.

# April 12, 2004 9:56 AM

Peter van Ooijen said:

Blush.. You're absolutely right, I updated the sql. It is indeed just some example code, adapted from something I'm working on.
Throwing an exception would indeed make a lot more sense in a real life situation, these results are not something you would want on a mailing label or the like.

# April 12, 2004 10:07 AM

Scott_NO_@_SPAM_Tripleasp.net (Scott Watermasysk) said:

You should also be able to do:

string result = cmd.ExecuteScalar() as string;

if(result == null)
{
return "NOT FOUND";
}

return result;

# April 12, 2004 1:10 PM

Peter van Ooijen said:

Mmm, a gentle typecast. Nice.
But the reason why the name was not found : "customer does not exist" versus "customer does exist but does not have a name", would get lost. Could be important to know.

# April 12, 2004 1:52 PM

Naveen Katara said:

While u try to insert a DBNull.Value in the varchar field of database it inserts the '' which is not NULL
for EX:-
iif(txt.Text="", DBNull.Value,txt.Text)
Result = ''
That shows System.DBNull.Value != NULL

# December 29, 2004 6:18 AM

Peter van Ooijen said:

Naveen: The column in the db should have the "allow nulls" flag set to true. I think that's not the case with your coulmn.you

# December 30, 2004 3:02 AM

Leon Bemmelmans said:

string result = cmd.ExecuteScalar() as string;

I would really like the result to be an empty string "" if the resulttype is DBNULL Will the above solution do the trick ?

Leon

# April 25, 2006 9:53 AM

pvanooijen said:

hi Leon,

An empty string is not the same as null. Which is not the same as dbnull. What you want do can be done as follows:

object o = cmd.ExecuteScalar();
if (o != null && o != DBNull.Value)
result = Convert.ToString(o);

Takes an extra step

# April 26, 2006 4:16 AM

Greg W said:

I've got a stored proceedure:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)

If User.Identity.IsAuthenticated Then

e.Command.Parameters("@ownerid").Value = Membership.GetUser().ProviderUserKey

Else

e.Command.Parameters("@ownerid").Value = DBNull.Value

End If

End Sub

Stored Procedure finds Albums with OwnerID.

When a person who is not logged in looks at the page they see all the albums except the private ones as spec'ed.

/* get the values for the date and row */

SELECT @rows = (@pageNum-1) * @pageSize

SET ROWCOUNT @rows

SELECT @keyid=albumid FROM albums

WHERE(Albums.private = 0) OR (Albums.private = 1) AND (Albums.ownerid = @ownerid)

ORDER BY albumid ASC

END

My questions is: No ablums should be displayed?

# August 1, 2007 12:45 AM

pvanooijen said:

I'm not sure about a clear answer on this. In my experience it depends on these things

- DB server brand

- Is the parameter marked as nullable ?

- Is the column you are selecting on marked as nullable ?

You need a couple of clear tests to sort this out

# August 20, 2007 10:45 AM

Peter's Gekko said:

Are you already drowning in the enormous amount of TLA's, tools, utilities, methodologies and whatever

# November 21, 2007 6:51 AM

Community Blogs said:

This post is an extended and revised version of " Back to basics, just reading some data from a

# December 3, 2007 4:18 PM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

Peter's Gekko

System.DBNull.Value != null

Comments

Michael Russell said:

Peter van Ooijen said:

Scott_NO_@_SPAM_Tripleasp.net (Scott Watermasysk) said:

Peter van Ooijen said:

Naveen Katara said:

Peter van Ooijen said:

Leon Bemmelmans said:

pvanooijen said:

Greg W said:

pvanooijen said:

Peter's Gekko said:

Community Blogs said:

Leave a Comment

This Blog

Syndication

Recent Posts

Tags

News

Links

Archives