.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

NULL label doesn't appear on drop down or multi-select parameter.

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
Hi, I am using SSRS 2005 SP3. I have the following query that I use for my parameter: SELECT 0 AS c1, 'NOT Specified' AS c2 UNION SELECT 1 AS c1, NULL AS c2 UNION SELECT 2 AS c1, 'Test 1' AS c2 UNION SELECT 3 AS c1, 'Test 2' AS c2 Let's call my parameter prm1. The type of prm1 is string and it has "Allow null value" and "Allow blank value" options ticked. C1 is the value field and C2 is the label field for prm1. When I preview the report, and I didn't see NULL in my drop down list,  but that record has been shown as 1 (which is the value). I am wondering if this is a normal behavior of SSRS? It's not a big deal. I can replace NULL with something else, but I am just curious. Cheers, Uzzie

View Complete Post

More Related Resource Links

ObjectDataSource CancelSelectOnNullParameter="false" // Select all when select parameter is null or


Hi ! I am using an objectdatasource in my project and I want to select all rows when the selectparameter value is empty ! I did the following but I am failling;
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DataObjectTypeName="TugberkUgurluCom_TransferModule.TransferPriceDetailsAll" SelectMethod="GetAllDataFromPricesForControl" TypeName="TugberkUgurluCom_TransferModule.TransferPriceDBComponent" UpdateMethod="UpdateTransferPrices">
    <asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="DropDownList1" Name="TpDestID" PropertyName="SelectedValue" Type="

ReportViewer 2008 multi-select parameter alignment

We are using SSRS 2008 SP1 & ReportViewer 2008 SP1. The multi-select parameter options are left aligned in the drop-down listbox on the Report Server but when run thru ASP.Net & reportViewer, they are aligned in the center of the drop-down list. This is very problematic because the width of the drop-down is also narrow compared to the text size of the fields. I tried wrapping the ReportViewer in a Div tag (<div style="vertical-align: left; text-align: left">), but still no joy.  Would appreciate any help thanks.

Update Parameter doesn't update if Null


