Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / Languages / C# / March 2008

Tip: Looking for answers? Try searching our database.

Handling null values returned via SQL ExecuteScalar() query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Anodes - 26 Mar 2008 21:05 GMT
How do I test for and handle a return of null in the following code?

               try
               {
                   OleDbConnection connection = new
OleDbConnection(connectionString);
                   connection.Open();
                   OleDbCommand command = new OleDbCommand(SQL_Query,
connection);
                   scalarReturned =
command.ExecuteScalar().ToString();
                   command.Dispose();
                   connection.Close();
               }
               catch (Exception ex)
               {

               }

This is part of a data-handling class I'm building, and I use this
method for pulling single values from one of my lookup tables where
the structure is fixed. It works fine; however I want to be able to
handle when the query returns null...of course trying to convert null
ToString() generates an error.

I suppose I could just trap that error, but I'd rather design it so no
error is generated.

Thanks.
Marc Gravell - 26 Mar 2008 21:15 GMT
IIRC - ExecuteScalar returns null if no value is returned, and
DBNull.Value if a null is returned. So test the value for these two
cases before going further. You could also try using
Convert.ToString(...) which handles null (don't know about DBNull).

Note you can test for DBNull using "foo is DBNull".
Note also that it is more reliable to use "using" than Dispose().

Marc
Jeroen Mostert - 26 Mar 2008 21:32 GMT
> IIRC - ExecuteScalar returns null if no value is returned, and
> DBNull.Value if a null is returned. So test the value for these two
> cases before going further. You could also try using
> Convert.ToString(...) which handles null (don't know about DBNull).

Using Convert.ToString() is rather pointless, since Convert.ToString(null)
returns null (the reason for this is the rather interesting overload
resolution that C# uses) while Convert.ToString(DBNull.Value) returns the
empty string. It's unlikely this is what you want, and even if it is,
readers of your code are probably not going to grasp immediately what's
happening. Better to test explicitly.

> Note also that it is more reliable to use "using" than Dispose().

To be precise, "using" just calls Dispose(), but in a finally block.

Signature

J.

Marc Gravell - 26 Mar 2008 21:57 GMT
IIRC it also handles the scenario when the disposable object is
initialized as null.

But yes - getting disposed on exception is the point I meant by "more
reliable".

Marc
Anodes - 27 Mar 2008 15:10 GMT
> IIRC it also handles the scenario when the disposable object is
> initialized asnull.
[quoted text clipped - 3 lines]
>
> Marc

Final working code:
object oScalarReturned = new Object();
oScalarReturned = command.ExecuteScalar();
if (oScalarReturned is DBNull || oScalarReturned == null)
   scalarReturned = "";
else
   scalarReturned = oScalarReturned.ToString();

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.