Working With Null Values from the DB

[MarkItZero]

Hello,

 

In my program I am reading several DB fields into text boxes on my form.

 

Ex

txtUserName.Text = Cstr(RcrdSt("UserName").Value)
txtIDNum.Text = CDbl(RcrdSt("IDNum").Value)
txtRate.Text = CDbl(RcrdSt("Rate").Value)
txtLocation.Text = CStr(RcrdSt("Location").Value

 

This works great, until there is a record with a null value in one of the fields. And the I get an error....

 

"Cast from type 'DBNull' to type 'String' is not valid"

 

Is there a way I can check to see if the field is NULL before reading it in.

 

I tried...

txtLocation.Text = IIF(IsDBNull(Cstr(RcrdSt("Location"))," ",Cstr(RcrdSt("Location"))

 

But that didnt seem to work.

 

Any Suggestions?

Thanks!

[Nerseus]

The IIF function evaluates ALL parts of the expression. Meaning, it will try the CStr on the column even if it's null.

 

Regardless of that, your code is ALWAYS trying the CStr() on the column, which is bad.

 

If you split out the If everything will be fine. You can easily write a wrapper for the following lines of code as well, if you use them often:

If RcrdSt("Location") = System.DBNull.Value Then
   txtLocation.Text = String.Empty
Else
   txtLocation.Text = RcrdSt("Location").ToString()
End If

 

I also got rid of your use of CStr, which is a backwards compatable feature for old VB users. The new and "better" way is to use the ToString method (available on every object).

 

-Nerseus

[hemenkap]

hi markitzero

 

the error "Cast from type 'DBNull' to type 'String' is not valid"

is raised by Cstr and even in the iif ststement that u have youes you have still made the same mistake. it will still give the same error as you are still trying to convert a null value to string.

 

you can try as nersus said or if you still want to stick to iff then do it this way

 

txtLocation.Text = IIF(IsDBNull(RcrdSt("Location"),"",RcrdSt("Location").ToString())

[MarkItZero]

Thank you both,

I went with Nerseus' suggestion (he hasnt steared me wrong yet)

 

I did have to modify it a bit, not sure why it didnt work your way though.

 

If RcrdSt("Location").Value Is System.DBNull.Value Then
               'Assign Empty String
               txtLocation.Text = String.Empty
           Else
               'Assign DBValue
               txtLocation.Text = RcrdSt("Location").Value()
End If

 

Thanks again

[hog]

I normally use this approach:

 

txtLocation.Text = IIF(IsDBNull(Cstr(RcrdSt("Location")),Nothing,Cstr(RcrdSt("Location"))

[Nerseus]

I can't imagine that code actually works, hog. It's pretty much what MarkItZero first had. You can't do the CStr on the null value to test if it's Null :)

 

Sorry about the bad syntax, MarkItZero. I'm a C# guy and we use "==" to compare values. I didn't realize VB.NET wanted to use Is to compare objects. Glad you got it working!

 

It's too bad VB.NET doesn't support the same type of IIf as C#, which short-circuits the "if" logic. So you can use:

text = (row("col")==System.DBNull.Value ? String.Empty : row("col").ToString());

 

In other words, evaluate the expression and if it's true, only run the TRUE part. In VB's IIf, both the True and False part are always evaluated, which stinks for doing simple checks like the one you want.

 

-Nerseus

[hog]

Yeah I realised I was quick off the mark with that response after I posted it! I was more making the point that I tend to assgin either Nothing or String.Empty rather than use " " which seems messy.

[Nerseus]

I'm guessing that assigning Nothing (or null in C#) might be worse than using String.Empty. If you assign Nothing then check the Text property you'll see it's actually String.Empty.

 

In the following sample, you'll see it prints out Empty. It's just a guess, but I'm thinking there might be a conversion of null to an empty string going on internally. Then again, it may be the exact same thing (hard to tell and I'm too lazy to try and figure out how to see what behind-the-scenes calls are made):

TextBox1.Text = Nothing
If TextBox1.Text Is Nothing Then
   Debug.WriteLine("Nothing")
Else If TextBox1.Text = String.Empty Then
   Debug.WriteLine("Empty")
End If

 

Plus, in my opinion, I'd rather always keep strings (even empty ones) in a Text property. Putting in Nothing might be confusing later. If you assume that the Text property could actually be Nothing, then you'd always have to do TWO checks to see if anything were in a TextBox: one to check for nothing, one to check for empty string.

 

-Nerseus

[Derek Stone]

This is ugly in my opinion, as it boxes and unboxes objects, but it does provide the one-line functionality that C#'s short-circuited if provides.

Private Function RemoveNull(ByRef o As Object) As String
   Return Microsoft.VisualBasic.Switch(o Is Nothing, String.Empty, Not o Is Nothing, o).ToString
End Function

[hemenkap]

hi friends,

 

would like your comments on this one

 

txtLocation.Text = Trim(Cstr(IIF(IsDBNull(RcrdSt("Location"))," ",RcrdSt("Location"))))

 

please match the brackets as i am finding it difficult to do so without the .NET ide.

 

bye

[Nerseus]

Like all the other ones, hemenkap, this won't work in VB.NET. The IIF will evaluate all expressions regardless of whether the expression is True or False. I believe this is mentioned at least twice in this thread alone. But, if you want to try it yourself, paste your code into a test app and let us know how it works.

 

-Nerseus

[APaule]

Another aproach to that problem:

My experience in that case is, that as far as you do not try to format the value before you fill the textbox, you could just use

 

TextBox1.Text = drow("price").ToString

 

Obviously this one converts the column's value automatically to an empty string.

And if you have to format the value you can use

 

TextBox1.Text = String.Format("{0:#,##0.00}", drow("price"))

 

This also works without raising any error when the field is Null. It just returns an empty string!