Re: Nullable types - am I missing something?

Tech-Archive recommends: Speed Up your PC by fixing your registry

Ops, that's what I get for typing in the dark (on the bed).

I accidentally hit the send button =P

So, picking up where I left off...

JDS wrote:


<snip>


If I have a table with several foreign key fields and I want to be
able to filter based on any combination of them with the additional
option of being able to pass null to indicate that that particular
field is not filtered. I can write a SQL query quite easily but the
problem is being able to pass either null or a valid value for each of
the fields. The only way to do it seems to be to check the value to be
passed first and then either send Nothing or the value of the
variable. This then becomes unweildy for a query with any more than a
couple of filtered fields. Maybe I need to re-think how the SQL query
is written.


Would you mind providing examples of the kind of SQL you're thinking
of using?

Besides, I'm using VB express 2008 here (no SP1) and I can assign
nullable values to parameters without problem. *But* if one of the
values is Nothing, when I execute the command an error is raised to
the lines of "the parameter ?_xx doesn't have a default value" (I'm
testing this on an Access database, and the error comes from the Jet
engine itself, not from inside .Net). On the other hand, if I assign
DBNull.Value when the nullable doesn't have a value, everything works
swell...


For an update, there is a similar problem in that I may want to update
certain fields with null values. The problem there is again passing
null values from .Net to the query. If I have to check whether each
variable is null first and pass Nothing instead of the variable value
then this becomes problematic when several fields are involved with
the possible number of permutations

Again, from what I get here, it seems to me that if you want to assign
a null value to a field, passing Nothing will not do. You should pass
DBNull.Value, instead.

Hope it helps.

Regards,

Branco
.

Quantcast