I want to query a SQL Server database with my VB6 program, using an SQL statement. I want to make a selection on a boolean (WHERE myBoolean = TRUE), but this statement always gives me a syntax error. I guess I need to add some kind of brackets or something in order to show SQL it's a boolean value, like ' ' for strings, { } for dates, ... Can you help me out? Been looking all over the Web for an answer.

Does your database already exist? What is the datatype of the myBoolean column? The reason I ask is because SQL Server does not support a boolean datatype. The closest it comes is the BIT

    Requires Free Membership to View

    Login

datatype, although most people use TINYINT, SMALLINT, or INTEGER instead, because these integer datatypes offer more flexibility when constructing queries.

Consider the following SQL, which creates and populates a table with a BIT column:

create table test_bit
( id    tinyint not null identity
, yesno bit     not null )


insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (0)
insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (0)


select * from test_bit


id   yesno 
---- ----- 
1    1
2    1
3    0
4    1
5    0

So far, so good, right? Okay, now try a query:

select sum(yesno)
  from test_bit


Server: Msg 409, Level 16, State 2, Line 1

The sum or average aggregate operation 
cannot take a bit data type as an argument.

Well, that sort of makes sense, if you accept that BIT is not actually a numeric datatype, even though you must specify values for it using 1 and 0.

Try another query:

select yesno, count(*)
  from test_bit
group 
    by yesno 


Server: Msg 252, Level 16, State 2, Line 1
Cannot group by a bit column.

That makes no sense to me. That is, I understand what the error means, but not why SQL Server won't let you do it.

You can get around these limitations by using CAST(yesno as INTEGER) instead. But then why not define the column using INTEGER to begin with? The only time BIT might make sense is if you were storing a yes/no attribute in a tremendously large table and were concerned about total table size.


This was first published in October 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top