Print Page | Close Window

Stored procedure nullable parameters

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1532
Printed Date: 08-Apr-2025 at 8:33pm


Topic: Stored procedure nullable parameters
Posted By: sathishs
Subject: Stored procedure nullable parameters
Date Posted: 27-Oct-2009 at 1:28am

I am using SQLExpress and DevForce winclient Version 5.2.2.0, License: EnterpriseWinClient, Express

I have a stored procedure with around 40 nullable parameters of string, bool, decimal and datetime type. This stored procedure retruns all records when these parameters are null(being executed in from SSMSE).

However When calling the same stored procedure with IdeaBlade Framework, I am getting an error as mentioned below.

Value cannot be null.
Parameter name: value

Stack Trace:
   at IdeaBlade.EntityModel.EntityManager.HandleEntityServerException(Exception pException, Boolean pTryToHandle, PersistenceOperation pOperation)
   at IdeaBlade.EntityModel.EntityManager.ExecuteServerQuery(IEntityQuerySurrogate pEntityQuery)
   at IdeaBlade.EntityModel.EntityManager.ExecuteFetch(IEntityFinder finder)
   at IdeaBlade.EntityModel.StoredProcFinder.Execute()
   at IdeaBlade.EntityModel.EntityManager.ExecuteQueryCore(IEntityQuery query, Boolean isAsync)
   at IdeaBlade.EntityModel.EntityManager.ExecuteQuery(IEntityQuery query)
   at DomainModel.DomainModelEntityManager.USP_POHeaderSearch_Findall(String ShipCode, Nullable`1 NotInShipCode, String PONumber, Nullable`1 NotInPONumber, String Type, String OrderType, String Status, Nullable`1 NotInStatus, String ShipRef, Nullable`1 Year, String SupplierCode, String Subject, String PoolName, Nullable`1 Priority, String RececiveShip, Nullable`1 CheckAllRecShip, String DeliveryPort, String Storage, String StorageRef, String Account, Nullable`1 NotInAccount, Nullable`1 CCNO, Nullable`1 IsBlankCC, Nullable`1 IsLinkedCC, String InvNo, String Archived, String UserID, String Proponent, String ArticleNo, String PartNo, String ContractCode, Nullable`1 IsPartlyRec, Nullable`1 IsOrderOnly, Nullable`1 IsNOOrder, Nullable`1 IsInqWithQ, Nullable`1 IsInqWithOutQ, Nullable`1 IsFirstVerOnly, Nullable`1 IsMerged, Nullable`1 IsExDeleteItems, Nullable`1 IsDangerousGoods, Nullable`1 IsNotSetDateLabel, Nullable`1 POHEAD_BID_CLOSEFromDate, Nullable`1 POHEAD_BID_CLOSEToDate, Nullable`1 pohead_order_confFromDate, Nullable`1 pohead_order_confToDate, Nullable`1 POHEAD_DEL_SHIPFromDate, Nullable`1 POHEAD_DEL_SHIPToDate, Nullable`1 pohead_inv_recFromDate, Nullable`1 pohead_inv_recToDate, Nullable`1 POHEAD_JEDFromDate, Nullable`1 POHEAD_JEDToDate, Nullable`1 pohead_podateFromDate, Nullable`1 pohead_podateToDate, Nullable`1 pohead_del_dateFromDate, Nullable`1 pohead_del_dateToDate, Nullable`1 Pohead_rec_shipFromDate, Nullable`1 Pohead_rec_shipToDate, Nullable`1 Pohead_conf_recFromDate, Nullable`1 Pohead_conf_recToDate, Nullable`1 Pohead_snt_shipFromDate, Nullable`1 Pohead_snt_shipToDate, Nullable`1 pohead_latedeldateFromDate, Nullable`1 pohead_latedeldateToDate) in C:\Demo IB ShipNet.ERP.Procurement.Prototypes\DomainModel\DomainModel.ServerModelPOIB.Designer.cs:line 98
   at ShipNet.ERP.Procurement.Common.UI.frmPOOverview.btnIB_Click(Object sender, EventArgs e) in C:\Demo IB

PS: If i provide String.Empty for a string parameter, 0 for decimal and  int parameter and DateTime.Min value for the above procedure. Stored procedure is called, but I will not be getting the desired results, since I am not passing null values.
 
Please Let me if I am missing something or this paritucular version does not support null values being passed as a stored procedure parameter.
 
 


Replies:
Posted By: sathishs
Date Posted: 27-Oct-2009 at 4:54am
I have even created a sample project and I am facing the same issue.
 
I have create a stored procedure scripted as

Create procedure [EmployeeTest]

@Beginning int = null, @Ending int = null AS

SELECT *

FROM Employee

WHERE EmployeeID Between isnull(@Beginning,0) And Isnull(@Ending,10)

DomainModelEntityManager _manager = DomainModelEntityManager.DefaultManager;

_manager.EmployeeTest(0, 5); //returns 5 records
// _manager.EmployeeTest(null, null); - Throws an errors as mentioned in the previous post.
 
It would be better, If the IB framework gives the Exact parameter name instead of saying 'Value cannot be null'.

Posted By: kimj
Date Posted: 28-Oct-2009 at 9:55am

This is a bug which has been fixed in current bits.


Print Page | Close Window