January 26, 2009 at 8:35 am
I have a query that sometimes returns nothing...not a NULL value...but nothing.
The query looks like this.
SELECT truck, drivername
FROM company
WHERE companyID = 'abba'
Well, sometimes a company will have no entry in the database, so there is no null, just nothing.
The vb.net script is using a datareader to read the data. And even if I use 'isDbNull', I still get an error.
How can I change the query to return something like 'N/A' or 'no item in database' when the query returns nothing?
Thanks
January 26, 2009 at 8:39 am
I'm not sure, but I think there's a way to get a data reader to deal with an empty recordset.
If that's not the best way to do this, there are a number of possible solutions.
You could add a row of nulls to the table, and have something in your query like:
select *
from dbo.MyTable
where company = 'abba'
union all
select *
from dbo.MyTable
where company = 'none'
and not exists
(select *
from dbo.MyTable
where company = 'abba')
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2009 at 12:35 pm
I actually run into this issue a lot with Crystal. If you run the report with parameters that return no data, the whole thing blows up. As Gus mentioned, there are a lot of ways to handle this. If it's a more complicated query that already uses a temp table for storing your data, you can add a check like:
IF (SELECT COUNT(*) FROM #MyTemp) = 0
SELECT 'NO DATA' as Result
I'm pretty sure this isn't necessary with .NET and there is a way to handle it. Possibly in the same fashion on the .NET side? Check the count of your return and then either show that or display a static information / error message.
As an aside, if anyone knows how to get around this when using SP's inside Crystal, I'd love to hear it =). I normally have to generate a long list of empty columns all datatype matched to what they should be if they returned values, and it's quite annoying.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply