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 its 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 cant imagine that code actually works, hog. Its pretty much what MarkItZero first had. You cant do the CStr on the null value to test if its Null

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

Its too bad VB.NET doesnt 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 its true, only run the TRUE part. In VBs 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]

Im guessing that assigning Nothing (or null in C#) might be worse than using String.Empty. If you assign Nothing then check the Text property youll see its actually String.Empty.

In the following sample, youll see it prints out Empty. Its just a guess, but Im 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 Im 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, Id 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 youd 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 wont 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 columns 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!