Stored procedure returns -1 instead of 1 when true

Question

Post reply

Stored procedure returns -1 instead of 1 when true

hprider

SSC Enthusiast

Points: 139
More actions
April 15, 2008 at 7:10 pm

#185537

Hello,
I have a stored procedure:
-- Get an individual league match by ID
ALTER PROCEDURE [dbo].[mb_League_GetLeagueMatchByID]
(
@LeagueMatchID int
)
AS
SET NOCOUNT ON
SELECT * FROM mb_LeagueMatch
WHERE mb_LeagueMatch.LeagueMatchID = @LeagueMatchID
The mb_LeagueMatch table has a column named IsActive that is a bit datatype.
The value for all rows is set to true (in database explorer in visual studio 2005).
When I run the above stored procedure I always get -1 (I'm guessing that means null) as a result for IsActive if it was true and 0 when false (as expected).
However, when I run a query on the database for the same parameter, I get the expected 1 as the value for IsActive.
Has anyone seen this before?
Thanks,
Howard

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

A bit field should be evaluated as:

False = Zero

True = Not Zero

Neither true or false = NULL

hprider SSC Enthusiast Points: 139 More actions · Answer 2

The problem happens when the data is accessed in code. In C# I populate a List of LeagueMatch objects from the database. I then iterate through the List, setting the DatePlayed column to the new value and then call Update on the object. The date is set, the other values stay the same. The problem is that IsActive, which was originally true, now gets updated to be false, even though it wasn't set to false.

The stored procedure that selects the data brings back IsActive = -1. When I do the update, the value of -1 is interpreted as false and therefore the value of IsActive is being updated to false.

This can't be expected behavior, can it?

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

As said before, in the database; All values equal to zero is false. All other numeric values is converted to 1 which equals to 1 meaning true.

NULL is unknown.

VB6 treated FALSE/TRUE with 0/-1. Not 0/1.

But if you added a CBOOL like this

iA = CBOOL(rs.Fields("Match").Value)

then all values coming from database equal to 0 is still zero in client.

All other values is treated as -1 / true.

And sending true/false from client (0/-1) to the databas is equal ok. 0 evaluates to zero (false) and all other numeric values converts to 1 (true).

N 56°04'39.16"
E 12°55'05.25"

GSquared SSC Guru Points: 260824 More actions · Answer 4

It sounds to me like there's a problem with the C# code that's being used. In a bit/bool data type, -1 = 1 = True, 0 = False.

I have seen the -1 = True thing before, when I was using Access as a front end for SQL. Web pages would set the value to 1 for True, Access would set the value to -1 for True. Both used 0 for False. Both of these behaviors are standard, and both applications could deal with it when they ran into data set by the other application.

I'm guessing, but check if there is something in the C# code that reads the data as "less than 1 = False".

- 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

hprider SSC Enthusiast Points: 139 More actions · Answer 5

Hi guys,

Thanks for all of the help. It did turn out to be a typo in the value object used in the business logic layer.

I guess seeing the -1 freaked me out as I'd never noticed it before.

Again, thanks!

Howard

GSquared SSC Guru Points: 260824 More actions · Answer 6

You're welcome.

- 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