Problem with null parameter -- dotnet windows forms databinding

dotnet windows forms databinding : Problem with null parameter

Bart Mermuys
10/1/2005 12:00:00 AM

Hi,

[quoted text, click to view]

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

[quoted text, click to view]

John
10/1/2005 12:00:00 AM

Worked like magic! Thanks.

Regards

[quoted text, click to view]

Norman Yuan
10/1/2005 2:11:10 PM

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?

[quoted text, click to view]

Michael C#
10/1/2005 6:00:13 PM

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.

[quoted text, click to view]

John
10/1/2005 7:39:16 PM

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

Jon Skeet [C# MVP]
10/1/2005 8:05:02 PM

[quoted text, click to view]

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.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet

John
10/1/2005 8:40:43 PM

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

[quoted text, click to view]

Jon Skeet [C# MVP]
10/1/2005 9:24:33 PM

[quoted text, click to view]

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.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet

John
10/1/2005 9:36:26 PM

How does one run profiler from within vs2005?

Thanks

Regards

[quoted text, click to view]

Jon Skeet [C# MVP]
10/1/2005 9:49:40 PM

[quoted text, click to view]

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.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet

John
10/1/2005 10:03:02 PM

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

[quoted text, click to view]

Jon Skeet [C# MVP]
10/1/2005 10:49:19 PM

[quoted text, click to view]

That's understandable - converting it to a string would indeed do that.

[quoted text, click to view]
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.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet

John
10/1/2005 11:08:55 PM

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

[quoted text, click to view]

Wicksy
10/1/2005 11:18:04 PM

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

[quoted text, click to view]

John
10/1/2005 11:33:41 PM

yes.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet windows forms databinding

home · blog · groups

Questions? Comments? Contact the dn