How to read Yes/No value as boolean from an Access database?

Question

Help me to retrieve the YES/NO Datatype from MS access in Boolean format.

I tried parsing it but it always returned false.

UPDATE: Was not problem actually
Sorry, it does accepts YES/NO as boolean values.

OleDbconnection dbConnect = new OleDbConnection(".....*.MDB");
dbConnect.Open();
.....
...
//xyz = dbCommand.ExecuteReader()
bool value = (bool)xyz[1];

Next time I'll research more and find minor mistakes before asking.. Sorry people

Could you show what you have tried (your code more specifically)? — Darin Dimitrov, Jan 20 '12 at 11:53

Gord Thompson · Answer 1 · 2015-05-06T16:42:48.913

With the hope of finally putting this question to rest:

It does not matter what the Access Database Engine uses for its internal representations of Yes/True and No/False. We get back a System.Boolean value.
In Access, Yes/No fields are either Yes/True or No/False. NULL values are No/False.

Test data:

Test code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace oleDbTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string myConnectionString;
            myConnectionString =
                    @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                    @"Data Source=C:\Users\Public\Database1.accdb;";

            using (var con = new OleDbConnection())
            {
                con.ConnectionString = myConnectionString;
                con.Open();

                using (var cmd = new OleDbCommand())
                {
                    // just to be sure, let's force one of the values to Null
                    cmd.Connection = con;
                    cmd.CommandText =
                            "UPDATE YesNoTable SET YesNoField = NULL " +
                            "WHERE Description = 'Null'";
                    cmd.ExecuteNonQuery();
                }

                using (var cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText =
                            "SELECT ID, YesNoField, Description FROM YesNoTable";
                    OleDbDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        Console.WriteLine(String.Format("Row {0}:", rdr["ID"]));
                        bool boolValue = Convert.ToBoolean(rdr["YesNoField"]);
                        Console.WriteLine(String.Format("    Description is: {0}", rdr["Description"]));
                        Console.WriteLine(String.Format("    Return type is: {0}", rdr["YesNoField"].GetType()));
                        Console.WriteLine(String.Format("    raw value is: {0}", rdr["YesNoField"]));
                        Console.WriteLine(String.Format("    boolValue is: {0}", boolValue));
                        Console.WriteLine();
                    }
                }
                con.Close();
            }
            Console.WriteLine("Done.");
        }
    }
}

Results:

Row 1:
    Description is: Yes
    Return type is: System.Boolean
    raw value is: True
    boolValue is: True

Row 2:
    Description is: No
    Return type is: System.Boolean
    raw value is: False
    boolValue is: False

Row 3:
    Description is: Null
    Return type is: System.Boolean
    raw value is: False
    boolValue is: False

Done.

score 2 · Answer 2 · answered Jan 20 '12 at 12:00

2

The problem is that the boolean of .Net is defined as

0 = false
1 = true

But MS Access uses the following values for its boolean

0 = false
-1 = true

So you have to manually convert this integer values to the corresponding boolean values.

answered Jan 20 '12 at 12:00

Oliver

43,366
8
94
151

score 0 · Answer 3 · answered May 20 '17 at 07:54

0

if (Convert.ToBoolean(ds.Tables[0].Rows[i]["UseCurrInWords"].ToString()))
    chkBobUseCurrencyInWords.Checked = true;

answered May 20 '17 at 07:54

Halim

2,090
2
16
9

2

**From review queue:** May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post. – help-info.de May 20 '17 at 08:25

score 0 · Answer 4 · answered Jan 20 '12 at 12:07

0

false = 0
true = !false

That's all you need to know.

answered Jan 20 '12 at 12:07

dwo

3,576
2
22
39

DRapp · Answer 5 · 2012-01-20T12:43:23.600

-1

From what I remember, the yes/no is actually a numeric of 1 or -1 where 1 is true.

Correction per Remou... 0 = false, -1 = true.. Last time I used Access was about 2005, so I wasn't perfect :)

edited Jan 20 '12 at 12:43

answered Jan 20 '12 at 11:59

DRapp

47,638
12
72
142

@Remou, sorry about that... the last time I used access was back about 2005, but at least I remembered it wasn't something like "YES" or "NO" as a string, or other enumerable :) – DRapp Jan 20 '12 at 12:42

score -1 · Answer 6 · answered Jan 20 '12 at 12:05

-1

ms access might be .mdb files or .accdb.

Watch with the debugger the values returned.

My guess is that true Is == 0 and False is !=0.

answered Jan 20 '12 at 12:05

GameAlchemist

18,995
7
36
59

How to read Yes/No value as boolean from an Access database?

6 Answers6

Linked