Null Returned from SQL Server Query

[Jonesgj]

Hi,

Whats the best way of capturing a null returned from a query?

What through me last night was I knew the table I was querying was empty, so
I thought the data reader HasRows method would be enough. Hoever, after
sleeping on the problem I thought I'd just try my query straight into Query
Analyser, and then it dawned on me that my query was a aggregate query. So
it returns a a Row with NULL.

This was what I was trying to do - avoid nulls

If MyDataread.HasRows Then

MyDatreader = .......(CommandBehavior.Default)

Do While MyDataread.Read
SELECT Max(Rank+1) as MaxRank FROM MyTable
......
Loop

Else
......

Any assistance greatfully accepted.

Thanks

JonesGJ

 

[Richard Myers]

Hello JonesGJ, owl are you?

You wanna wake up to using ExecuteScalar for that kind of SQL not
ExecuteReader.
But dont be alarmed as MSDN has blanketed that subject area with more
helpful
hints than you've had pillow fights.

Im assuming you've em-bed-ded that SQL in a stored proc rather than
just letting it drift about among your code like biscuit crumbs between the
sheets.

hth
Richard

 

[Ken Tucker [MVP]]

Hi,

Take a look at the datareaders isdbnull property.
http://msdn.microsoft.com/library/d...asqlclientsqldatareaderclassisdbnulltopic.asp

http://msdn.microsoft.com/library/d...ataoledboledbdatareaderclassisdbnulltopic.asp


Ken
----------------------------
Hi,

Whats the best way of capturing a null returned from a query?

What through me last night was I knew the table I was querying was empty, so
I thought the data reader HasRows method would be enough. Hoever, after
sleeping on the problem I thought I'd just try my query straight into Query
Analyser, and then it dawned on me that my query was a aggregate query. So
it returns a a Row with NULL.

This was what I was trying to do - avoid nulls

If MyDataread.HasRows Then

MyDatreader = .......(CommandBehavior.Default)

Do While MyDataread.Read
SELECT Max(Rank+1) as MaxRank FROM MyTable
......
Loop

Else
......

Any assistance greatfully accepted.

Thanks

JonesGJ

 

[Hayato Iriumi]

Hello Jonesgj,

It seems that looping through the DataReader is a waste in this scenario.
You only get one column and one row after all, so why don't you use ExecuteScalar
which returns an object? I'm not exactly sure what the method is going to
return when the data is NULL, but I suggest that you check if return value
is System.DBNull or null (or Nothing in VB).

Hope it helps.

 

[Jonesgj]

Thanks Richard ... apologies in the delay in returning this. I solved this
using an isnull in my sql....and ofcourse I use Stored procs where
appropriate ;-)

However, I do struggle in getting values retunred from my procs. Any ideas?

Best regards

JonesGJ