Access conversion to SQL

[tclancey]

Hi all.

I have an application that currently connects to an Access database, now the
customers are asking for a MySql and MsSql server back end database.

MySql handles True and False values through the bit field without any
problems, MsSql doesn't. I can't even use the words True or False in an Sql
statement.

This means I have to write two loads of code for every transaction where
there is a true/false value involved, a complete pain.

It also gives me the problem of setting controls true/false value by
checking a table field:
chkAdmin.Checked = GetField("select admin from users where
username='Bert';", "admin")
(Get field is a routine that loads a data record and returns the field as
the second parameter)

As SQL never returns a true or false value in this way I have to write extra
code for every check, or checked list, etc.

Is there a way around this?

Cheers,
Tull.

 

[Guest]

problems, MsSql doesn't. I can't even use the words True or False

in an sql statement.

I don't have any trouble using the words True and False
in a MSsql statement.

chkAdmin.Checked = GetField("select admin from users where
username='Bert';", "admin")

That works for me: you will have to be more careful with the
way you write GetField.


Access/Jet/Intel/ and others use -1 as True.

MS SQL/ C/ Digital/ and others used +1 as True.

Access/Jet will correctly translate Boolean values as long as you are
careful to only use them as Boolean's (not as numbers).

Alternatively, you can choose to only use numbers (0,1 Null), so
that your SQL is clearer, more exact, and more portable, but then
you won't be able to depend on the sign of TRUE.

Many people choose to only use FALSE (note that it has the
same value in both systems). They use NOT FALSE instead of
TRUE.

Many people choose to only use numbers instead of Booleans.

It is a real problem, but nobody solves it by using "two loads
of code". If you are doing that, you are doing something wrong.

(david)

 

[Aaron Kempf]

I'm not so sure that I agree with you, dude

I've definitely had some problems using True and False in SQL Server.. I
always convert them to an integer..


most of the times; I move a bunch of booleans into an integer-- I use
bitwise operators for this

 

[Susie Johnson]

Im not sure I agree with you david

I've had problems using the constant true or false in SQL Server


select true = ERROR
select 'true' = 'true'

so how does this explicitly convert to 0 or 1 or -1?