SQLDataReader: handling null values

Question

Some tables I'm dealing with have zero values ​​and cause errors. So far, ive tried several solutions to deal with zeros without success.

Here are code examples from my efforts so far;

If (r("datemodified").Equals(DBNull.Value)) Then datemodified = String.Empty Else datemodified = (r("datemodified")) End If 

and

 If r.HasRows Then datemodified = (r("datemodified")) Else datemodified = String.Empty End If 

and;

  If r("datemodified") = Nothing Then datemodified = String.Empty Else datemodified = (r("datemodified")) End If 

and;

 If r.IsDBNull("datemodified") Then datemodified = String.Empty Else datemodified = (r("datemodified")) 

and through sql;

 Select isnull(datemodified, '') 

The end result is an IndexOutOfRangeException.

here is sql;

 select datemodified, maintainedby, email, hitcount from grouping where id = @footid 

ps, I fulfilled the request and it works fine (i.e. all cols exist)

Answer 1

To handle a null value in code, you can use the IsDBNull method:

 Dim index As Integer = r.GetOrdinal("datemodified") If r.IsDBNull(index) Then datemodified = String.Empty Else datemodified = r(index) End If 

To handle a null value in SQL, you must specify a field name so that you can access it by name in a data reader:

 select datemodified = isnull(datemodified, '') 

Answer 2

An exception to IndexOutofRangeException is that the column you are trying to retrieve does not exist in the result set.

When you access an ordinal or column through SqlDataReader, you must specify the column index or column name. In your script, you need to specify an alias to return the SQL column.

 SELECT ISNULL(datemodified, '') AS [datemodified]