Null values

[Guest]

Another null value question. I have a form in vb.net that is driven from
queries in an Access data base. There is four fields;

ServiceID ServiceName Cost Plus

AutoNumber Text Double Text

the field Plus does not require a value, but I get a "Can not convert from
DBNull to String" error message when my form loads. I know I can do a SELECT
query that will include the nulls, but can not remember how to set the
criteria to null or not null.

TIA CoachBarkerOJPW

 

[Brendan Reynolds]

You could create a SELECT query to select records where Plus is Null or
where Plus is not Null, but I don't think that will solve your problem.

To select all records where Plus is Null ...

SELECT * FROM SomeTable WHERE Plus IS NULL

To select all records where Plus is not Null ...

SELECT * FROM SomeTable WHERE Plus IS NOT NULL

However, as I said, I don't think this will solve your problem. Somewhere in
your VB.NET code (whether in code written by you or in code auto-generated
by VS.NET) an attempt is being made to assign the value of the Plus column
to a string variable or property without first checking if the column
contains a Null value.

One possible solution would be to use a query to return an empty string in
place of the Null value ...

SELECT ServiceID, ServiceName, Cost, IIf([Plus] IS NULL, "", [Plus]) As
PlusString

The disadvantage of this solution is that you have to use an alias for the
column name, so you'll have to modify any code that expects the original
name.

The alternative is to check for Null values in your VB.NET code before
attempting to assign the values to a variable or property. I don't have a
VB.NET example to hand, but here's a C# example. In this example the
variable 'dr' is a DataReader.

if (dr.IsDBNull(dr.GetOrdinal("MiddleName")))
txtMiddleName.Text = string.Empty;
else
txtMiddleName.Text = (string)dr["MiddleName"];

 

[Guest]

Another solution would be to amend the definition of the table so that the
Plus column accepts a zero-length string, then update all Nulls to a
zero-length string:

UPDATE YourTable
SET Plus = ""
WHERE Plus IS NULL;

Then amend the table definition again so that the Plus column's Required
property is True (this is the equivalent of the NOT NULL constraint in DDL)
and make the column's default value a zero length string.

Alternatively you can update the Plus column where IS NULL to something like
"N/A", "Unknown" or whatever is appropriate, and make this the default value.
This has the advantage of avoiding the semantic ambiguity of NULL or a
zero-length string.

Ken Sheridan
Stafford, England