Peter's Gekko

Sponsors

The Lounge

Syndication

Recent Posts

Tags

View more

News

Links

Archives

Advertisement

Peter's Gekko » System.DBNull.Value != null
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
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

 

Posted 04-12-2004 12:41 PM by pvanooijen

[Advertisement]

Comments

Michael Russell wrote Bugs in the routine...
on 04-12-2004 9:56 AM
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.
Peter van Ooijen wrote re: System.DBNull.Value != null
on 04-12-2004 10:07 AM
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.
Scott_NO_@_SPAM_Tripleasp.net (Scott Watermasysk) wrote RE: System.DBNull.Value != null
on 04-12-2004 1:10 PM
You should also be able to do:

string result = cmd.ExecuteScalar() as string;

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

return result;


Peter van Ooijen wrote re: System.DBNull.Value != null
on 04-12-2004 1:52 PM
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.
Naveen Katara wrote re: System.DBNull.Value != null
on 12-29-2004 6:18 AM
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
Peter van Ooijen wrote re: System.DBNull.Value != null
on 12-30-2004 3:02 AM
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
Leon Bemmelmans wrote re: System.DBNull.Value != null
on 04-25-2006 9:53 AM
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
pvanooijen wrote re: System.DBNull.Value != null
on 04-26-2006 4:16 AM
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
Greg W wrote re: System.DBNull.Value != null
on 08-01-2007 12:45 AM

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?

pvanooijen wrote re: System.DBNull.Value != null
on 08-20-2007 10:45 AM

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

Peter's Gekko wrote Back to basics, just reading some data from a DB
on 11-21-2007 6:51 AM

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

Community Blogs wrote Back to basics, from the DB to a simple domain object
on 12-03-2007 4:18 PM

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

Add a Comment

(required)  
(optional)
(required)  
Remember Me?