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

[dotnetnubie]

Hello all. I am experiencing problems with DbNull values being returned from a data request. In fact, I can't seem to even test for the problem without errors. The process starts with a parent form displaying a datagrid of selections. Clicking a link on that page invokes the child form, sending a URL variable as the parameter. In the page load function, a query is performed and text boxes on the form are set with values from the selected row of the database. But, if a field in the database has a DbNull value, I receive an error. In the example code below, PHONE_B is the field with the DbNull value. I try to test for the null value, but it errors-out on that as well (I will bold the test statement for clarity).

I hate to have to test every field for a Dbnull value. Is there an easy way to handle this?

-- START CODE
PrivateSub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load

Dim MyUrl As Uri = Request.Url

Dim emp_id AsString

Dim dsrow AsInteger

Me.company_logo.ImageUrl = company_logo_path

Dim tmp_string AsString

Dim tmp_null AsBoolean

'Put user code to initialize the page here

emp_id = Mid(MyUrl.Query, 2)

'Me.name.Text = emp_id

OnErrorGoTo 0

Me.Ds_fpersonnel_detail1.Clear()

Me.SqlSelectCommand1.CommandText = "SELECT COMPANY_ID, CATEGORY, EMP_ID, NAME, ADDRESS, CITY, STATE, ZIP, PHONE_H, PHONE_B, PHONE_F, EMAIL, POSITION, EMP_ID, COMPANY_EMP_ID, DATE_APPLIED, DATE_HIRED, DATE_TERMINATED, LOCATION from fs_personnel where COMPANY_ID = " & company_id & " and EMP_ID = " & emp_id

Me.SqlDataAdapter1.Fill(Ds_fpersonnel_detail1)

Me.DataBind()

If Ds_fpersonnel_detail1.fs_personnel.Count > 0 Then

dsrow = CType(Session("RecordPos"), Integer)

Me.name.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).NAME

Me.address.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).ADDRESS

Me.city.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).CITY

Me.state.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).STATE

Me.zip.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).ZIP

Me.phone_h.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_H

If Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_B Is DBNull.Value Then

Me.phone_b.Text = ""

Else

Me.phone_b.Text = Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_B

EndIf

tmp_string = Ds_fpersonnel_detail1.fs_personnel(dsrow).CATEGORY

If tmp_string = "1" Then

Me.DropDownList_category.SelectedValue = "1"

ElseIf tmp_string = "2" Then

Me.DropDownList_category.SelectedValue = "2"

ElseIf tmp_string = "3" Then

Me.DropDownList_category.SelectedValue = "3"

ElseIf tmp_string = "4" Then

Me.DropDownList_category.SelectedValue = "4"

EndIf

Me.Label3.Text = "HR Detail Page"

Else

Me.Label3.Text = "Record Not Found!"

EndIf

EndSub

-- END CODE


Thanks in advance,
dotnetnubie

 

[TechGnome]

I think
If Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_B Is DBNull.Value Then

should be
If Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_B = DBNull.Value Then

You don't specify what the error you get with the line is, so I can only guess at the solution (I haven't imported System.Brain.Reader).

-tg

 

[PLH]

Just add .ToString after every database statement value and it will be ok. The VB.Net does not show members after you placed the dot but it is ok you just type it. It will convert DBNull value to a string value that you can test and it will not give an error if the value is nothing.

 

[TechGnome]

Except that's not *allways* OK to do.... in our case there, there is a distinct difference between a Null value and an empty string (which is what Null will .ToString as), and we have to treat them differently.

I'm not saying it's wrong to do that, just depending on the application it may or may not work.

-tg

 

[PLH]

<should be
If Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_B = DBNull.Value Then>


Actually VB.Net dose not support DBNull. It sgests to use isNothing instead. The best way to go in my app is to convert the value to a string.
And empty string is Null too so I don't see any difference!

 

[dotnetnubie]

Thanks to all that have replied thus far.

1. The editor tells me that I must use the "is" operator and not the "=" operator when I do a comparision with String and DBNull. I can't even compile with the "=" operator in place

2. The exact error I receive on when the statement is executed is:

"Cast from type 'DBNull' to type 'String' is not valid." The exception details are: "System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid". I can include more error detail if it would be helpful. (I love the
System.Brain.Reader comment!!!!)

3. I attempted to add ".ToString" to the end of the database statements, but it still received an error when the problem line of code is executed.

4. I tried "if IsNothing(Ds_fpersonnel_detail1.fs_personnel(dsrow).PHONE_B) = True then", but that bombed out with the same error as well.

Any other sugggestions? Again, thanks so much for all the help!!!!!

dotnetnubie

 

[PLH]

Look this code works in my app. If I don't add ".ToString" I will get the same error that you are getting when the database filed is empty; becouse "logoImagePath" is string type.

[size=2]logoImagePath = Ds1.Company.Rows(0)("Image_Path").ToString
 
'insted of 
if Ds_fpersonnel_detail.fs_personne(dsrow).PHONE_B is DBNull.Value Then
[color=#0000ff][size=1][size=2][color=#000000][/color][/size] 
[/size][/color]'try this
if Ds_fpersonnel_detail.fs_personne(dsrow)("PHONE_B").ToString = "" Then
 
[color=#0000ff][size=1]

[/size][/color]
[/size]

 

[dotnetnubie]

That did the trick. In fact, I didn't even have to do a test anymore. Just used that syntax to do the assignment, and voila...!

Thanks so much for your assistance!
dotnetnubie