JesusDamnGod JesusDamnGod - 1 year ago 53 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 = "...."
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
Catch ex As SqlException
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

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 Source

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"
        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)