0

I commonly use the SqlDataAdapter class to retrieve data from SQL Server for consumption in a .NET application. Almost as commonly, while traversing the returned DataSet I often hit null object exceptions because the cell contains a DbBull which translates to Nothing in VB.NET.

Naturally it is better to store empty cells as NULL rather than an empty NVARCHAR or some such data type.

What is the best practice (assuming there is one) for converting these DbNulls to an object in VB.NET. It's most commonly an empty string, but each data type has its own "most appropriate" "Empty" value?

The goal here, is that I don't want to fill my DB with garbage, nor for my application to break when it encounters a DbNull, nor to be required to do something silly like this...

UNDESIRABLE SOLUTION!

For Each row As DataRow In dt.Rows
    Dim cell1 As String = If(IsDBNull(row(0)), "", row(0).ToString)
    Dim cell2 As String = If(IsDBNull(row(1)), "", row(1).ToString)
    Dim cell3 As String = If(IsDBNull(row(2)), "", row(2).ToString)

    Response.Write(String.Format("Cell1: {0}, Cell2: {1}, Cell3: {2}<br />", & _
                   {cell1, cell2, cell3})
Next

Imagine if I had 50 columns, it would be a major hassle to check all these before using them. I'd like a more intelligent solution if one exists.

Side Note:

In SQL Server it's very easy to do something similar, but not quite the same. I normally use something like isnull(@value,@value) to ensure nulls are captured as part of my dataset.

Chiramisu
  • 4,687
  • 7
  • 47
  • 77
  • try to use the [IsNullOrWhiteSpace](http://msdn.microsoft.com/en-us/library/system.string.isnullorwhitespace.aspx) method – spajce Dec 16 '12 at 07:05
  • @spajce I think you're misunderstanding the problem. In order to use that method, there needs to be an object to evaluate. I'm getting the `NullReferenceException` which means there is no object. – Chiramisu Dec 16 '12 at 07:28
  • Nulls can be an utter nuisance sometimes, but the `ToString()` should return an empty string for Null values. It certainly did when I just tested it using VS2008. A cool trick I used in my VB6 days is to simply concatenate the value with empty string, E.g. `"" & Row(0)` – Ciarán Dec 16 '12 at 10:23

1 Answers1

0

Try this:

Dim sValue as String = ""
sValue = If(cmdComanda.ExecuteScalar(), String.Empty)
milo2011
  • 339
  • 1
  • 9
  • 25