.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

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

Posted By:      Posted Date: August 23, 2010    Points: 0   Category :ASP.Net

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="

View Complete Post

More Related Resource Links

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

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

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

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.

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Enter or Select value for parameter

Hi all, I have a parameter 'ProjectNumber' and I want the user to be able to select a Project Number from a drop-down list OR type in the Project Number (in case of a new Project that needs to be added to the database). Is this possible?  Everything I've tried gives me the impression that it has to be one or the other - type it in or select from list. Thanks for your assistance.

Boolean Parameter to include null on selection on false

What i am trying to do is use a boolean parameter which when a user selects true only brings up true but when user selects false it brings up all nulls as well as false    Sample    Declare @Parameter1 boolean  Select  From example  Where field.1 = @Parameter1 (If parameter1 is false I want it to select all null fields as well as false)   Thanks   Michael 

Parameter For Select Statement

I Am Working On A Project,I am using MVC, VS 2010, SQL 2005, Model Is Created Using LINQ TO SQL (.dbml)Table Joining Code in My Controller Looks Like Thispublic ActionResult Index() { var DataContext = new SMSAPPDataContext(); var result = from t in DataContext.tbl_teachers from e in DataContext.tbl_teacherenrollments from b in DataContext.tbl_batches from c in DataContext.tbl_classes from s in DataContext.tbl_subjects where t.Teacher_ID == e.Teacher_ID && e.Batch_ID == b.Batch_ID && b.Class_ID == c.Class_ID && e.Sub_ID == s.Sub_ID && c.Class_Name == "TEN" && c.Class_Section == "C" && t.Teacher_Name == " ABC " select c; return View(result.ToList()); } This Table Join QUery Gives Me Data From On Table with Select Statement.And My Inherit Page In View Looks Like This<%@ Page Title="" Language=&quo

Configuring ObjectDataSource not giving me option to select correct TableAdapter

So, I have 3 table adapters in my dataset, and trying to configure the objectDataSource will not give me the option to select the correct table adapter.I set up a temporary website to illustrate what I am talking about, along with pics, not much, just 4 pics.https://sites.google.com/site/configuringobjectdatasource/The first 2 pics are irrelevant but just in case anyone thinking I don't have it set up properly. The other table adapters are working fine, which I have gridviews being populated etc.Anyways, anyone have any idea why it will not give me the option to select aspnet_GetRecordDatesTableAdapter?

How can I select Table by pussing parameter table name


How can I Create Select store proc for select Table but I will send the Table Name in Parmeter which table I want Like below.



Proc SelectAll







Including NULL columns as empty elements in SELECT FOR XML



Hi everyone,


I was wondering if it is possible for a SELECT FOR XML statement to map a row with a NULL value in a column to an empty element in XML?


For example, let's say I have the following table:


CREATE TABLE NetworkAdapter



MacAddress char(17)



The table has one row with the values (10, NULL). Can I use the SELECT FOR XML statement to return the following XML:




<MacAddress /> -- Or <MacAddress></MacAddress>, doesn't matter



Is it possible to do this without using ISNULL on the MacAddress column? Or if not, how would yo

Linq select null value

from e in Employees
where e.EmployeeID == id
select new {
Sales1 = e.HomeSales.Max(),
Sales2 = e.AwaySales.Max()};

I have to get the maximum of these two select values. The select values are nullable. How do I go about checking if the value is null before
calling the Max() method on it?

I tried the ?? coalesing operator (example: e.HomeSales.Max() ?? 0M,) but I get the following error:

"Operator '??' cannot be applied to operands of type 'decimal' and 'decimal'"

Report viewer control is is displaying parameter item 'select a value' even when the default param



I am using SSRS 2008 R2,

I have set the default parameter of dropdown and in the output "select a value" item text is removed ,also in the Report Manager 'select a value' removed .

But when I am displaying the same reports in the ReportViewer control as ReportViewer1.ServerReport  in the the vs 2010 asp.net project the item select a value is reappearing in the output.

Could you please help me remove the default item text 'select a value' from the ReportViewer control aswel.


In Crystal Reports selection formulas/select expert with formula field and parameter field together


I have this report that does not yield the same output from my crystal reports to ssrs due to below selection formula.  Any help would be much appreciated.

Is there any workaround on this combined formula of parameter and formula field i.e {@HomeCo} in {?Company}

@HomeCo as being ...

    then {view_EmpMaster_ca1.LABLEV4NM}

and {?Company} as being ...

select distinct COMP_ID,COMP_DESC
from COST_ALLON.dbo.COMP where COMP_ID NOT IN ('0110', '0610')



remove select a value in parameter dropdown


Hi all,

I need to remove the select a value option in report parameter: so i did this

="<Select a Report>"       NULL

="Score"                   ="[Measures].[Indicator Score]"

="Rank"                    ="[Measures].[Indicator Rank]"

="Value"                   ="[Measures].[Indicator Value]"

Default value


In BIDS i dont see the select a value parameter.
When rendered to report manager/reportviewer it shows Select a report but also shows Select a value before that...this is still there...

ie it shows
<Select a Value>
<Select a report>

In report manager I can go to properties and in the parameters section i can see a default value..

I deleted the parameters and re did them. I also created a new report and added jus these parameters but its still not work?? :(

I also deleted the reported and re-deployed also deleting the paremters an re-deploying fresh re

problem with SQL select statement when variable are NULL


Hello all,

I'm struggling with a strange problem.

Let me first start with giving you my SQLdatasource:


      <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT ID_PLS, Checked, Userid, DONE, Entity, Environment, TO_DO, Description, HOW_TO, LINK_TO_PROC, Overtime, Processing_date, Driver FROM PLS_DAILY WHERE (Entity = @Entity) AND (Environment = @Environment) OR (Entity = @Entity2) AND (Environment = @Environment2)">
               <asp:SessionParameter DefaultValue="" Name="Entity" SessionField="eses_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Entity2" SessionField="eb_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment2" SessionField="eb_environment" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment" SessionField="eses_environment" 
                   Type="String" />

Then let me give you the sessionfield parameters:

    Protected Sub Button1_Click(ByVa

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

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