Vb.net - Handling null or no values returned from SQL Command object

Question

we got this sql query:

Select EmployeeDesignation from Employee where EmployeeName = Paramater1

(Parameter1 is value is passed to this)

We are using this statement: lEmployeeDesignation = _SQLCommand.ExecuteScalar()

3 scenario's:

Returns the employee Designation (record exists in table)
No value is set in database for Parameter1 - so should return NULL (record exists but has no value)
No employee record exists so returns nothing (record doesn't exists)

I'm struggling with 2 and 3 scenario - incase of Scenario 3 we want the application to fail, but struggling how to trap this error. regards

Steve · Accepted Answer · 2012-12-10 09:47:14Z

1

lEmployeeDesignation = _SQLCommand.ExecuteScalar()
if lEmployeeDesignation IsNot Nothing AndAlso lEmployeeDesignation <> DBNull.Value then
    ' you have found your data....' 
Else
    if lEmployeeDesignation = DBNull.Value then
       ' you have a record for parameter1 but EmployeeDesignation field is null'
    End If
End If

Notice the use of AndAlso to shortcircuit the evaluation process. If the first condition is false then the second one is not evaluated

edited Dec 10, 2012 at 9:47

answered Dec 10, 2012 at 9:41

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mac Over a year ago

In the case where lEmployeeDesignation Is Nothing, then wouldn't this throw an exception when evaluating "if lEmployeeDesignation = DBNull.Value"? Wouldn't you also want to preface that evaluation with "if lEmployeeDesignation IsNot Nothing AndAlso..."?

Steve Over a year ago

I like when someone, 4 years after the answer, downvote it and doesn't leave any comment to explain what he/she has fount to be wrong. I was very new to the site at that time but still today I can't see where this answer is wrong.

Ciarán · Accepted Answer · 2012-12-10 10:45:35Z

0

You could eliminate the Null entirely by...

Select IsNull(Max(EmployeeDesignation),0) from Employee where EmployeeName = Paramater1

It's not a terribly good thing to do, but it works and as long as you're not doing too many of them it'll be fine.

You could also put in a Count(EmployeeDesignation)=0 to check scenario 3 though of course this couldn't be done on the same query or you would have to use a reader

answered Dec 10, 2012 at 10:45

Ciarán

3,0571 gold badge18 silver badges21 bronze badges

Collectives™ on Stack Overflow

Vb.net - Handling null or no values returned from SQL Command object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related