Re: Problem with null parameter
I would need to see more of your code, but off the top of my head I would
set the @Status parameter on your query to the proper value without
converting DBNull.Value to a string. That, or pass in a value you'll never
use as a status, like maybe 'ZZ' or change the IS NULL to = ''.
WHERE (@Status = '' or @Status = Status)
"John" <John@nospam.in fovis.co.uk> wrote in message
news:OKw84StxFH A.1168@TK2MSFTN GP10.phx.gbl...[color=blue]
> The profiler shows that if I use DBNull.Value.to string then status is
> passed as a 0 length string (@Status=N''), as you have said.
>
> If I use DBNull.Value then I get the error "Value of type 'System.DBNull'
> cannot be converted to 'String'.".
>
> So what should I do to get NULL value for @status at sql server level?
>
> Thanks
>
> Regards
>
> "Michael C#" <xyz@abcdef.com > wrote in message
> news:QFD%e.2792 2$Xa.17636@fe12 .lga...[color=green]
>> Just a stab at it here, but you're converting your DBNull.Value to a
>> string before passing it in? How about if you just pass DBNull.Value in.
>> I don't even know what the DBNull.Value.To String() would return;
>> presumably a Zero-Length String, but I've never used that before so I'm
>> not sure. That would make sense, since the only thing returned would be
>> rows in which @Status = ''. Presumably you don't have any statuses with
>> ZLS' in them... If you can, try changing a single row's status to a ZLS
>> and run your second query again. If DBNull.Value.To String() is being
>> passed in as a ZLS you should get that one row back.
>>
>> "John" <John@nospam.in fovis.co.uk> wrote in message
>> news:eQCHvdrxFH A.1256@TK2MSFTN GP09.phx.gbl...[color=darkred]
>>> Hi
>>>
>>> I have a datadapter with the following SQL;
>>>
>>> SELECT ID, Company, Status, CompanyType
>>> FROM Companies
>>> WHERE (@Status IS NULL or @Status = Status)
>>>
>>> When I try to fill like so;
>>> Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, "Current"), it brings
>>> all companies with status Current.
>>>
>>> The problem is that when I fill using
>>> Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, DBNull.Value.to string)
>>> to bring all companies regardless of status, then instead of getting all
>>> companies I get nothing. How can I get this fixed?
>>>
>>> Thanks
>>>
>>> Regards
>>>[/color]
>>
>>[/color]
>
>[/color]
I would need to see more of your code, but off the top of my head I would
set the @Status parameter on your query to the proper value without
converting DBNull.Value to a string. That, or pass in a value you'll never
use as a status, like maybe 'ZZ' or change the IS NULL to = ''.
WHERE (@Status = '' or @Status = Status)
"John" <John@nospam.in fovis.co.uk> wrote in message
news:OKw84StxFH A.1168@TK2MSFTN GP10.phx.gbl...[color=blue]
> The profiler shows that if I use DBNull.Value.to string then status is
> passed as a 0 length string (@Status=N''), as you have said.
>
> If I use DBNull.Value then I get the error "Value of type 'System.DBNull'
> cannot be converted to 'String'.".
>
> So what should I do to get NULL value for @status at sql server level?
>
> Thanks
>
> Regards
>
> "Michael C#" <xyz@abcdef.com > wrote in message
> news:QFD%e.2792 2$Xa.17636@fe12 .lga...[color=green]
>> Just a stab at it here, but you're converting your DBNull.Value to a
>> string before passing it in? How about if you just pass DBNull.Value in.
>> I don't even know what the DBNull.Value.To String() would return;
>> presumably a Zero-Length String, but I've never used that before so I'm
>> not sure. That would make sense, since the only thing returned would be
>> rows in which @Status = ''. Presumably you don't have any statuses with
>> ZLS' in them... If you can, try changing a single row's status to a ZLS
>> and run your second query again. If DBNull.Value.To String() is being
>> passed in as a ZLS you should get that one row back.
>>
>> "John" <John@nospam.in fovis.co.uk> wrote in message
>> news:eQCHvdrxFH A.1256@TK2MSFTN GP09.phx.gbl...[color=darkred]
>>> Hi
>>>
>>> I have a datadapter with the following SQL;
>>>
>>> SELECT ID, Company, Status, CompanyType
>>> FROM Companies
>>> WHERE (@Status IS NULL or @Status = Status)
>>>
>>> When I try to fill like so;
>>> Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, "Current"), it brings
>>> all companies with status Current.
>>>
>>> The problem is that when I fill using
>>> Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, DBNull.Value.to string)
>>> to bring all companies regardless of status, then instead of getting all
>>> companies I get nothing. How can I get this fixed?
>>>
>>> Thanks
>>>
>>> Regards
>>>[/color]
>>
>>[/color]
>
>[/color]
Comment