Dataset column always returns -1

Question

I have a SQL stored proc that returns a dataset to ASP.NET v3.5 dataset. One of the columns in the dataset is called Attend and is a nullable bit column in the SQL table. The SELECT for that column is this:

CASE WHEN Attend IS NULL THEN -1 ELSE Attend END AS Attend

When I execute the SP in Query Analyzer the row values are returned as they should be - the value for Attend is -1 is some rows, 0 in others, and 1 in others. However, when I debug the C# code and examine the dataset, the Attend column always contains -1.

If I SELECT any other columns or constant values for Attend the results are always correct. It is only the above SELECT of the bit field that is behaving strangely. I suspect it has something to do with the type being bit that is causing this. So to test this I instead selected "CONVERT(int, Attend)" but the behavior is the same.

I have tried using ExecuteDataset to retrieve the data and I have also created a .NET Dataset schema with TableAdapter and DataTable. Still no luck.

Does anyone know what is the problem here?

Answer 1

Like you, I suspect the data type. If you can change the data type of Attend, change it to smallint, which supports negative numbers. If not, try changing the name of the alias from Attend to IsAttending (or whatever suits the column).

Also, you can make your query more concise by using this instead of CASE:

ISNULL(Attend, -1)

Answer 2

You've suggested that the Attend field is a bit, yet it contains three values (-1,0,1). A bit, however, can only hold two values. Often (-1, 0) when converted to an integer, but also possible (0, 1), depending on whether the BIT is considered signed (two's compliment) or unsigned (one's compliment).

If your client (the ASP code) is converting all values for that field to a BIT type then both -1 and 1 will likely show as the same value. So, I would ensure two things:
- The SQL returns an INTEGER
- The Client isn't converting that to a BIT

[Though this doesn't explain the absence of 0's]

One needs to be careful with implicit conversion of types. When not specifying explicitly double check the precidence. Or, to be certain, explicitly specify every type...

Just out of interest, what do you get when using the following?

CASE [table].attend
   WHEN NULL THEN -2
   WHEN 0    THEN  0
   ELSE            2
END