Handling Boolean Fields in MS-SQL and MS-Access

John Avis by | May 3, 2008 | Classic ASP Web Development

If you have migrated from Microsoft Access to SQL Server or MySql then you have probably encountered the differences with boolean values.

In Microsoft Access you can use true or false in queries, for example:

SELECT * FROM tablename WHERE booleanfield=TRUE
SELECT * FROM tablename WHERE booleanfield=FALSE
SQL Server/MySql require a different approach:

SELECT * FROM tablename WHERE booleanfield=1
SELECT * FROM tablename WHERE booleanfield=0
The Microsoft Access query will not work in SQL Server/MySql and the SQL Server/MySql query will not work in Access as it treats true as -1 not 1.

For a cross-platform solution you can use the following:

SELECT * FROM tablename WHERE booleanfield<>0
SELECT * FROM tablename WHERE booleanfield=0

Related Posts

Computers & Internet Web Development Website Hosting

500 Internal Server Error after migrating from IIS 7.5 to IIS 10

by John Avis | November 4, 2019

As support ends for Microsoft Windows Server 2008 I have recently gone through migrating some websites to a new server running Windows Server 2016 and IIS 10 but some of the websites did not work.


Jquery/Javascript Web Development

tagInput: A simple jQuery plugin for tag entry using Bootstrap 4

by John Avis | October 15, 2019

For a website project I needed a way to enter multiple tags. I just wanted something simple that I could easily modify to suit my own needs, so I wrote my own.


ASP.NET Web Forms Web Development

ASP.NET bug with RadioButton GroupName in Repeater

by John Avis | September 1, 2019

I rediscovered a bug in ASP.NET that affects RadioButtons inside repeaters. Here is my solution to the problem.

Comments

There are no comments yet. Be the first to leave a comment!

Leave a Comment

Tags

About me

...random postings about web development and programming, Internet, computers, electronics and automotive topics.

Subscribe

Get the latest posts delivered to your inbox.