Handling Boolean Fields in MS-SQL and MS-Access

John Avis by | May 3, 2008 | Classic ASP

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

ASP.NET Web Forms Classic ASP

Custom error pages for ASP.NET Web Forms and Classic ASP in IIS 7 and 8.5

by John Avis | February 20, 2019

I was recently doing some work on a website which has a mixture of older Classic ASP pages and ASP.NET Web Forms pages and ran into problems with custom error pages.


Classic ASP

Sending email using Amazon SES with Classic ASP

by John Avis | March 21, 2017

I recently needed to change a client's website to send emails using Amazon SES and encountered a few issues.


Classic ASP

Classic ASP class constructors with parameters

by John Avis | June 8, 2016

Although probably no one cares about Classic ASP, except those who still need to support it, I was refreshing my memory today on how to use classes in Classic ASP and found a couple of options for simulating constructors with parameters.

Comments

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

Leave a Comment
Tags
ASP.NET Html Forms ASP.NET MVC ASP.NET Web Forms ASP.NET Web Pages Bootstrap C# Classic ASP Cool Websites Databases eBay and PayPal Electrical Repairs General Hardware HTML/CSS Jquery/Javascript Media Center Mobile Phones Responsive Web Design Reviews SEO and Social Networking Web Design Web Development Web Security web+db Website Hosting Windows XP Youtube

About me

...mostly about web development and programming, with a little bit of anything else related to the Internet, computers and technology.

Subscribe

Get the latest posts delivered to your inbox.