.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

dynamic query with a null parameter to dont take in cosideration if null

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server

Hi guys,

Do you know in a dynamic query how can I manage if a parameter isnull or not.

declare @businessunitidname varchar(15)
set @businessunitidname = NULL;
--print @businessunitidname

declare @eu_reporthubname varchar(15);
set @eu_reporthubname = Null;
declare @ownerid varchar(15);
set @ownerid = Null;

declare @SQL varchar(4000);

SET @SQL = ';with cte as
	select ''eu_mondayamid'' as WeekName,0 as WeekVal union all
	select ''eu_mondaypmid'',0 union all
	select ''eu_tuesdayamid'',1 union all

View Complete Post

More Related Resource Links

Dynamic query for Null field checking

Hi I want dynamiclly find the table to null values finding.. Please give me quickly   Regards Ram

Dynamic query for Null field checking

Hi I want dynamically check the data Formation query in SQL server please  provide me the query

How to check for null/empty strings on dynamic objects?


What is the proper way to check for null or empty strings on dynamic objects? This is the only solution I've found so far:

if (admin.PasswordHash.GetType() == typeof(DBNull) || admin.PasswordHash == string.Empty)

If the field is null, the object returned is DBNull.Value, but if the field is empty string, it's return as a System.String object. This makes it impossible to do regular "admin.PasswordHash == DBNull.Value" check, as that will fail whenever the field has been set to empty string. Is my example the right (and only) way to go?

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


We have the following query:   SELECT H.VEHICLE_ID,                 COUNT(ISNULL(H.HISTORY_ID, 0))            FROM VEHICLES V LEFT OUTER JOIN VEHICLE_HISTORY H              ON V.VEHICLE_ID = H.VEHICLE_ID           WHERE H.SPEED >= 65             AND CONVERT(DATE, H.GPS_TIME) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))        GROUP BY H.VEHICLE_ID;   Unfortunately, if there inst a record for a vehicle for the current day in VEHICLE_HISTORY, or there inst a record over 65 in VEHICLE_HISTORY, the query simply does not include the result. How can we include all results (i.e. return 0)? What we want is the number of rows returned to match the number of rows in the vehicles table. I thought doing the LEFT OUTER JOIN would take care of this, and return a result for all vehicles.   Thanks.

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

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 

page version history gives error: "value cannot be null. parameter key: name"

Hello, I'm have customized my homepage in SharePoint Designer 2010. I want to go back to a previous version, so I right click on the aspx file in sharepoint designer and choose "version history". When I do that I get this message: There was an error connecting to the server. The server error encountered is "Value cannot be null. Parameter name: key" What should I do in order to revert my aspx page to a previous version ?

How to avoiet null value in parameter

I want to send dat from my vb.net project by store procudure but if I take a three paramater in store procudure and and and I send two parmeter value than it does not work. How can I do it without error

Value cannot be null. Parameter name: entity

BurnbetDataContext ctx = new BurnbetDataContext("data source=.\\SQLEXPRESS;Integrated Security=SSPI;" + "AttachDBFilename=|DataDirectory|\\BurnbetDatabase.mdf;User Instance=true"); tbl_Payment democustomer = ctx.tbl_Payments.SingleOrDefault(c => c.ID == 5); ctx.tbl_Payments.DeleteOnSubmit(democustomer); ctx.SubmitChanges(); If this information does not exist in the table displays this error:Value Can not BE null. Parameter name: EntityHow to solve this problem?

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

WorkFlow Event Reveiver parameter "properties.ActivationProperties" is null.

I have been trying to write the event receiver on completion of my out of box "Approval - Sharepoint 2010 workflow" set on a Site Page library. I need to update some column on completion of the workflow.

So for this I need is the list and listItem to which the workflow is attached and the column that I need to update. I understand I can get this using "properties.ActivationProperties" but what I get is null value for this. Can someone help me in this regard? I also tried reading the same in workflowStarted event but even there I get the value as null.

SQL CE 3.5 Null Parameter Check



I cannot find a definitive answer for a very simple question. In SQL CE 3.5, I am attempting to execute the following:

            using (var connection = new SqlConnection(""))

                using (var command = connection.CreateCommand())

                    command.CommandType = CommandType.Text;
                    command.CommandText = "Select * From County Where CountyId = Coalesce(@Id,3)";
                    var dbParameter = command.CreateParameter();
                    dbParameter.ParameterName = "@

VS2010 RTM bug?? Error Occurred in deployment step 'Add Solution': Value cannot be null. Parameter


I'm getting this error with Visual Studio 2010 (RTM) deploying to SharePoint 2010 (also RTM).

An "Empty SharePoint Solution" project with an OOB "Module" item added. Try to deploy and I get:

Error Occurred in deployment step 'Add Solution': Value cannot be null. Parameter name: s

The account I'm developing with has:

  • Domain Admin
  • Local Admin
  • SharePoint Farm Admin
  • Site Collection Admin
  • SQL "sysadmin"
  • SQL "DBOwner" on all config / content databases
  • UAC is disabled

The WSP package is created successfully, and if I roll it out using STSADM then it works perfectly. For some reason though Visual Studio 2010 thinks that it cannot be deployed!

Any ideas??

Martin Hatch
MCPD .Net Web Development
MCTS WSS 3.0 | MOSS 2007
Visit my Blog - www.martinhatch.com

OLEDB source task + NULL Parameter



we are using SSIS 2005 and Sql server 2005. My stored proc has 3 parameter. 2 date parameters and 1 varchar parameter. The varchar parameter is set to null as default like this:

Alter procedure uspSPName







Showing Null value in a string expression to show fullName in T-SQL query



I have a SQL query which calculates the full name from firstname, middlename and lastname. When any of the column contains a null value the calculated value shows null.


FirstName + ' ' + MiddleName + ' '+ LastName as FullName

from <table name>

FirstName : Ruben

MiddleNAme : Null

LastName : Torres

FullName : Null

Why it does not leave the null column and have the string concatanated as :

Ruben Torres




Error Value cannot be null Parameter name: type after move app to server


Hey, i have a WebSite project which i published, in the machine where i published the WebSite everything works smooth.

when i moving it to the server, i got the Error:

Value cannot be null.<br>Parameter name: type

Value cannot be null.
Parameter name: type

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: Value cannot be null.
Parameter name: type


and the details of it:


[ArgumentNullException: Value cannot be null. Parameter name: type] System.Activator.CreateInstance(Type type, Boolean nonPublic) +2843963 System.Web.Profile.ProfileBase.CreateMyInstance(String username, Boolean isAuthenticated) +76 System.Web.Profile.ProfileBase.Create(String username, Boolean isAuthenticated) +312 System.Web.HttpContext.get_Profile() +108 PasswordRecovery.get_Profile() +39 PasswordRecovery.Page_PreInit(O

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