Nullable types - am I missing something?

[JDS]

I want to be able to use effectively a table adaptor query that can
take several arguments with several of those arguments possibly null.
I have not been able to do this elegantly.

When I first came across nullable types I thought this may provide the
answer but sadly not. I can't just pass the variable value if it is
null (or nothing) but have to check HasValue first and if not pass
Nothing directly. This seems to completely miss what I thought was the
potential benefit.

So back to the original problem. What is the most effective way to
pass several arguments to a table adaptor query when several of them
could be null? Checking each value first with a whole series of if
statements is not practical when you get more than a couple of
variables with the number of possible permutations. I did come up with
one solution using a data row but this did not seem very efficient.

Am I missing something? Is there an effective way of doing this?

[Cor Ligthert[MVP]]

JDS,

Don't mix up the DBValue with other types. The Type DBValue.Null exist
solely in the database, like there are other DBValue types.

If you have set in your database that nulls are allowed (in fact columns can
be nothing), then they only thing you can do is check the data if they have
a type which has a value confirming the DBValue type. It is not for nothing
that Server 2008 has now a DBValue.Empty.

Cor

"JDS" <go****@jeremysage.comwrote in message
news:f6**********************************@c22g2000 prc.googlegroups.com...

>I want to be able to use effectively a table adaptor query that can
take several arguments with several of those arguments possibly null.
I have not been able to do this elegantly.

When I first came across nullable types I thought this may provide the
answer but sadly not. I can't just pass the variable value if it is
null (or nothing) but have to check HasValue first and if not pass
Nothing directly. This seems to completely miss what I thought was the
potential benefit.

So back to the original problem. What is the most effective way to
pass several arguments to a table adaptor query when several of them
could be null? Checking each value first with a whole series of if
statements is not practical when you get more than a couple of
variables with the number of possible permutations. I did come up with
one solution using a data row but this did not seem very efficient.

Am I missing something? Is there an effective way of doing this?

[JDS]

OK, maybe it is best to consider two separate scenarios. Firstly, a
select and secondly an update.

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.

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, I can't help feeling I am missing something.

Jeremy

[Branco Medeiros]

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

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.

[Branco Medeiros]

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

[JDS]

On Nov 4, 5:23*am, Branco Medeiros <[e-mail]:

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

OK, some examples. This is not from working code so forgive any errors
but it should demonstrate the point.

say we have a select statement for a table adaptor query
("FillBySelected") in a dataset:
SELECT RecordID, Field1, Field2, Field3 FROM MyTable
WHERE (Field1 = @Filter1 OR @Filter1 IS NULL)
AND (Field2 = @Filter2 OR @Filter2 IS NULL)
AND (Field3 = @Filter3 OR @Filter3 IS NULL)

(I know the query can be written differently but I think the above is
the most readable)

then in code we have:
dset.MyTableTableAdaptor.FillBySelected(dset.MyTab le, intValue1,
intValue2, intValue3)

The problem is that although the query can accept null values it is
difficult to pass in null values without first having a complex series
of statements to determine whether to pass a variable value or just
null. ...

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).

I think if the query parameter is set to accept null values then it
will accept "Nothing" as an argument but whether we pass Nothing or
DBNull is not really the point. The main question is how to avoid
having to test whether each variable is null or not and send a
different value. Even with just three arguments the number of
permutations makes this approach impractical.

I hope I have explained the problem adequately. As I said in the
original post, I can't help thinking I am missing something fairly
fundamental as I can't be the first person to come across this
challenge (and I had hoped nullable types would provide the answer but
having to test for "HasValue" returns us to the same problem). May be
I just need to write the query differently.

Any help greatly appreciated.

[Branco Medeiros]

JDS wrote:
<snip>

say we have a select statement for a table adaptor query
("FillBySelected") in a dataset:
SELECT RecordID, Field1, Field2, Field3 FROM MyTable
WHERE (Field1 = @Filter1 OR @Filter1 IS NULL)
AND (Field2 = @Filter2 OR @Filter2 IS NULL)
AND (Field3 = @Filter3 OR @Filter3 IS NULL)

<snip>

I think if the query parameter is set to accept null values then it
will accept "Nothing" as an argument but whether we pass Nothing or
DBNull is not really the point. The main question is how to avoid
having to test whether each variable is null or not and send a
different value. Even with just three arguments the number of
permutations makes this approach impractical.

<snip>

Well, if you are writing the table adapter yourself, then it's just a
matter of using nullable types in the appropriate arguments of the
fill method and converting to DBNull.Value if appropriate when setting
the actual parameters for the internal DbCommand used by the adapter:

<example>
ClassMyTableAdapter
'...
Sub FillBySelected(MyTable As DataTable, _
Field1 As Integer?, _
Field2 As Integer?, _
Field3 As String _
)
With Me.SelectCommand
.Parameters("Field1").Value = _
If(Field1.HasValue, Field1.Value, DBNull.Value)
.Parameters("Field2").Value = _
If(Field1.HasValue, Field2.Value, DBNull.Value)
.Parameters("Field3").Value = _
if(Field3 IsNot Nothing, Field3, DBNull.Value)
End With
Me.Fill(MyTable)
End Sub
End Class
</example>

If the adapter was generated by the wizard, you can extend it with
your own take (which would somewhat mimic the actions of the example
above).

(to extend the adapter you create a partial class in the same
namespace where the table adapter is located and add your own methods
and fields to the class)

Hope this helps,

Regards,

Branco.