Search code examples
c#sqlsql-server-ce

Getting empty answer when I should get nothing

I'm working on a C# program that uses a SQL Server Compact database. I have a query where I want to select the highest number in a specific field that looks like this:

SELECT MAX(nr2) FROM TABLE WHERE nr1 = '10'

This works as inteneded when there is a row where nr1 is 10. But I would expect to not get an answer when that row doesn't exist, but instead I get an empty field. So in my C# code I have:

text = result[0].ToString();

When I get a value from my SQL query the string contains a number and when the specified row doesn't exist I get an empty string.

This isn't really a big problem but I would be able to do the following check:

if (result.Count > 0)

Instead of:

if (result[0].ToString() == "")

which I have to do at the moment since count is always larger than 0.

Solution

  • Talk about using a sledgehammer to crack a nut, but...

    I don't test it with C# code, but in SQL Server Management Studio, if you run...

    SELECT MAX(nr2) FROM TABLE WHERE nr1 = '10' HAVING MAX(nr2) IS NOT NULL

    , the result is an empty collection, not a collection with one null (or empty) element.

    NOTE: My answer is based on this SO Answer. It seems that MAX and COUNT SQL functions returns always a single row collection.