Storing missing values

  • Thread starter Thread starter John Dann
  • Start date Start date
J

John Dann

For a column in (eg an Access) database that's used to store numeric
data, is there a specific way to designate a missing value for a given
row (ie the value was simply never measured or assigned). Or must I
set up my own convention, ie if it's a short type then to use eg
-32768 or -999 (or some other value obviously outside the valid range
for that column)?

JGD
 
Hi John,

I think you are talking about DBNull.Value ?
Where exactly is your problem - in database or at program level?
 
I think you are talking about DBNull.Value ?
Click to expand...

Very possibly - afraid that I'm using ado.net as a tool to deal with a
specific project and am far from an expert! I'm having to understand
each issue as it arises.
Where exactly is your problem - in database or at program level?
Click to expand...

Hmm, well both really. Parameter values originate in a separate
external program and are placed in a dataset before using this to
update the main (currently Access) database. Similarly for data
retrieval from the database, the data moves into a dataset and thence
into a charting WinForm.

For passing data into the database I need to be able to set missing
values in the update dataset and similarly to interpret such values
when they're retrieved. Are you implying that I can set a value to
VBNull.value in the dataset without causing an error in a column eg
otherwise of variable type short? And that this would be accepted by
the database and also retrieved into the retrieval dataset intact?

That would be quite a help if I've understood correctly.

I would be left with one further problem. Any missing values in the
retrieved dataset would need to be converted to a value recognised as
missing by the charting Winform. Since this is a third-party control
it uses its own convention for missing values. I guess there's no
global method for converting vbnull.value to some other value (eg -999
or whatever) in the retrieved dataset other than iterating through
every single value and testing for vbnull.value?

JGD
 
For passing data into the database I need to be able to set missing
values in the update dataset and similarly to interpret such values
when they're retrieved. Are you implying that I can set a value to
VBNull.value in the dataset without causing an error in a column eg
otherwise of variable type short? And that this would be accepted by
the database and also retrieved into the retrieval dataset intact?
Click to expand...

Yes. (DBNull.Value actually).
Both table in database and datatable.column have to allow nulls of course.
That would be quite a help if I've understood correctly.

I would be left with one further problem. Any missing values in the
retrieved dataset would need to be converted to a value recognised as
missing by the charting Winform. Since this is a third-party control
it uses its own convention for missing values. I guess there's no
global method for converting vbnull.value to some other value (eg -999
or whatever) in the retrieved dataset other than iterating through
every single value and testing for vbnull.value?
Click to expand...

There are other options. You might add a calculated column to DataTable in
question (a DataColumn that has Expression property set -see .net help) and
use this column for output.
 
.

Many thanks for the helpful comments. Well some progress, but also a
problem:

Yes indeed you've shown me that I can set Int16 values to DBNull.value
in a dataset and that I can successfully update an Access database
with this dataset and also retrieve a new dataset from the database
OK.

But what I have been doing hitherto is to scale the numeric values
when they're retrieved as part of the SELECT statement, which looks
like eg:

SELECT cdbl(col_name/10) as col_name FROM TABLE etc.......

But this throws an error that 'The provider could not detemine the
Double value.......' at the first row containing a null value (though
the dataset up to that point is then returned), which is maybe
unsurprising if ado.net can't cope with this particualr situation. So
I'm left with two questions:

1. Is there any workaround that I can use while still retaining my
current approach, eg maybe there's an alternative syntax to this form
of SELECT statement? (I know there are other approaches like a stored
procedure or doing the scaling on the dataset after retrieval and
explicitly doing an IsNull test there, but I'm interested to know
whether there's any fix for the approach I've used until now.)

2. Could this be a problem just with Access and maybe not with SQL or
some other database?

JGD
 
Back
Top