Home

ODP.NET - Handling Nulls when Executing with ExecuteScalar

Author: Packt Publishing Published on: 5th Apr 2008 | Last Updated on: 9th Apr 2008

ODP.NET - Handling Nulls when Executing with ExecuteScalar

The most important issue to remember is that ExecuteScalar simply returns an object type of data. The object refers to any data type within .NET. If the data type of your variable matches with the type of object returned by ExecuteScalar, an implicit (automatic) conversion takes place.

Ads

There would not be a problem as long as the data types match. However, it would be a problem if the result is NULL. Let us have an example that accepts an employee number from the user and gives his or her commission:

Sample Code

Imports Oracle.DataAccess.Client
Public Class Form12
Private Sub btnGetCommission_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetCommission.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
User Id=scott;Password=tiger")
Try
'create the command object
Dim cmd As New OracleCommand("SELECT comm FROM _
emp WHERE empno=" & Me.txtEmpno.Text, cn)
'open the connection from command
cmd.Connection.Open()
'execute the command and get the single value
'result
Dim result As Double = cmd.ExecuteScalar
cmd.Connection.Close()
cmd.Dispose()
'display the output
MessageBox.Show("Commission: " & result)
Catch ex As Exception
'display if any error occurs
MessageBox.Show("Error: " & ex.Message)
'close the connection if it is still open
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Sub

In the highlighted statement above, we are expecting a numeric (or double) value as the result. If the ExecuteScalar returns a double value, it would never be a problem. What if it returns a NULL? The following is the error you would receive:

Ads

To deal with the above error, we may have to include our own condition to test against nulls in the output. Just replace the highlighted code above with the following two statements and it should work fine now: