Problem with null parameter

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

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.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, "Current"), it brings all
companies with status Current.

The problem is that when I fill using
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, DBNull.Value.tostring) to
bring all companies regardless of status, then instead of getting all
companies I get nothing. How can I get this fixed?

Thanks

Regards
 
John said:
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.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, "Current"), it brings all
companies with status Current.

The problem is that when I fill using
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, DBNull.Value.tostring) to
bring all companies regardless of status, then instead of getting all
companies I get nothing. How can I get this fixed?
Click to expand...

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)
 
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
 
John said:
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!
Click to expand...

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.
 
John said:
How does one run profiler from within vs2005?
Click to expand...

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.
 
The profiler shows that if I use this fill statement
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, DBNull.Value.tostring),
then status is passed as a 0 length string (@Status=N'').

If I use Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, 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
 
I am qite confused here:

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

DataAdapter.Fill(DateSet, string)

Assume Me.MyDataSet.Cliets 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.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, "Current") "

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

"Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, DBNull.Value.tostring) "

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.Clients 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 said:
The profiler shows that if I use this fill statement
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, DBNull.Value.tostring),
then status is passed as a 0 length string (@Status=N'').
Click to expand...

That's understandable - converting it to a string would indeed do that.
If I use Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, 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
Click to expand...
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.
 
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.ToString() 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.ToString() is being passed in as a ZLS you
should get that one row back.
 
The profiler shows that if I use DBNull.Value.tostring 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
 
Hi,

John said:
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.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, "Current"), it brings
all companies with status Current.

The problem is that when I fill using
Me.CompanyTableAdapter.Fill(Me.MyDataSet.Clients, DBNull.Value.tostring)
to bring all companies regardless of status, then instead of getting all
companies I get nothing. How can I get this fixed?
Click to expand...

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 "DataSources" 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 (CompanyTableAdapter).

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 (CompanyTableAdapter) 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.CompanyTableAdapter.Fill(Me.MyDataSet.Clients)

' get records status = current
Me.CompanyTableAdapter.FillByStatus(Me.MyDataSet.Clients, "Current")


HTH,
Greetings
 
Worked like magic! Thanks.

Regards

Bart Mermuys said:
Hi,



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 "DataSources" 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 (CompanyTableAdapter).

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 (CompanyTableAdapter) 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.CompanyTableAdapter.Fill(Me.MyDataSet.Clients)

' get records status = current
Me.CompanyTableAdapter.FillByStatus(Me.MyDataSet.Clients, "Current")


HTH,
Greetings
Click to expand...
 
what i do is the following

i set the variabel in the stored procedure from the start with NULL so it
becomes a optional parameter from VB i just skip the parameter and it does
now exactly what you want
i believe that sending Nothing will also do the trick in this case

in your declarations section of the proc
@status vartype=Null

so if nothing is send as parameter it will default to Null

regards

Michel Posseth [MCP]
 
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)
 
There is a solution by Bart in this thread which has worked for me. Seems
elegant too. Apparently you can have multiple select statements with
dataadapter in vs2005.

Regards
 
Look at the Fill method overload (it should be generated code sitting in
your project) to see how it deals with that parameter. It may expect
Nothing and then assign the actual parameter to be DBNull.Value.

I believe this may be what you want for your where clause
WHERE ((Status = @Status) OR (Status IS NULL AND @Status IS NULL))


--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

m.posseth said:
what i do is the following

i set the variabel in the stored procedure from the start with NULL so it
becomes a optional parameter from VB i just skip the parameter and it
does now exactly what you want
i believe that sending Nothing will also do the trick in this case

in your declarations section of the proc
@status vartype=Null

so if nothing is send as parameter it will default to Null

regards

Michel Posseth [MCP]
Click to expand...
 
Back
Top