[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

What to do when .GetDecimal(#) encounters a null value?

Posted on 2009-02-18
5
Medium Priority
?
886 Views
Last Modified: 2012-05-06
Hi Experts,

I have an SQL field that can contain a decimal value or may contain nulls. Currently, when it contains null and I try to retrieve it, an exception occurs. I would like to be able to handle either situation without an exception occurring if possible. When I encounter a nulll value, I would then like to be able to set the associated textbox on my webpage to blank  - instead of zero.

My code to retrieve the field is attached.

Any suggestions?
Dim decAPYRate As Decimal
                    Try
                        decAPYRate = .GetDecimal(21)
                    Catch ex As Exception
                        decAPYRate = 0
                    End Try

Open in new window

0
Comment
Question by:taduh
5 Comments
 
LVL 27

Expert Comment

by:nmarun
ID: 23673336
Two things:

1. In your sql, change that column as:
     SELECT COALESCE(theDecimalColumn, '').... from table1...
2. In your code
     Dim decAPYRate As Decimal
     Try
           If sqlDataReader("decColumn") = string.Empty then
                    decAPYRate = 0
           Else
                    decAPYRate = .GetDecimal(sqlDataReader("decColumn"))
           End If
     Catch ex As Exception
           
      End Try
0
 
LVL 27

Expert Comment

by:nmarun
ID: 23673347
Slight change

1. In your sql, change that column as:
     SELECT COALESCE(theDecimalColumn, '') as [decColumn] .... from table1...
2. In your code
     Dim decAPYRate As Decimal
     Try
           If sqlDataReader("decColumn") = string.Empty then
                    decAPYRate = 0
           Else
                    decAPYRate = .GetDecimal(sqlDataReader("decColumn"))
           End If
     Catch ex As Exception
           
      End Try
             
 
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23673389
or you could just modify the sql

select isnull(decColumn,0)
0
 
LVL 15

Expert Comment

by:aibusinesssolutions
ID: 23676688
Yeah I would just use ISNULL(decAPYRate, 0) in the SQL command.  

ISNULL ( check_expression , replacement_value )

Arguments
check_expression
    Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
    Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expression.
0
 

Accepted Solution

by:
taduh earned 0 total points
ID: 23682677
Found my own workaround as posted below. Thanks anyway for all suggestions.
 'Must read as a GetValue(20).ToString so that it will not throw an exception if null.
                    'If you determine its not null, then you can .GetDecimal(20) to get it in decimal form.
 
                    Dim strAPRrate As String = ""
                    Dim decAPRrate As Decimal = 0
                    Dim usedecAPR As Boolean = False
                    Try
                        strAPRrate = .GetValue(20).ToString
                        If strAPRrate <> "" Then
                            decAPRrate = .GetDecimal(20)
                            usedecAPR = True
                        End If
                    Catch ex As Exception
                        strAPRrate = ""
                        decAPRrate = ""
                        MsgBox("Encountered exception retrieving APR Rate - Set to blank")
                    End Try

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question