Passing NULL values while using Multivalue parameters

Question

Passing NULL values while using Multivalue parameters

srinivas pallikonda

Say Hey Kid

Points: 669
More actions
December 15, 2010 at 1:58 pm

#242522

I'm using multi value parameters in one of my reports and I'm calling the report from an aspx page.
My report runs just fine when I select one or more values from the drop down.
I have a default option of "ALL" in my aspx dropdowns. When selected, it should list all the records.
Problem is I'm not able to pass NULL to my stored proc from code behind when "ALL" is selected. Any pointers?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

srinivas-406082 (12/15/2010)
I'm using multi value parameters in one of my reports and I'm calling the report from an aspx page.
My report runs just fine when I select one or more values from the drop down.
I have a default option of "ALL" in my aspx dropdowns. When selected, it should list all the records.
Problem is I'm not able to pass NULL to my stored proc from code behind when "ALL" is selected. Any pointers?

One way is in your proc, set any parameter passed with 'ALL' as a value to NULL at the beginning.

IE:

IF @param1 = 'ALL'

SET @param1 = NULL

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

srinivas pallikonda Say Hey Kid Points: 669 More actions · Answer 2

Thanks for the quick reply. I think I should have mentioned it while posting the question. I'm not able to set the parameters to "ALL" or "NULL" or NULL from my aspx page.

I'm doing some thing like this

Dim strCountryList As String = ""

Dim strFetchAllRows As String = "ALL"

If lstCountry.SelectedValue <> "-ALL-" And lstCountry.SelectedValue <> "" Then

strCountryList = generateFilter(lstCountry, "Country")

paramList.Add(New ReportParameter("CountryIDs", strCountryList.Split(","), True))

Else

paramList.Add(New ReportParameter("CountryIDs", strAllRows.Split(","), True))

End If

I'm getting an error message which says "CountryIDs" parameter is missing a value.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Is strAllRows a typo?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

srinivas pallikonda Say Hey Kid Points: 669 More actions · Answer 4

My bad, it is. I changed strAllRows to strFetchAllrows to make it more clear. I forgot to change in all the places.