Checking for nulls in SQL record set
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();
:
:
:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<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.
It works in 1.1
It performs the test and if true, returns the true value otherwise returns the false value.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is why knowledge sharing is great! Great job guys, thanks alot!
ASKER
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.
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.
it should be:
public static string GetStr(object o)
public static string GetStr(object o)
ASKER
DUH!!!!! Thanks jaime.....let me go bang my head against the wall for a while :)
ASKER
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?
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?
you can do this:
public static GetStr(object o)
{
if (o is string)
return string.IsNullOrEmpty((stri
else
return o==null ? "n/a" : o.ToString();
}
public static GetStr(object o)
{
if (o is string)
return string.IsNullOrEmpty((stri
else
return o==null ? "n/a" : o.ToString();
}
ASKER
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......
Don't worry, this question has the maximum points (500), so it is not possible to add more.
ASKER
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
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"
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"
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();
}
}
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();
}
}
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=
else
return o.ToString();
}
}
public static GetStr(object o)
{
if (o==null)
return "n/a";
else
{
if (o is string)
return ((string)o).Trim().Length=
else
return o.ToString();
}
}
ASKER
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?
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?
>>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
yes, I think so, empty string are equal to strings with length=0
ASKER
Jaime, everything works great now. Thanks alot for the assistance. It definitely helped me think outside the box :)
ASKER
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.