assigning null value to the Yes/No field of MS Access database table

hi

I want to assign null value to Yes/No field. So that the value of the field
of MS Access database table is neither YES nor NO. Is this possible? The
field properties show that there is a property called "Allow Null". I have
set it to TRUE. But this does not help.

I thereafter try to access this table from VB.NET and try to read the value
or insert a record with this field value as NULL. But when I read the record
as DBBoolean, I always get value as FALSE.

Can somebody throw some light on the same?

warm regards
Haresh Gujarathi

Grzegorz
Tue Nov 16 02:38:27 CST 2004

U¿ytkownik "dev guy" <info@emantratech.com> napisa³ w wiadomo¶ci
news:O0DSKr6yEHA.2788@TK2MSFTNGP15.phx.gbl...
> hi
>
> I want to assign null value to Yes/No field. So that the value of the
> field
> of MS Access database table is neither YES nor NO. Is this possible? The
> field properties show that there is a property called "Allow Null". I have
> set it to TRUE. But this does not help.
>
> I thereafter try to access this table from VB.NET and try to read the
> value
> or insert a record with this field value as NULL. But when I read the
> record
> as DBBoolean, I always get value as FALSE.

I'm not sure but probably bit field is not tristate? Instead od bit use
another type, for example byte.
My small snipped on breakfast:

using System;
using System.Data;
using System.Data.OleDb;

namespace TestNull
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
Test t = new Test();

t.MakeTable();
t.FillTable();
t.ReadTable();

System.Console.ReadLine();
}
}

class Test
{
OleDbConnection con = new OleDbConnection();
public Test()
{
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=E:\Data.mdb;";
}

public void MakeTable()
{
OleDbCommand myC = new OleDbCommand(
"Create Table MyTable(" +
" Id Long Identity(1,1) Not Null Primary Key," +
" Name Varchar(20) Not Null," +
" Women Byte Null" +
")", con); // "Women Bit Null" not worked
con.Open();
myC.ExecuteNonQuery();

con.Close();
}

public void FillTable()
{
con.Open();

OleDbCommand myC = new OleDbCommand(
"Insert Into MyTable (Name, Women) " +
"Values('Alice', true)", con);
myC.ExecuteNonQuery();

myC = new OleDbCommand(
"Insert Into MyTable (Name, Women) " +
"Values('Ben', false)", con);
myC.ExecuteNonQuery();

myC = new OleDbCommand(
"Insert Into MyTable (Name, Women) " +
"Values('Alf', Null)", con);
myC.ExecuteNonQuery();

con.Close();
}

public void ReadTable()
{
con.Open();
OleDbCommand myC = new OleDbCommand(
"Select * From MyTable", con);
OleDbDataReader dr = myC.ExecuteReader();

while(dr.Read())
System.Console.WriteLine("{0}\t{1}\t{2}\t{3}",
dr[0], dr[1], dr[2], dr.IsDBNull(2));
}
}
}

I hope it helps.
Grzegorz

Grzegorz
Tue Nov 16 06:05:11 CST 2004

U¿ytkownik "Grzegorz Danowski" <gdn@usuntopoczta.onet.pl> napisa³ w
wiadomo¶ci news:cncea5$gmt$1@inews.gazeta.pl...
(...)
> I'm not sure but probably bit field is not tristate? Instead od bit use
> another type, for example byte.
> My small snipped on breakfast:
>
(...)
>
> public void ReadTable()
> {
> con.Open();
> OleDbCommand myC = new OleDbCommand(
> "Select * From MyTable", con);
> OleDbDataReader dr = myC.ExecuteReader();
>
> while(dr.Read())
> System.Console.WriteLine("{0}\t{1}\t{2}\t{3}",
> dr[0], dr[1], dr[2], dr.IsDBNull(2));
> }
> }
> }
>

Another version of reading function:

public void ReadTable()
{
con.Open();
OleDbCommand myC = new OleDbCommand(
"Select * From MyTable", con);
OleDbDataReader dr = myC.ExecuteReader();

while(dr.Read())
{
if(dr.IsDBNull(2))
{
System.Console.WriteLine("{0} has unknown sex", dr[1]);
}
else
{
bool women = women = Convert.ToBoolean(dr.GetByte(2));
System.Console.WriteLine("{0} is women: {1}", dr[1], women);
}
}
}

Regards,
Grzegorz