New Posts New Posts RSS Feed: Stored procedure nullable parameters
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Stored procedure nullable parameters
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
sathishs View Drop Down
Newbie
Newbie


Joined: 27-Oct-2009
Location: Chennai, India
Posts: 2 		Post Options Post Options   Quote sathishs Quote  Post ReplyReply Direct Link To This Post Topic: Stored procedure nullable parameters
    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.
 
 
Back to Top
sathishs View Drop Down
Newbie
Newbie


Joined: 27-Oct-2009
Location: Chennai, India
Posts: 2 		Post Options Post Options   Quote sathishs Quote  Post ReplyReply Direct Link To This Post 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'.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 28-Oct-2009 at 9:55am

This is a bug which has been fixed in current bits.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down