CODEWITHSUNDEEP
vb.net
Conrad Jagger
Conrad Jagger

Reputation: 643

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

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:

  1. Returns the employee Designation (record exists in table)
  2. No value is set in database for Parameter1 - so should return NULL (record exists but has no value)
  3. 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

Upvotes: 2

Views: 13383

Answers (2)

Ciarán
Ciarán

Reputation: 3057

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

Upvotes: 0

Steve
Steve

Reputation: 216351

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

Upvotes: 1

Related Questions