CODEWITHSUNDEEP
c#nullsqldatareader
Jason
Jason

Reputation: 4957

how to check if a datareader is null or empty

I have a datareader that return a lsit of records from a sql server database. I have a field in the database called "Additional". This field is 50% of the time empty or null.

I am trying to write code that checks if this field isnull. The logic behind this is: If the field "Additional" contains text then display the info otherwise hide the field.

I have tried:

if (myReader["Additional"] != null)
{
    ltlAdditional.Text = "contains data";
}
else
{
     ltlAdditional.Text = "is null";
}

The above code gives me this error:

Exception Details: System.IndexOutOfRangeException: Additional

Any help would be greatly appreciated...

See Also:

Check for column name in a SqlDataReader object

Upvotes: 63

Views: 228662

Answers (13)

esenkaya
esenkaya

Reputation: 378

Best thing to get this done in query. However, if query bond to other functions than checking DBNull.Value in while loop would address the issue.

if (reader.HasRows)
{
   while (reader.Read())
   {
      (reader["Additional"] != DBNull.Value) ? "contains data" : "is null";
   }
}

Upvotes: 0

cnom
cnom

Reputation: 3241

Try this simpler equivalent syntax:

ltlAdditional.Text = (myReader["Additional"] == DBNull.Value) ? "is null" : "contains data";

Upvotes: 1

Rodrigo Medeiros da Maia
Rodrigo Medeiros da Maia

Reputation: 1

This

Example:

objCar.StrDescription = (objSqlDataReader["fieldDescription"].GetType() != typeof(DBNull)) ? (String)objSqlDataReader["fieldDescription"] : "";

Upvotes: 0

Kemal Can ÖZÇELİK
Kemal Can ÖZÇELİK

Reputation: 724

if (myReader.HasRows) //The key Word is **.HasRows**

{

    ltlAdditional.Text = "Contains data";

}

else

{   

    ltlAdditional.Text = "Is null Or Empty";

}

Upvotes: 16

catalyst
catalyst

Reputation: 81

I also use OleDbDataReader.IsDBNull()

if ( myReader.IsDBNull(colNum) ) { retrievedValue = ""; }
else { retrievedValue = myReader.GetString(colNum); }

Upvotes: 8

Ahmed Fahmy
Ahmed Fahmy

Reputation: 97

This is the correct and tested solution

if (myReader.Read())
{

    ltlAdditional.Text = "Contains data";
}
else
{   
    ltlAdditional.Text = "Is null";
}

Upvotes: 8

Rob
Rob

Reputation: 638

I also experiencing this kind of problem but mine, i'm using DbDataReader as my generic reader (for SQL, Oracle, OleDb, etc.). If using DataTable, DataTable has this method:

DataTable dt = new DataTable();
dt.Rows[0].Table.Columns.Contains("SampleColumn");

using this I can determine if that column is existing in the result set that my query has. I'm also looking if DbDataReader has this capability.

Upvotes: 0

Robert Durgin
Robert Durgin

Reputation: 1840

if (myReader["Additional"] != DBNull.Value)
{
    ltlAdditional.Text = "contains data";
}
else
{
     ltlAdditional.Text = "is null";
}

Upvotes: 86

Kevin Nelson
Kevin Nelson

Reputation: 7663

I haven't used DataReaders for 3+ years, so I wanted to confirm my memory and found this. Anyway, for anyone who happens upon this post like I did and wants a method to test IsDBNull using the column name instead of ordinal number, and you are using VS 2008+ (& .NET 3.5 I think), you can write an extension method so that you can pass the column name in:

public static class DataReaderExtensions
{
    public static bool IsDBNull( this IDataReader dataReader, string columnName )
    {
        return dataReader[columnName] == DBNull.Value;
    }
}

Kevin

Upvotes: 12

Shiva
Shiva

Reputation: 20935

@Joe Philllips

SQlDataReader.IsDBNull(int index) requires the ordinal number of the column. Is there a way to check for nulls using Column Name, and not it's Ordinal Number?

Upvotes: 1

Jason
Jason

Reputation: 4957

AMG - Sorry all, was having a blond moment. The field "Additional" was added to the database after I had initially designed the database.

I updated all my code to use this new field, however I forgot to update the actual datareader code that was making the call to select the database fields, therefore it wasn't calling "Additional"

Upvotes: -1

Kirtan
Kirtan

Reputation: 21695

In addition to the suggestions given, you can do this directly from your query like this -

SELECT ISNULL([Additional], -1) AS [Additional]

This way you can write the condition to check whether the field value is < 0 or >= 0.

Upvotes: 1

Joe Phillips
Joe Phillips

Reputation: 51100

First of all, you probably want to check for a DBNull not a regular Null.

Or you could look at the IsDBNull method

Upvotes: 3

Related Questions