CODEWITHSUNDEEP
vb.net
user6363145
user6363145

Reputation: 39

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?

Upvotes: 0

Views: 104

Answers (2)

Steve
Steve

Reputation: 216293

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)

Upvotes: 2

p3tch
p3tch

Reputation: 1495

If Not IsDBNull(SqlDR) Then
    lblResultadoTotal.Text = SqlDR.GetValue(0)
End If

Maybe, the actual error message would help

Upvotes: 0

Related Questions