Assigning the result of an SQL ExecuteScalar() to an int?

[Red_Wizard_Shot_The_Food]

I want to assign the results of an SQL ExecuteScalar to an int. Which is fine when the ExecuteScalar returns a value (i.e the paramater sent to the stored procedure caused the SELECT function to find a valid record). BUT if the paramater does not pass the SELECT procedure a value that is already in the table the assignment to an int causes an exception (as no record was returned). Can i do this without using a try/catch block? Thanks.

[Pete OHanlon]

Red_Wizard_Shot_The_Food wrote:

I want to assign the results of an SQL ExecuteScalar to an int. Which is fine when the ExecuteScalar returns a value (i.e the paramater sent to the stored procedure caused the SELECT function to find a valid record). BUT if the paramater does not pass the SELECT procedure a value that is already in the table the assignment to an int causes an exception (as no record was returned). Can i do this without using a try/catch block?

You could return it as an object and then cast it if the object was not null.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[aSarafian]

As far as i know you can't do it without using try catch or an if statement which in this case is the same. Convertion in .Net assumes that the converted value is not null. So any testing should be done prior assuming your id in base are not zero-valued i have implemented a function that executed the command and return zero if not found or the desired value. I have implemented something similar for strings also.

[Colin Angus Mackay]

Sarafian wrote:

you can't do it without using try catch or an if statement which in this case is the same.

No it isn't. A try/catch adds a heck of a processing overhead if an exception is raised. Since this is not an exceptional case getting the value as an object then testing for null would be the better option.

Sarafian wrote:

assuming your id in base are not zero-valued i have implemented a function that executed the command and return zero if not found or the desired value.

Returning magic numbers is not the solution. Testing for null or DBNull.Value is. It should also be realised that null is not the same as DBNull.Value. null is returned if the SELECT statement returned nothing at all. DBNull.Value is returned if the SELECT statement found the row and column, but the column was null.

---

Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos

[aSarafian]

Thanks. Didn't know the difference.