I have a vb.net page that has an optional parameter.   If a user enters data for that field, and clicks save, it saves fine via the stored procedure.  HOWEVER, if the user changes their mind, and wants to erase the data they entered, they go to the text field, and delete the characters, and click save, but no matter what, the stored procedure will not save the fact that they erased the data (it won't set it back to NULL).  If the user enters a space, the stored procedure will save the space, and if they enter different data, it will save the other data.  I can't figure out how to get it back to letting them set it to Null?      I think this is because it's an optional parameter, and the stored proc is used by several forms (Some of those forms do not include this parameter), but this form utilizes that optional paramater. 

Here's my stored procedure...

CREATE PROCEDURE [dbo].[123456]
@hdnMatterID int,
@BillingArrangementID int,
@BillingArrangementDetails varchar(200) = NULL,   -----This is the parameter that can't seem to get set back to null if a user changes their mind and tries to erase the stored data...
@BillingFrequencyID int


UPDATE  t123456
SET BillingArrangementID = @BillingArrang

How do I - pass multiple params from multi-select drop down on one report to another via action


Report 1 - 3 params:  @Status =Y,N  @FromDate, @ToDate.  Report one shows 2 cols (Status, Total). 

 @Status gets values from shared dataset - getStatus

Status      Totals
[Status]    [Total]

I created an action [Total] when clicked runs another report that accepts the same 3 params (Status, FromDate, ToDate) and works fine if i only select one of the status' from the status param drop down.

I am trying to create an action on [sum(Total)] that will run the same 2nd report with both status' selected.  Show it should show me details of all Y and N

The second report will work if i set the 2nd report @Status param to no default value.  But i want a user to be able to run the 2nd report by itself if needed so i set the @Status param to Get Values from a Query, which get the same dataset as report 1 which is getStatus.  It will not populate the 2nd report Status field when I do this.  I am forced to set it to no default values.  Is there a way to pass both the Y and N from report 1 to report 2 and still keep the drop down multi-select box on report 2 for Status?


How to create Drop down list with multi select capabilty.


Hi All,

I want to create a multi selected drop down list using ASP.NET MVC 2.

Can anyone guide me.

How do i achieve it.

Please Help.

How to use LIKE and IN in SQL with a multi-select parameter


I have a report parameter, @antibiotics, which presently allows the user to select one value from a dropdown list.  I then use this parameter to search a string column using LIKE.  So far, this all works great.   So my WHERE statement looks like this presently:

Where Notes LIKE '%' + @antibiotics + '%'

Now I want to allow users to select multiple values for @antibiotics.  So I made the parameter multi-select. 

But now I am lost on how to construct my WHERE statement.  I tried using combinations of LIKE and IN but nothing works. 

It works if I only select one value from my dropdown list.  But if I select more than one value, I get a syntax error near "," or something.  Does anyone know how I can accomplish this?




Multi-Select with NULL option?



Using SSRS 2005 I have a detail report that takes three parameters.  Two are date parameters and are not a problem.  I use the third parameter as a lookup for a staff ID.  I have it setup as a multi-value, allowing blanks and pulls from a data set.  This basically works, however sometimes this field is left blank in the database set as NULL.

To also capture the NULL records I have the following in the WHERE clause of the dataset query:

AND (staff.staff_id IN (@staffid) OR staff.staff_id IS NULL)

The trouble with this is that if someone selects a sub set of the staff IDs the records with staff ID = NULL always gets reported too.  I would like to change this so that if you select a specific staff ID only

Use BDC data in list as multi-select column

I have a document library that uses values from another system for its metadata.  Right now, we copy the values from the primary system into SharePoint so users can choose them.  I want to find a way to use the BDC connection that I have set up to automatically pull those values.  The problem is that the current metadata is set to multi-select.  So, I have a file that is in the library and it can be used for multiple clients - clients is a column in my library and users can select many entries.  When I set up a column to pull BDC data, it only allows single entry.  Does anyone have any ideas?

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

DVWP connections with multi-select lookup columns

I have a question concerning the Data View Web Part using SharePoint Designer 2007.   I have two list (A and B). List A has a lookup column (called B-items) with multiple selections to items in List B(using the Title column from B).   I create a web part page and insert a data view of using List A. I create another data view with data from List B. Next, I make a Web Part Connection between the two data views with A passing B-items column as parameter to List B data view. I then create a filter on the List B data view with a comparison of List B’s Title column equal to the parameter of List A’s B-items value when a user selects an item from List A data view. The problem is nothing appears in List B’s data view.   When I try the reverse of the above scenario it works fine.     I understand that this is properly functionality, but is there a way to achieve my first scenario? If so, how can it be accomplished?   I have looked through the web for help and found the answer here http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/749b7477-f37f-4724-94b3-b6ace770e73a seems to be what I’m looking for. However, I am unsure how to implement his code into the web part page.   I am a worse than a novice with xsl so if anyone gives an example I would greatly appreciate step by step on what code is needed a

multi-valued parameter

I need to do something like this: DECLARE @SalespersonCode SET  @SalespersonCode = '''smith'' + ',' + ''jones'''@SalespersonCode = '''SMITH'',''JONES'',''ADAMS''' SELECT * FROM Salesperson WHERE SalespersonCode in (@SalespersonCode) but I cannot get any results to return.  Is there a way to put multiple parameters in a single variable to use with in IN statement? John Schroeder* FROM Salesperson@Salesperson Varchar(100)

how to in Insert database NULL if drop down list item is not selected ?

hi,   on my aspx I have the below DDL   <asp:DropDownList ID="ColorDDL" runat="server" DataSourceID="ColorObj"                                                 DataTextField="Size" DataValueField="BustId" Width="150px"                                                 AppendDataBoundItems="True">                                                    <asp:ListItem Value="-1">(Optional)</asp:ListItem>                             &n

SELECT name FROM sys.assemblies, to DROP all assemblies?

Is there a way using SQL for us to run a SELECT name FROM sys.assemblies WHERE LEFT(name,10) = 'SomePrefix'  /* for example */ ? We install various assemblies and they are all named with a special prefix string to help identify them as ours. I want to write some SQL that can find all of these and drop each assembly. This is an SQL question I guess, and I am no expert on SQL. Cap'n  

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Cannot select 'SQL Server Compact Edition' from Server Type drop down

I have seen this issue addressed for SQL Server 2005 but not 2008. I have installed these components:SQL Server 2008 (Browser, Management Objects, Native Client, Policies and Setup Support Files)SQL Server Compact 3.5 (for Devies, Server Tools and SP1)In the SQL Server Management Studio, on the Connect to Server dialog, the "Server Type" drop down list is disabled and "Database engine" is the default. Does anyone know what I need to do to make "SQL Server Compact Edition" a choice in the "Server Type" drop down list?

Multi select control does not work with Infopath. :(

I create new lookup column that allows multiple values.  The important part is the column is the Title(linked to item) so the item is shown as Hyperlink item to the actual item.  This works fine, but then I would like to fine tune my form with infopath.  When I open this form it complains about these columns, basically will not let me open the form without first deleting these lookup columns.  So I then try the other way I create a lookup column from infopath and this work fine, the only problem being is these items no longer so up as hyperlinks to the original items.  This is a major bumma, any one have any suggestions to get this working?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend