Hi All.

First time working on an SQL database. I'm able to create all my tables and
fields without errors. I can work eith the back end pretty well, but I
can't work out True or False Fields/Columns.

If I try:
Insert Into MyTable (value1, value2) values (true, false);

I'm told that true (or false) cannot be used as they are column names or
keywords.

I've searched and searched for the answer, all the info I've found pretty
much contradicts itself!

I'm using a Bit field, which surely should mean 1=True, 0=False. Does with
any other database.

What am I doing wrong? !!

Cheers,
Tull.

"tclancey" <tull (AT) idcodeware (DOT) co.uk> wrote

Brace yourself. What does "I'm told" mean? A rhetorical question - this
time I think I know what is happening. In the future, you should quote any
errors exactly and completely and tell us exactly how you came to encounter
the error. Usually this means providing some clue about the application you
are using to execute queries. In addition, you should always specify which
version of sql server you are using alsong with the SP level.

Make a note of the "sources" that told you that you could use TRUE or FALSE.
Ignore them for all future information needs.

Assuming that 1 = TRUE and that 0 = FALSE. They aren't. TSQL is not
C/C++/VB, there is no boolean datatype, and you cannot evaluate a number (by
itself) as a boolean expression. If you want to use a bit value of 1, then
you must specify 1 (and not TRUE or "TRUE" or 'TRUE'). If you have a
background in Access, then there are many other things that you will need to
relearn when using sql server. I recommend you search (and post to, if
needed) the .programming NG since it is more focused on sql server
programming and experiences much higher volume.

Thanks for your reply.

I understand my expression of the problem was somewhat limited, I thought
there would be an easy answer that I've missed.

If I explain the following it may make more sense, or at least give a better
idea of what I want to achieve.

I have an application that I need to work with Access (currently it does,
very well) MySql and MsSql.

I can get all True/False queries to work with MySql without any problems
using exactly the same syntax as Access. MsSql is the problem. There
simply isn't a Boolean return field available.

There must be a simple answer somewhere as I'm sure other people have faced
the same problem.

The 'bit' field in MySql handles True and False correctly. Why can't MS?


"Scott Morris" <bogus (AT) bogus (DOT) com> wrote

Sorry, tired when I wrote this last night. Let me explain a little better.


The application I'm working on at the moment needs to connect to Access,
MsSql or MySql, this will be decided by the customer and set in the software
by a hardware key.

I have started conversion of the code that was originaly written for Access.
All commands, Insert, Update etc work fine with MySql, but not with MsSql.
The only real problem that I can see is the True and False fields. I really
don't want to have to write completely seperate routines for each database
back end.

Also, pulling in a field and setting a CheckBox.Checked state to True or
False is easy if the return value is True or False.

I have thought about writing some kind of parser to check for certain
keywords and replace them, but I cannot guarantee that the users are not
going to use the words True, Flase or even 1 or 0.

Any help would be greatfully recevied as I currently have no idea how to get
around this!

Thanks,
Tull.



"tclancey" <tull (AT) idcodeware (DOT) co.uk> wrote

Laudable - but highly improbable (IMO).

Neither do I as I don't use Access. I suggest you post to an
Access-specific NG since that is the crux of the issue here. As I said
earlier, there is no boolean datatype in sql server. Since moving an Access
system to sql server appears to be a common activity, I expect that this
topic has been covered in an Access NG.

Many thanks for your input. I'll give this a go.
Cheers,
Tull.

"Scott Morris" <bogus (AT) bogus (DOT) com> wrote

"tclancey" <tull (AT) idcodeware (DOT) co.uk> wrote

As Scott pointed out, MS SQL Server doesn't have a "Boolean" data type
(there are some specific reasons why this is the case, but we won't dive
into them here). MS SQL does have a "Bit" data type, which you can use to
"simulate" Boolean data types to some degree, much like Access does. You
can even write insert and update statements that accept 'True'/'False' and
convert it to 1/0 for storage, and vice versa, by using SQL CASE
expressions. As Scott said, you should post to the MS SQL Programming
newsgroup for more detailed responses. In the meantime, here's a Q&D
example of converting 'True' and 'False' to and from '1' and '0' in a Bit
data type column:

CREATE TABLE #test (code INT NOT NULL PRIMARY KEY,
bitflag BIT)

DECLARE @flag VARCHAR(10)
SET @flag = 'True'

INSERT INTO #test (code, bitflag)
SELECT 1, 0

INSERT INTO #test (code, bitflag)
SELECT 2, 0

INSERT INTO #test (code, bitflag)
SELECT 3, CASE @flag
WHEN 'True' THEN 1
WHEN 'False' THEN 0
ELSE NULL
END

UPDATE #test
SET bitflag = CASE @flag
WHEN 'True' THEN 1
WHEN 'False' THEN 0
ELSE NULL
END
WHERE code = 2

SELECT code, bitflag, CASE bitflag
WHEN 1 THEN 'True'
WHEN 0 THEN 'False'
END AS boolean
FROM #test

DROP TABLE #test

On Sat, 31 Mar 2007 15:39:27 +0100, tclancey wrote:

Hi Tull,

Since Access, SQL Server, and MySQL all have different proprietary ways
to handle boolean and/or bit values, the best way to get the same code
working against all databases would be to avoid these proprietary types
altogether. Just use (for isntance) a CHAR(1) datatype, with a CHECK
constraint to only alllow the values 'T' and 'F' (for true and false).

It might be a bit more work for the front end since you can't use the
standard boolean types, but that's easily offset by the fact that you
only need one code base for the three databases.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis