SQL Server CE - Unable to insert values in the BIT column

Question

I have created a small table in SQL Server CE 3.5

Following is the table description:

ROW_ID NVARCHAR(30),
NAME NVARCHAR(30),
TEST BIT

I am using following query to insert record in the table:

insert into EMP(ROW_ID, NAME, TEST)
values('123', 'XYZ', TRUE);

But I am getting a strange error:

Error Message: The column name is not valid. [Node Name (if any) = ,Column name = TRUE]

Please help me with this.

Thanks in advance.

Answer 1

Instead of true and false use 1 and 0. Eg:

insert into EMP(ROW_ID, NAME, TEST)
values('123','XYZ',1);

This is for SQL Server 2005 bit:

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

You can try it. If it applies to CE following code ('TRUE' as string) might work as well:

insert into EMP(ROW_ID, NAME, TEST)
values('123','XYZ', 'TRUE');

Answer 2

Nothing strange about that. It's saying is doesn't know what TRUE is. Not a known name, not a column in the table, hence error message.

Use 1 for true, 0 for false.

Answer 3

Otherwise You Can try this way:

  insert into EMP(ROW_ID, NAME, TEST)
  values('123','XYZ',b1);    // b for bit type and 1 is the field value as (true)

or

  insert into EMP(ROW_ID, NAME, TEST)
  values('123','XYZ',0b1);   // 0b for bit type and 1 is the field value as (true)