?
Solved

Checking for nulls in SQL record set

Posted on 2007-11-30
21
Medium Priority
?
196 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 1400 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 600 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 55

Assisted Solution

by:Jaime Olivares
Jaime Olivares earned 1400 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 600 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

831 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