Conversion from type 'DBNull' to type 'String' is not valid.

[Alexandrumbm]

Hello to you all,

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!

 

[Borvik]

The quickest way around your problem is to do check for IS DBNull on each ExecuteScalar call:

Dim sql_1_1 = "SELECT tip_unitate FROM unitatea_economica"
cmd = New OleDb.OleDbCommand(sql_1_1, conn)
Dim o As Object = cmd.ExecuteScalar
If o Is DBNull.Value Then
    tip_unitate = ""
Else
    tip_unitate = CStr(o)
End If

As for optimizing I notice all you sql statements are just pulling a different column from the same table. The first thing I would do is to consolidate those SQL statements into 1 statement: SELECT denumire, tip_unitate, statut_juridic FROM unitatea_economica.

Also as ExecuteScalar only returns the first row I would modify the SELECT statement to only return the first row. Assuming MSSQL it would end up: SELECT TOP 1 denumire, tip_unitate, statut_juridic FROM unitatea_economica.

The I would use a DataAdapter and fill a DataTable (I use ODBC, but the process is similar for OleDb):

Dim dt As New System.Data.DataTable("Returned")
cmd = New System.Data.Odbc.OdbcCommand(sql, SqlConn)
Dim da As New System.Data.Odbc.OdbcDataAdapter(cmd)
da.Fill(dt)

SqlConn is just my sql connection.

Once my datatable is filled I then cycle through each object in it checking for DBNull and if so, setting to an empty value for that columns data type.

        For o As Integer = 0 To dt.Rows.Count - 1
            For u As Integer = 0 To dt.Columns.Count - 1
                If dt.Rows(o).Item(u) Is DBNull.Value Then
                    dt.Rows(o).Item(u) = GetNullValue(dt.Columns(u))
                End If
            Next
        Next

'GetNullValue function
    Public Function GetNullValue(ByVal dc As DataColumn) As Object
        Dim retVal As Object
        Dim colName As String = dc.ColumnName
        Dim dT As String = dc.DataType.ToString()
        'Dim bT As String = dc.DataType.BaseType.ToString()
        'Dim uT As String = dc.DataType.DeclaringTyp
        If dc.DataType Is GetType(Int64) Then '***
            retVal = CType(0, Int64)
        ElseIf dc.DataType Is GetType(Byte).MakeArrayType Then '***
            retVal = New Byte() {0}
        ElseIf dc.DataType Is GetType(Boolean) Then '***
            retVal = False
        ElseIf dc.DataType Is GetType(String) Then
            retVal = ""
        ElseIf dc.DataType Is GetType(DateTime) Then
            retVal = New DateTime(1, 1, 1)
        ElseIf dc.DataType Is GetType(Decimal) Then '***
            retVal = CDec(0)
        ElseIf dc.DataType Is GetType(Double) Then '***
            retVal = CDbl(0)
        ElseIf dc.DataType Is GetType(Single) Then '***
            retVal = CSng(0)
        ElseIf dc.DataType Is GetType(Int16) Then '***
            retVal = CType(0, Int16)
        ElseIf dc.DataType Is GetType(Byte) Then '***
            retVal = CByte(0)
        ElseIf dc.DataType Is GetType(Guid) Then
            retVal = Guid.Empty
        ElseIf dc.DataType Is GetType(Int32) Then '***
            retVal = CType(0, Int32)
        Else
            retVal = ""
        End If
        Return retVal
    End Function

Now that each object in the datatable is not DBNull it won't have any DBNull problems and you can just assign your values:

denumire_furnizor = CStr(dt.Rows(0).Item("denumire"))
tip_unitate = CStr(dt.Rows(0).Item("tip_unitate"))
statut_juridic = CStr(dt.Rows(0).Item("statut_juridic"))

Hope that helps you out.