Blank/empty field confusion

[Martin Stabrey]

I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that field.
This relates to a DB search form which I have in Visual Web Developer 2008 -
it has 5 fields - 3 text, 2 drop-down. It seems that when this search form
queries the Access DB, it is behaving like a blank/empty field in a record
in fact has something inside it. Is this correct? How can I get the empty
fields in the DB to be ignored during the search process?

Thanks!

Mart

 

[Brendan Reynolds]

I'm quite new to Access DB design.

My problem is that I am not sure how certain fields that are left
blank/empty are treated. Is there in fact "something" inside that field.
This relates to a DB search form which I have in Visual Web Developer
2008 - it has 5 fields - 3 text, 2 drop-down. It seems that when this
search form queries the Access DB, it is behaving like a blank/empty field
in a record in fact has something inside it. Is this correct? How can I
get the empty fields in the DB to be ignored during the search process?

Thanks!

Mart

If the AllowZeroLengthStrings property is set to True, then a field that
appears to be empty may contain either a null value or an empty string. To
test for these two possible values in .NET, you'll need to compare against
both DbNull and String.Empty.

 

[Martin Stabrey]

Thanks Brendan!

Can the AllowZeroLengthStrings be changed after data has already been
entered in the DB so that it affects all data in the DB?
I must admit to not entirely knowing what you mean, or knowing what to do
about the comparing of DbNull and String.empty.

Mart

 

[Brendan Reynolds]

The AllowZeroLength property is a property of text fields that determines if
a zero length string (a string containing no characters) is permitted in
that field. You'd have to change it on each individual text field, and (to
the best of my memory) it would not cause any changes to data already in the
database, it would only prevent any zero length strings being entered in the
future. To change data already in the database, you'd need to run a bunch of
update queries, similar to the following example ...

UPDATE SomeTable SET SomeField = Null WHERE SomeField = ""

That's two empty quotes with nothing (not even a space) between them at the
end.

I've been working in Access and away from .NET for a while, so I'm a bit
rusty on the exact syntax you need to use in .NET to compare a value with
DbNull and String.Empty, and the details will depend on just what you're
doing in which version of .NET, and whether you're using VB.NET or C# or
some other language. As an example, here's something that I wrote way back
to return the value of a column in a ADO.NET datareader as a string,
converting null values to empty strings ...

private string GetString(System.Data.IDataReader dr, string columnName)
{
if (dr.IsDBNull(dr.GetOrdinal(columnName)))
return string.Empty;
else
return (string)dr[columnName];
}

If you're still stuck, you might want to try asking the question in an
ADO.NET forum, or a VB.NET or C# forum, depending on which language you're
using.

 

[Martin Stabrey]

Thanks Brendan. Will give it a try.

The AllowZeroLength property is a property of text fields that determines
if a zero length string (a string containing no characters) is permitted
in that field. You'd have to change it on each individual text field, and
(to the best of my memory) it would not cause any changes to data already
in the database, it would only prevent any zero length strings being
entered in the future. To change data already in the database, you'd need
to run a bunch of update queries, similar to the following example ...

UPDATE SomeTable SET SomeField = Null WHERE SomeField = ""

That's two empty quotes with nothing (not even a space) between them at
the end.

I've been working in Access and away from .NET for a while, so I'm a bit
rusty on the exact syntax you need to use in .NET to compare a value with
DbNull and String.Empty, and the details will depend on just what you're
doing in which version of .NET, and whether you're using VB.NET or C# or
some other language. As an example, here's something that I wrote way back
to return the value of a column in a ADO.NET datareader as a string,
converting null values to empty strings ...

private string GetString(System.Data.IDataReader dr, string columnName)
{
if (dr.IsDBNull(dr.GetOrdinal(columnName)))
return string.Empty;
else
return (string)dr[columnName];
}

If you're still stuck, you might want to try asking the question in an
ADO.NET forum, or a VB.NET or C# forum, depending on which language you're
using.

--
Brendan Reynolds

Thanks Brendan!

Can the AllowZeroLengthStrings be changed after data has already been
entered in the DB so that it affects all data in the DB?
I must admit to not entirely knowing what you mean, or knowing what to do
about the comparing of DbNull and String.empty.

Mart

 

[Tom Wickerath]

Hi Brendan,

Nice to see you back in this newsgroup!

...and (to the best of my memory) it would not cause any changes to data
already in the database, it would only prevent any zero length strings being
entered in the future.

This is a correct statement.

Martin:
Access MVP Allen Browne has an article that explains more about ZLS (zero
length string) vs. nulls in Access/JET applications, available here:

Problem properties
http://allenbrowne.com/bug-09.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

The AllowZeroLength property is a property of text fields that determines if
a zero length string (a string containing no characters) is permitted in
that field. You'd have to change it on each individual text field, and (to
the best of my memory) it would not cause any changes to data already in the
database, it would only prevent any zero length strings being entered in the
future. To change data already in the database, you'd need to run a bunch of
update queries, similar to the following example ...

UPDATE SomeTable SET SomeField = Null WHERE SomeField = ""

That's two empty quotes with nothing (not even a space) between them at the
end.

I've been working in Access and away from .NET for a while, so I'm a bit
rusty on the exact syntax you need to use in .NET to compare a value with
DbNull and String.Empty, and the details will depend on just what you're
doing in which version of .NET, and whether you're using VB.NET or C# or
some other language. As an example, here's something that I wrote way back
to return the value of a column in a ADO.NET datareader as a string,
converting null values to empty strings ...

private string GetString(System.Data.IDataReader dr, string columnName)
{
if (dr.IsDBNull(dr.GetOrdinal(columnName)))
return string.Empty;
else
return (string)dr[columnName];
}

If you're still stuck, you might want to try asking the question in an
ADO.NET forum, or a VB.NET or C# forum, depending on which language you're
using.

 

[Brendan Reynolds]

Hi Brendan,

Nice to see you back in this newsgroup!

Thanks Tom! Best wishes to yourself and the other Access MVPs, present and
past.