3

I have got this code below:

Dim lJobName As String = ""
SQLCommand.CommandText = "Select JobName from Jobs where Id = @Id "
SQLCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int))
SQLCommand.Parameters(0).Value = var_id
lJobName = SQLCommand.ExecuteScalar()

Issue how to catch if no records are found?

gnat
  • 6,213
  • 108
  • 53
  • 73
Conrad Jagger
  • 643
  • 6
  • 19
  • 31

4 Answers4

4

Whatever the manual says, comparing to Nothing does not work. So If lJobName Is Nothing will NOT be triggered when the result set is empty.

Comparing to DBNull.Value DID work for me:

If lJobName Is DBNull.Value Then
    'Do something with the error condition
Else
    'Do something with lJobName which contains a valid result.
End If

It is worth noting that when the result set is empty (i.e. no records were found), this is not an "error".

finch
  • 549
  • 1
  • 6
  • 18
2

I try to avoid comparing a string to Nothing, even though it does work in VB.

The Visual Basic .NET runtime evaluates Nothing as an empty string; that is, "". The .NET Framework, however, does not, and will throw an exception whenever an attempt is made to perform a string operation on Nothing.

Plus pseudocoder's answer wont work as currently shown (oJobname is never set to anything)

Dim lJobName as String = String.Empty
Dim oJobName as object = SqlCommand.ExecuteScalar()

If oJobName Is Nothing Then
    'Do something with the error condition
Else
    lJobName = oJobName.ToString
End If
PatFromCanada
  • 2,738
  • 1
  • 27
  • 27
  • String.Empty is not the same thing as Nothing, which is why I didn't use `If String.IsNullOrEmpty(lJobName) Then` – pseudocoder Nov 06 '12 at 15:05
  • Wow, major cut and paste error, i have changed my post to show what I intended. – PatFromCanada Nov 06 '12 at 15:10
  • I see what you're going for there. Still, `String` holds a value of `Nothing` just fine. The only reason not to use it is if you want to implement a pattern that would work well for non-nullable value types such as `Integer` or `Decimal`, which is exemplified by your answer. You should really correct your inaccurate statement about "evaluates Nothing as an empty string" though. – pseudocoder Nov 06 '12 at 15:13
1

I think it is possible to handle this situation in the statement of the query:

SELECT ISNULL(FieldID,"0") FROM TableName

and subsequently in the program validate the result:

if then ...... else ...... endif

Oskar
  • 11
  • 1
0

ExecuteScalar() returns Nothing if there is an empty result set, which should be preserved when assigning into a string, so I would try this:

Dim lJobName as String = String.Empty
lJobName = SqlCommand.ExecuteScalar()
If lJobName Is Nothing Then
    'Do something with the error condition
Else
    'Do something with lJobName which contains a valid result.
End If

Of course this doesn't help you if you cause a SqlException, but it should handle the problem of no rows found in the result set.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

pseudocoder
  • 4,314
  • 2
  • 25
  • 40
  • 1
    Not really DBNull.Value. ExecuteScalar return `The first column of the first row in the result set, or a null reference (Nothing in Visual Basic)` – Steve Nov 06 '12 at 14:45
  • Thanks for correcting my inability to read documentation. I updated my answer. – pseudocoder Nov 06 '12 at 14:50
  • Yes better to use Is Nothing than IsDbNull also, lJobName = oJobName is an implicit conversion and will not work with option strict, better to call .ToString on oJobName – PatFromCanada Nov 06 '12 at 14:51