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?

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  December 15, 2010 at 2:14 pm

  #1263157

  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

  December 15, 2010 at 3:15 pm

  #1263192

  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: 47068

  More actions

  December 15, 2010 at 5:07 pm

  #1263236

  Is strAllRows a typo?

  MM
  

  ---

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

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