Problem with null parameter

[John]

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

[Jon Skeet [C# MVP]]

Re: Problem with null parameter

John <John@nospam.in fovis.co.uk> wrote:[color=blue]
> 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?[/color]

Well, it's doing exactly what you've asked it to. When @Status is null,
your WHERE clause is:

WHERE (NULL IS NULL OR NULL = Status)

Now, the second clause is obviously never true - but the first one is
*always* true!


I *suspect* you meant:

WHERE (@Status IS NULL AND Status IS NULL) OR (@Status=Status )

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[John]

Re: Problem with null parameter


I *am* looking for the clause WHERE (NULL IS NULL OR NULL = Status) so that
it is true for when null value is sent for @status and brings back all
records. Problem is it is returning none!

Thanks

Regards

"Jon Skeet [C# MVP]" <skeet@pobox.co m> wrote in message
news:MPG.1da8ee 354d9249f98c83b @msnews.microso ft.com...[color=blue]
> John <John@nospam.in fovis.co.uk> wrote:[color=green]
>> 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?[/color]
>
> Well, it's doing exactly what you've asked it to. When @Status is null,
> your WHERE clause is:
>
> WHERE (NULL IS NULL OR NULL = Status)
>
> Now, the second clause is obviously never true - but the first one is
> *always* true!
>
>
> I *suspect* you meant:
>
> WHERE (@Status IS NULL AND Status IS NULL) OR (@Status=Status )
>
> --
> Jon Skeet - <skeet@pobox.co m>
> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
> If replying to the group, please do not mail me too[/color]

[Jon Skeet [C# MVP]]

Re: Problem with null parameter

John <John@nospam.in fovis.co.uk> wrote:[color=blue]
> I *am* looking for the clause WHERE (NULL IS NULL OR NULL = Status) so that
> it is true for when null value is sent for @status and brings back all
> records. Problem is it is returning none![/color]

I do apologise - I misread your previous post.

Hmm - have you put a profiler onto SQL Server to check what value is
actually being submitted in the query? I'm afraid I can't easily test
it myself at the moment.

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[John]

Re: Problem with null parameter

How does one run profiler from within vs2005?

Thanks

Regards


"Jon Skeet [C# MVP]" <skeet@pobox.co m> wrote in message
news:MPG.1da900 db39e1594c98c83 c@msnews.micros oft.com...[color=blue]
> John <John@nospam.in fovis.co.uk> wrote:[color=green]
>> I *am* looking for the clause WHERE (NULL IS NULL OR NULL = Status) so
>> that
>> it is true for when null value is sent for @status and brings back all
>> records. Problem is it is returning none![/color]
>
> I do apologise - I misread your previous post.
>
> Hmm - have you put a profiler onto SQL Server to check what value is
> actually being submitted in the query? I'm afraid I can't easily test
> it myself at the moment.
>
> --
> Jon Skeet - <skeet@pobox.co m>
> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
> If replying to the group, please do not mail me too[/color]

[Jon Skeet [C# MVP]]

Re: Problem with null parameter

John <John@nospam.in fovis.co.uk> wrote:[color=blue]
> How does one run profiler from within vs2005?[/color]

Sorry, I don't mean a .NET profiler - I mean a SQL profiler. Just run
it up separately against the SQL database you're sending the query to,
and you should see the query including its parameters.

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[John]

Re: Problem with null parameter

The profiler shows that if I use this fill statement
Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, DBNull.Value.to string),
then status is passed as a 0 length string (@Status=N'').

If I use Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, 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


"Jon Skeet [C# MVP]" <skeet@pobox.co m> wrote in message
news:MPG.1da906 bf7cf27e798c83d @msnews.microso ft.com...[color=blue]
> John <John@nospam.in fovis.co.uk> wrote:[color=green]
>> How does one run profiler from within vs2005?[/color]
>
> Sorry, I don't mean a .NET profiler - I mean a SQL profiler. Just run
> it up separately against the SQL database you're sending the query to,
> and you should see the query including its parameters.
>
> --
> Jon Skeet - <skeet@pobox.co m>
> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
> If replying to the group, please do not mail me too[/color]

[Norman Yuan]

Re: Problem with null parameter

I am qite confused here:

you used one of the overload DataAdapter.Fil l() method that takes two
paramters( second on is a string), so it must be:

DataAdapter.Fil l(DateSet, string)

Assume Me.MyDataSet.Cl iets IS a DataSet (it looks like a DataTable to me,
though), the string parameter should be the mapped DataTable name in the
DataSet.

So, following code

"Me.CompanyTabl eAdapter.Fill(M e.MyDataSet.Cli ents, "Current") "

fills data into a mapped DataTablcalled "Current" in the DatSet. while the
code

"Me.CompanyTabl eAdapter.Fill(M e.MyDataSet.Cli ents, DBNull.Value.to string) "

fills data into a mapped DataTable called "".

Obviously, regardless whether your "SELECT..." statement is correct or not,
the data (if they get retrieved from database) are filled into different
tables in the DataSet

However, if Me.MyDataSet.Cl ients is a DataTable, I wondered why you did not
get a "Type mismatch" error, since you passed wrong type to the overloaded
Fill() method. Did i misunderstand something here?


"John" <John@nospam.in fovis.co.uk> wrote in message
news:eQCHvdrxFH A.1256@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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]

[Jon Skeet [C# MVP]]

Re: Problem with null parameter

John <John@nospam.in fovis.co.uk> wrote:[color=blue]
> The profiler shows that if I use this fill statement
> Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, DBNull.Value.to string),
> then status is passed as a 0 length string (@Status=N'').[/color]

That's understandable - converting it to a string would indeed do that.
[color=blue]
> If I use Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts, 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[/color]
level?

Have you tried passing in a straight null reference?

To be honest, I'm far from an ADO.NET expert, but that's really where
you need to be asking, I think. I've trimmed the follow-ups to this
post appropriately.

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Michael C#]

Re: Problem with null parameter

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=blue]
> 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]

[John]

Re: Problem with null parameter

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=blue]
> 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=green]
>> 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]

