Newbie question about dataadapter parameter

[Alphonse]

Hi,
I have a dataadapter that has 2 parameters. But sometimes I would like
to use only one of the parameters.

For example:

The dataadapter selectcommand would be like this:
SELECT CategoryID, CategoryName, Description, Picture
FROM Categories
WHERE (CategoryID = @CID) AND (CategoryName = @CName)


On My code, before filling the dataset, I would add the parameters

Mydataadapter.SelectCommand.Parameters("@CID").Value = 1
Mydataadapter.SelectCommand.Parameters("@CName").Value = "Beverages"
Mydataadapter.Fill(Mydataset)

This Code would run nicely. But how if I want to ignore one of the
parameter? For example I just want to use The @CID parameter. How to
ignore the @CName parameter?


Hope, my question is clear cause my English is not so good. All helps
will be greatly appreciated. Thank you

 

[Earl]

I think you should have a different query for the second scenario.

 

[Jerry H.]

You can't really "ignore" a Parameter for a query/stored procedure,
since SQL expects those values to be filled. However, through the
Designer, you can set default Values for each of the Parameters, so
that in your code, you would only have to set those that you
specifically needed.

Just open the SelectCommand object from the Properties window for your
Datadadapter, and then select Parameters. In the Parameters dialog
box, set each parameter's Value.

 

[William \(Bill\) Vaughn]

There are a number of ways to handle sparse parameter lists (where one or
more parameters are left off). However, these (generally) assume you're
calling a stored procedure that sets default values (used when the parameter
value is not supplied).
When you setup any query you must supply values for any parameter
placeholders you define in the query either by setting the corresponding
Parameter.Value.

One approach that I'm working on now is to use the Command.Clone function
that permits me to define a separate Command object for each variation I
want to use. In this case, I set the Value property to either a hard-coded
default value or to Nothing (in VB.NET) or Null (in C#). Of course this
assumes that you're calling a SP -- and using ADO.NET 2.0 (which adds the
"Clone" function). Until then, you'll have to build separate Command objects
by hand...

If you're using ad hoc queries (most production apps don't), you'll probably
have to create a clone (or multiple instances) of the Command and change the
SELECT's WHERE clause to reflect the changes in the desired parameter
configuration.

The bottom line: This is not that hard to do if you're using SPs.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Earl]

He didn't mention that he was using stored procedures (indeed his code
showed a static query), thus my recommendation to simply write a different
query for the 2nd scenario. Your suggestion also assumes that he is using a
typed object rather than ad hoc.

 

[Alphonse]

Yes, I didn't use SP. I Just curious, is there a simple way to "ignore"
the parameter set for dataadapter.
Maybe it is a good idea if .Net provides a way to do this. Maybe
something like
Mydataadapter.SelectCommand.Pa­rameters("@CID").Ignore


Well, Thanks anyway for all the reply

 

[William \(Bill\) Vaughn]

Ah I actually asked for this and it was turned down.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Yes, I didn't use SP. I Just curious, is there a simple way to "ignore"
the parameter set for dataadapter.
Maybe it is a good idea if .Net provides a way to do this. Maybe
something like
Mydataadapter.SelectCommand.Pa­rameters("@CID").Ignore


Well, Thanks anyway for all the reply