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.

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:

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:

Dim result As Object = cmd.ExecuteScalar
If IsDBNull(result) Then result = 0

You can observe from the above two lines that we are receiving the value in the form of an object and assigning a value zero if it is null.

Comments

Subscribe via RSS

Get Daily Updates via

Sponsored Links

ODP.NET Tutorials

ODP.NET - Handling Nulls when Executing with ExecuteScalar

ODP.NET - Handling Nulls when Executing with ExecuteScalar

Comments

Post Your Comment:

Sponsored Links

Subscribe via RSS