Cast from type DBNull to type String is not valid

[The Cheat]

I have an application where I am filling text boxes with data from a table. Every now and then, not all the fields in the table are going to contain data. For instance some records will have notes and others won't. Right now if I try and do this (below) I get Cast from type DBNull to type String is not valid

Try
                cnn.Open()
                dr = cmd.ExecuteReader
                While dr.Read()
                    tbRoNumb.Text = dr("RoNumber")
                    tbLastName.Text = dr("LastName")
                    tbFirstName.Text = dr("FirstName")
                    tbVehicle.Text = dr("Vehicle")
                    tbInsurer.Text = dr("Insurer")
                    tbColorCode.Text = dr("ColorCode")
                    tbAmount.Text = dr("Amount")
                    tbDateEntered.Text = dr("DateEntered")
                    tbDeliverDate.Text = dr("DeliveryDate")
                    tbDeliverTime.Text = dr("DeliveryTime")
                    tbPartsDate.Text = dr("PartsDate")
                    tbF.Text = dr("Frame")
                    tbM.Text = dr("Mech")
                    tbA.Text = dr("Align")
                    tbB.Text = dr("Body")
                    tbP.Text = dr("Paint")
                    tbReleaseDay.Text = dr("ReleaseDate")
                    tbReleaseTime.Text = dr("ReleaseTime")
                    CB1.Checked = dr("CB1")
                    CB2.Checked = dr("CB2")
                    CB3.Checked = dr("CB3")
                    CB4.Checked = dr("CB4")
                    CB5.Checked = dr("CB5")
                    tbMemo.Text = dr("Notes")
                End While
                cnn.Close()

How do I do this and still allow for nulls in one or more fields?

[brendalisalowe]

Try this:

If ISDBNull(dr("LastName")) Then
tbLastName.Text = ""
Else
tbLastName.Text = dr("LastName")
End If

Good luck!

Brenda

[The Cheat]

So I will have to do an If Then for every field? Well any field that may be null. Is there any other way to accomplish this?

[Qualm]

So I will have to do an If Then for every field? Well any field that may be null. Is there any other way to accomplish this?

I've run into this same thing many times. Yes, every field that could be null. No, I'm not aware of any other way to do it, but I'd love to discover one.

[corwin_ranger]

Create a function that incapsulates the IF statement that returns either an empty string or the value of the field. Then just call that function from the appropriate potentially null fields.

If this is an issue that you might have in more than one form in your project, add a module and define the function in the module making it available throughout the project.

[brendalisalowe]

That is the only way I know of. That is what I do in my programs. Lots of copy paste I guess. Maybe someone smarter knows a better way.

Brenda

[The Cheat]

Ok thanks for the help.

[eL_NiNo]

You can use :

tbLastName.Text = dr("LastName") & ""

If you know you want a string, this way, you will end up with "" if your value in DB is null.

[Qualm]

Aha! That'll save a lot of typing, thanks

[brendalisalowe]

Hey, that works great! Love it! Thanks!

Brenda

[EricDalnas]

I do it like this:
dr.Item("fieldname").ToString

If the field isn't string type field than I use the function in the link listed below. It is ugly at the moment but maybe I'll make it pretty one day.

http://www.mredkj.com/vbnet/FixNull.html