[Wicksy]

Re: Problem with null parameter

Just curious... is the Status field in your table set to allow Null values?


"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]

[Bart Mermuys]

Re: Problem with null parameter

Hi,

"John" <John@nospam.in fovis.co.uk> wrote in message
news:eQCHvdrxFH A.1256@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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?[/color]

It looks like you are using the new TableAdapter generated by wizard in
VS2005, which is used if you drag table onto the Form.

The idea is to change the query you already have so that it gets all
records. Then *add* another query that filters on Status.

1. Open "DataSource s" window ( Data -> Show Data Sources ).

2. Right click on the relevant DataSet ( MyDataSet ) and choose "Edit
DataSet with Designer".
Now you should visually see all your DataTables and TableAdapters, look for
the relevant TableAdapter (CompanyTableAd apter).

3. Under the relevant TableAdapter you should see a row with Fill, GetData.
Click on that row and then right click and choose properties. Then change
the CommandText of the query so that it uses SELECT * FROM Companies; or
make it use a stored procedure that does the same.

4. Right click on the relevant TableAdapter (CompanyTableAd apter) and choose
"Add Query".
Follow the wizard and make sure this time you're using a query like:
SELECT * FROM Companies WHERE Status = @Status;
! In the last step change FillBy to FillByStatus and change GetBy to
GetByStatus.


Now, you have two ways to fill your dataset:

' get all records
Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts)

' get records status = current
Me.CompanyTable Adapter.FillByS tatus(Me.MyData Set.Clients, "Current")


HTH,
Greetings


[color=blue]
>
> Thanks
>
> Regards[/color]

[John]

Re: Problem with null parameter

yes.

"Wicksy" <wicksee@nosp am-yahoo.com> wrote in message
news:dhn1ut$3pr $1@news.freedom 2surf.net...[color=blue]
> Just curious... is the Status field in your table set to allow Null
> values?
>
>
> "John" <John@nospam.in fovis.co.uk> wrote in message
> news:OKw84StxFH A.1168@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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=darkred]
>>> 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...
>>>> 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]

[John]

Re: Problem with null parameter

Worked like magic! Thanks.

Regards

"Bart Mermuys" <bmermuys.nospa m@hotmail.com> wrote in message
news:uZ7GuetxFH A.736@tk2msftng p13.phx.gbl...[color=blue]
> Hi,
>
> "John" <John@nospam.in fovis.co.uk> wrote in message
> news:eQCHvdrxFH A.1256@TK2MSFTN GP09.phx.gbl...[color=green]
>> 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?[/color]
>
> It looks like you are using the new TableAdapter generated by wizard in
> VS2005, which is used if you drag table onto the Form.
>
> The idea is to change the query you already have so that it gets all
> records. Then *add* another query that filters on Status.
>
> 1. Open "DataSource s" window ( Data -> Show Data Sources ).
>
> 2. Right click on the relevant DataSet ( MyDataSet ) and choose "Edit
> DataSet with Designer".
> Now you should visually see all your DataTables and TableAdapters, look
> for the relevant TableAdapter (CompanyTableAd apter).
>
> 3. Under the relevant TableAdapter you should see a row with Fill,
> GetData.
> Click on that row and then right click and choose properties. Then change
> the CommandText of the query so that it uses SELECT * FROM Companies; or
> make it use a stored procedure that does the same.
>
> 4. Right click on the relevant TableAdapter (CompanyTableAd apter) and
> choose "Add Query".
> Follow the wizard and make sure this time you're using a query like:
> SELECT * FROM Companies WHERE Status = @Status;
> ! In the last step change FillBy to FillByStatus and change GetBy to
> GetByStatus.
>
>
> Now, you have two ways to fill your dataset:
>
> ' get all records
> Me.CompanyTable Adapter.Fill(Me .MyDataSet.Clie nts)
>
> ' get records status = current
> Me.CompanyTable Adapter.FillByS tatus(Me.MyData Set.Clients, "Current")
>
>
> HTH,
> Greetings
>
>
>[color=green]
>>
>> Thanks
>>
>> Regards[/color]
>
>[/color]