JesusDamnGod JesusDamnGod - 6 months ago 12
Vb.net Question

Validation to get null values

I am trying to make a validation where if there is no value on a label the application will run normally. To explain better I will show you the the code that I am using and then explain it

Private Sub ShowTotalResult()
SQLCon = New SqlConnection
SQLCon.ConnectionString = "...."
Try
SQLCon.Open()
Dim Query As String
Query = "SELECT
CAST(SUM(CAST(Filesize as float)) / 1024 / 1024 AS DECIMAL(10,2))
FROM infofile"
SqlCmd = New SqlCommand(Query, SQLCon)
SqlDR = SqlCmd.ExecuteReader
If lblResultadoTotal.Text <> "" Then
If SqlDR.Read() Then
lblResultadoTotal.Text = SqlDR.GetDecimal(0)
Exit Sub
End If
End If
SQLCon.Close()
Catch ex As SqlException
MsgBox(ex.Message)
Finally
SQLCon.Dispose()
End Try
End Sub


What this piece of code does is giving me the total in MB of what I got on the database. To make some tests I deleted all the info from database and when I've tried to run the application it gives me error on this line
lblResultadoTotal.Text = SqlDR.GetDecimal(0)
because there is no value on that label. So to turn that around what could I do?

I've tried to use an
If lblResultadoTotal.Text <> "" Then
lblResultadoTotal.Text = SqlDR.GetValue(0)
End If


This is the query that I am using to get the values from database

SELECT
CAST(SUM(CAST(Filesize as float)) / 1024 / 1024 AS DECIMAL(10,2))
FROM infofile


This is the error
Data is Null. This method or property cannot be called on Null values.

but this does not work. Do you have any solution?

Answer

You cannot call GetDecimal if the field is NULL because internally the GetDecimal method tries to cast the null value to a decimal. You can read the warning in the Remarks section of the GetDecimal page on MSDN

It is easy to fix using SqlDataReader.IsDbNull

' A call to SqlDataReader.Read is always required to position '
' the reader on the first record returned by your query'
if SqlDR.Read() then 
    if SqlDR.IsDbNull(0) Then

        lblResultadoTotal.Text = "0,00"
    else 
        lblResultadoTotal.Text = SqlDR.GetDecimal(0)

    End If
End If

However, I am a bit perplexed from all those casts inside your query. I would get the raw value and try to convert the values obtained in VB.NET code where it will be more easy to do it. (Need to test for performances)