Solved

Checking for nulls in SQL record set

Posted on 2007-11-30
21
187 Views
Last Modified: 2013-12-16
I am wanting to know if there is a way to check for null values in a record set for all columns at one time, instead of doing if a == null then or else for each column?  I call a SP that returns one row (always). My code behind looks like this without any null checking:
                       
try
{
  oRs = oCmd.ExecuteReader();
  if (oRs.Read())
 {
     QSNumber = oRs["QS#"].ToString();
     ProductName = oRs["Product_Name"].ToString();
			:
			:
			:

Open in new window

0
Comment
Question by:66chawger
  • 9
  • 8
  • 4
21 Comments
 
LVL 55

Accepted Solution

by:
Jaime Olivares earned 350 total points
ID: 20383618
no, you cannot check all, but you can design your own function, like:

public static GetStr(object o)
{
     return o==null ? "" : o.ToString();
}

So you can use as:
    QSNumber = GetStr(oRs["QS#"]);
 
0
 
LVL 4

Assisted Solution

by:donjon56
donjon56 earned 150 total points
ID: 20383653
you will likely want to modify the above return to read as below.

If a column contains a null value in a reader it will be reflected as a DBNull.Value.


return o==DBNull.Value ? "" : o.ToString();

Open in new window

0
 

Author Comment

by:66chawger
ID: 20383654
Is the ? for .NET 2.0?   I am using 1.1, unfortunately we have not upgraded yet.  Also

what does the "" represent after the ?.   Can I include to return "N/A" if the value is null?
I like the function though, just taking me some time to get up to speed again on .NET after being in JAVA for so long.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 55

Assisted Solution

by:Jaime Olivares
Jaime Olivares earned 350 total points
ID: 20383702
? is the ternary operator, it is present in every C# version (not .net related)
And yes, you can replace the ""  with  "n/a"
0
 
LVL 4

Expert Comment

by:donjon56
ID: 20383711
<test> ? <true value> : <false value>

It works in 1.1

It performs the test and if true, returns the true value otherwise returns the false value.
0
 
LVL 4

Assisted Solution

by:donjon56
donjon56 earned 150 total points
ID: 20383727
Also

return o==DBNull.Value ? "" : o.ToString();

is syntatically the same as

if (o==DBNull.Value)
    return "";
else
    return o.ToString();
 
0
 

Author Closing Comment

by:66chawger
ID: 31411963
This is why knowledge sharing is great!  Great job guys, thanks alot!
0
 

Author Comment

by:66chawger
ID: 20384722
Ok, receiving "Class, Struct, or interface method must have a return type for

public static GetStr(object o)

My namespace is QSNumberInfo
then public class is GetQSNumberInfo : System.Web.UI.Page
then private void for page load
then public void GetQSNumInfo(object sender, System.EventArgs e)

I tried to place the new function at the end. I have encountered this message before when a return type is missing.
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20384788
it should be:
public static string GetStr(object o)
0
 

Author Comment

by:66chawger
ID: 20385038
DUH!!!!!   Thanks jaime.....let me go bang my head against the wall for a while :)
0
 

Author Comment

by:66chawger
ID: 20403499
Jaime, I have a bit of a situation.  This is in regards to the solution you provided me last Friday.  In our Dev and QA SQL DB's the following code works great checking for nulls prior to setting as string:
     return 0==DBNull.Value ? "n/a" : o.ToString();
In our prod SQL DB, even though I see "NULL" represented in some of the SQL column fields, this code is not picking it up?    can I a logical or to also check for isempty?.....  e.g.,   || isEmpty()  ??????   This is the only thing I can think of if the these columns are not actually null, but are emtpy?
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20403541
you can do this:

public static GetStr(object o)
{
     if (o is string)
           return string.IsNullOrEmpty((string)o) ? "n/a" : o.ToString();
    else
           return o==null ? "n/a" : o.ToString();
}
0
 

Author Comment

by:66chawger
ID: 20403581
ahhh!!!  did not think of the IsNullOrEmpty!!!!!   that kills two birds with one stone!  I know I submitted this as a comment, but is there a way to award you points?   by the way it took until now to find this problem out as it only happens in prod, and my code was not promoted until yesterday......
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20403694
Don't worry, this question has the maximum points (500), so it is not possible to add more.
0
 

Author Comment

by:66chawger
ID: 20403699
Jaime, I hate to ask this, is the IsNullOrEmtpy definition part of .NET 2.0?  I am working in .NET 1.1.  IsNullOrEmpty is not included as a definition for string.   I work in both environments, however, this particular one is .NET 1.1, my apologies
0
 
LVL 4

Expert Comment

by:donjon56
ID: 20403884
It is a 2.0 method.

This might work better for you:

    public static string GetStr(object o)
    {
        string s = o as string;

        return (s != null && s.Length > 0 ? s : "n/a");
    }

this tries to cast o as a string (if it is not a string then s will be null)

if s is not null and the length is greater than zero, then return the string.  Otherwise return "n/a"
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20403893
it is just compatible with .net 2.0 and 3.x
Alternative:

public static GetStr(object o)
{
     if (o==null)
           return "n/a";
     else
     {
          if (o is string)
                return ((string)o).Length==0 ? "n/a" : o.ToString();
          else
                return o.ToString();
     }
}
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20403905
this will work with non-empy string but filled with spaces:

public static GetStr(object o)
{
     if (o==null)
           return "n/a";
     else
     {
          if (o is string)
                return ((string)o).Trim().Length==0 ? "n/a" : o.ToString();
          else
                return o.ToString();
     }
}
0
 

Author Comment

by:66chawger
ID: 20404211
Jamie, the second if in your example is basically checking for length of 0 and if true set to "n/a" correct?
With the addition of the trim() this will still work with non-empty strings but as you said strings filled with spaces, correct?  

So trim or with out trim, will this cover all the bases, e.g., NULL values and Empty strings?
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20404284
>>So trim or with out trim, will this cover all the bases, e.g., NULL values and Empty strings?
yes, I think so, empty string are equal to strings with length=0
0
 

Author Comment

by:66chawger
ID: 20404596
Jaime, everything works great now.  Thanks alot for the assistance.  It definitely helped me think outside the box :)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question