Solved

Checking for nulls in SQL record set

Posted on 2007-11-30
21
184 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 55

Assisted Solution

by:Jaime Olivares
Jaime Olivares earned 350 total points
Comment Utility
? 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
Comment Utility
<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
Comment Utility
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
Comment Utility
This is why knowledge sharing is great!  Great job guys, thanks alot!
0
 

Author Comment

by:66chawger
Comment Utility
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
Comment Utility
it should be:
public static string GetStr(object o)
0
 

Author Comment

by:66chawger
Comment Utility
DUH!!!!!   Thanks jaime.....let me go bang my head against the wall for a while :)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:66chawger
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Don't worry, this question has the maximum points (500), so it is not possible to add more.
0
 

Author Comment

by:66chawger
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Jaime, everything works great now.  Thanks alot for the assistance.  It definitely helped me think outside the box :)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now