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

[dev guy]

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 Danowski]

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 Danowski]

U¿ytkownik "Grzegorz Danowski" <[email protected]> napisa³ w
wiadomo¶ci (...)

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