What is the safest practical way to deal with non-required MS Access text fields in queries?

Question

In querying MS Access, I've learned (How can I preempt a "Specified cast is not valid" exception?) that I have to query defensively where Text (string) values may be empty (or, apparently, in actuality null) by using the "IIF(ISNULL(colName),'',colName)" construct, such as:

SELECT id, pack_size, IIF(ISNULL(description),'',description), department, subdepartment, IIF(ISNULL(vendor_id),'',vendor_id), IIF(ISNULL(vendor_item),'',vendor_item), avg_cost, list_cost FROM PhunkyPlatypi ORDER BY id

I assume that this is only necessary for Text columns that have been designated required == false. Is my ASSumption wrong - do I have to do this with all non-required columns?

Specifically, if I need to query defensively regarding columns of data type double, is this the way to do that:

IIF(ISNULL(two_bagger),0.0,two_bagger)

?

Or better yet (one can always hope): Is there some cleaner/less obtrusive way of dealing with result sets that do not contain data in every column?

If it makes any difference, I'm querying the MS Access database from a .NET 4.5.1 Web API app using OleDbDataReader (old whine in new wineskins?)

UPDATE

Re: HansUp's (Reach for the Sky?) suggestion: "Maybe it would be more productive to attack this from the .Net side and make the code more accommodating of Nulls", would something like this be the way to do it, or is there a more efficient/safer way:

if (null == oleDbD8aReader.GetString(2))
{
    description = "Blank description";
}
else
{
    description = oleDbD8aReader.GetString(2);
}

?

UPDATE 2

I changed the code to check for DBNull, setting the value to a generic one based on the data type (string.empty for Text, 0 for ints, 0.00 for double) when it IS DBNull, but I still get the same err msg.

UPDATE 3

I'm getting "Specified cast is invalid" on this line:

long RedemItemId = (oleDbD8aReader["dbp_id"] is DBNull ? 0 : (long)oleDbD8aReader["dbp_id"]);

dbp_id is a LongInt in the Access table

The data being returned from the query includes these values in that column:

...so how could any of these values be failling a cast to long? Should I be using "Convert.ToX()" instead of "(long)"? Or...???

Actually yes, `Convert.ToX()` would be a better choice. Good suggestion. — Gord Thompson, Oct 26 '13 at 07:49
`int iResult = (rdr["numCol"] is DBNull ? 0 : Convert.ToInt32(rdr["numCol"]));` — Gord Thompson, Oct 26 '13 at 07:50

score 2 · Answer 1 · answered Oct 25 '13 at 16:16

"for Text columns that have been designated required == false ... do I have to do this with all non-required columns?"

Perhaps. Columns with other data types (numeric, Date/Time, etc.) could contain Null if you haven't set Required = True for them. Also, with a LEFT or RIGHT JOIN you could get Nulls in the unmatched rows even if all columns in the source tables have Required = True. If you don't want any Nulls in your query output, you would have to do the substitutions for all possible cases.

"regarding columns of data type double, is this the way to do that"

IIF(ISNULL(two_bagger),0.0,two_bagger)

Yes, that should work. Or you could do it this way if you prefer ...

IIF(two_bagger Is Null,0.0,two_bagger)

From OleDb, I don't believe there is "some cleaner/less obtrusive way of dealing with result sets that do not contain data in every column". Maybe it would be more productive to attack this from the .Net side and make the code more accommodating of Nulls ... then you wouldn't need to ask the db engine to substitute something else for Nulls.

score 2 · Accepted Answer · edited May 23 '17 at 12:10

(in response to the add-on question about handling things on the client side...)

OleDbDataReader will return an Access database Text field as either System.String (if it contains a value), or System.DBNull (if it is Null in the database).

So, if you want to convert DBNull values to empty (zero-length) strings just use

cmd.CommandText =
    "SELECT txtCol FROM Clients WHERE ID = 3";
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
string result = rdr["txtCol"].toString();

In the cases where you do care if the value returned was DBNull then test for it

cmd.CommandText =
    "SELECT txtCol FROM Clients WHERE ID = 3";
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
string result;
if (rdr["txtCol"] is DBNull)
{
    result = "{That field was Null.}";
}
else
{
    result = rdr["txtCol"].ToString();
}

(Note that in C#, null and DBNull are different critters.)

Edit

Similarly, for numeric database fields (for example, of type Double), you can "force" nulls to zero with

cmd.CommandText =
    "SELECT dblCol FROM Clients WHERE ID = 3";
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
double dResult = (rdr["dblCol"] is DBNull ? 0 : Convert.ToDouble(rdr["dblCol"]));

What about for non-Text/String columns? Is DBNull the thing to test for? I reckon so... — B. Clay Shannon-B. Crow Raven, Oct 25 '13 at 20:45

score 0 · Answer 3 · answered Oct 25 '13 at 17:51

0

There is a non SQL non Jet but Access function NZ which could help

IIF(ISNULL(description),'',description)

almost equal to

nz(description,'')

Check if it is avaiable through oleDbReader

answered Oct 25 '13 at 17:51

4dmonster

3,012
1
14
24

2

Unfortunately, no, `Nz()` is only available in queries that are run from within the Access application itself. – Gord Thompson Oct 25 '13 at 19:05

What is the safest practical way to deal with non-required MS Access text fields in queries?

UPDATE

UPDATE 2

UPDATE 3

3 Answers3

Edit