[RESOLVED] Conversion from type 'DBNull' to type 'String' is not valid

[Alexandru_mbm]

Hello to you all,

I have searched almost the all forum to resolve my problem but without any positive effect.

I have a problem reading from the database some values.

If one of the value is empty i get this error message:
Conversion from type 'DBNull' to type 'String' is not valid.

How can i pass thru this problem ?

Please give me a suggestion and how can i optimize this code better!

Dim denumire_furnizor, tip_unitate, statut_juridic As String

        conn.Open()

        Dim sql_1 = "SELECT denumire FROM unitatea_economica"
        cmd = New OleDb.OleDbCommand(sql_1, conn)
        denumire_furnizor = CStr(cmd.ExecuteScalar)

        Dim sql_1_1 = "SELECT tip_unitate FROM unitatea_economica"
        cmd = New OleDb.OleDbCommand(sql_1_1, conn)
        tip_unitate = CStr(cmd.ExecuteScalar)

        Dim sql_1_2 = "SELECT statut_juridic FROM unitatea_economica"
        cmd = New OleDb.OleDbCommand(sql_1_2, conn)
        statut_juridic = CStr(cmd.ExecuteScalar)

        Me.lbl_furnizor.Text = tip_unitate & " " & denumire_furnizor & " " & statut_juridic

        If conn.State <> ConnectionState.Closed Then

            conn.Close()

        End If

Thanks alot and please to escuse me for my bad english!

[Shaggy Hiker]

The queries you have written will return just the first value from the table you are querying (if it is a table), which seems a bit peculiar. Usually there's a WHERE clause in there to reduce the number of records, but that's not the cause of the problem. The problem comes from the fact that one or all of these fields have a Null value in the first record.

I can't say that I have ever tried to check for Null on an ExecuteScalar call before, but I would think....that it would be a pain. If you simply check whether cmd.ExecuteScalar returns a Null, that would require two calls. Better would be to put the data into an object and test the object. However, if you have access to the database, and control over the design of it, the best solution would be to not have Null (empty) values in those fields. Give them a default value of pretty much anything. I think even an empty string ("") would do.

[Al42]

denumire_furnizor = CStr("" & cmd.ExecuteScalar)

should work.

[bmahler]

I ran into the same issue and wrote a little function

    Public Function FixNull(ByVal o As Object) As Object
        If IsDBNull(o) Then
            Return Nothing
        Else
            Return o
        End If
    End Function

Then you can just write

denumire_furnizor = FixNull(cmd.ExecuteScalar)

[bgmacaw]

Use String.Concat

lbl_furnizor.Text = String.Concat(tip_unitate, " ", denumire_furnizor, " ", statut_juridic) 

[Alexandru_mbm]

Many thanks to all of you!!!

Shaggy Hiker

Yes it's a table with only one row.
Thank you for the indications!

My problem is now solved...

I have used "bmahler" solution. I dont know if it is the best one but it works and seems to be more simple.

Many thanks to all of you again!!!