Exforsys.com
 
Home Tutorials ODP.NET
 

ODP.NET - Handling Nulls when Executing with ExecuteScalar

 

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
  1. Imports Oracle.DataAccess.Client
  2.  
  3. Public Class Form12
  4.  
  5.      Private Sub btnGetCommission_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetCommission.Click
  6.         'create connection to db
  7.             Dim cn As New OracleConnection("Data Source=xe; _
  8.                         User Id=scott;Password=tiger")
  9.                 Try
  10.                 'create the command object
  11.                 Dim cmd As New OracleCommand("SELECT comm FROM _
  12.                             emp WHERE empno=" & Me.txtEmpno.Text, cn)
  13.                     'open the connection from command
  14.                 cmd.Connection.Open()
  15.                     'execute the command and get the single value
  16.                 'result
  17.                     Dim result As Double = cmd.ExecuteScalar
  18.                 cmd.Connection.Close()
  19.                     cmd.Dispose()
  20.                     'display the output
  21.                     MessageBox.Show("Commission: " & result)
  22.                     Catch ex As Exception
  23.                     'display if any error occurs
  24.                     MessageBox.Show("Error: " & ex.Message)
  25.                     'close the connection if it is still open
  26.                     If cn.State = ConnectionState.Open Then
  27.                         cn.Close()
  28.                     End If
  29.             End Try
  30.     End Sub
Copyright exforsys.com


 


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.



Read Next: ODP.NET - Handling Nulls when Working with OracleDataReader



 

 

Comments



Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape