problem: I was unable to the the new value out of a SP inout parm executed from .net 4.5// simplified to highlight the inout parm being null always, no problem with out parm though private DataTable daily_holdg_detl_variance(DateTime valuation_dt, int acct_grp_id, ref DateTime? valuation_dt_dif, out string Msg) { SAConnection myConnection = ModuleDb.myConnection; // a c# module to setup the connection...
the stored proc for sa 11.0.1.2960 is like: PROCEDURE "dba"."pf_daily_holdg_detl_variance"( in @valuation_dt date, in @acct_grp_id integer, inout @valuation_dt_dif date default null ) if (@valuation_dt_dif is null) then select max(valuation_dt) into @valuation_dt_dif from dba.dba.pf_daily_holdg_detl //pf_dailyvaluation where valuation_dt between dateadd(day,-40,@valuation_dt) and dateadd(day,-1,@valuation_dt) and acct_grp_id=@acct_grp_id; end if; end I verified I was to able to get non null valuation_Dt_dif in isq with begin declare @test decimal
endand used the same set of values to test against the c# member but got null valuation_dt_dif where did I go wrong? |
Just change the SQL of your command to something like
then you will get your result set without the hassle of the Parameter objects. |
Historical remark. 7-8 years ago I had problems with stored procedures that simultaneously return data via resultset and out params. Using ODBC/ADO invocation of that kind of procedures always return resultset, but out params was empty. So, I think that ODBC/ADO SA drivers has bug, that still don't resolved. Hm, in my understanding the procedure in question does not return a result set. Therefore I would think the problem with accessing both a result set and inout/out parameters and return values from a procedure (that is still an existing problem/feature AFAIK) should not apply here...
(20 May '14, 05:42)
Volker Barth
Volker, you are absolutely right, but from centures to centures DB vendors and especially sybase still produce buggy client side drivers. Anybody know, that native drivers (ctlib/cli/etc) has much less bugs and problems. gg99 wrote, that in ISQL there are no problems, so probably problems in client side drivers or .NET DB-side library.
(20 May '14, 06:30)
AlexeyK77
Comment Text Removed
Comment Text Removed
I was hoping it is something I did wrong instead of the .net or odbc driver. hated to delve into the sql and add that column. that require a lot of test scenario. is it bad idea/practice to return that date as another result set and let the c# member to pick that up? or just code the default valuatn_dt_dif as separate function to let the c# call and the stored proc to call I suspect I will run into the same problem in sa 16 in the future. unless someone find it otherwise
(21 May '14, 00:45)
gg99
Replies hidden
As Volker has correctly seen, your stored procedure is not returning a result set, so using the SADataAdapter might not help, try the ExecuteNonQuery instead.
(21 May '14, 02:48)
Martin
|
A DateTime value cannot be null in .NET. You are likely getting "1/1/0001 12:00:00 AM" returned since that is the declared value of DateTime if not set with a value. If you know that 1/1/0001 is not a valid Date value for your implementation, you will need to check for that case and explicitly set the parameter value to null as in:
I was using DateTime? valuation_dt_dif; so I was able to store null value there. if (parm_Valuatn_Dt_dif.Value !=DBNull.Value) valuation_Dt_dif=Util.object2DateTime(parm_Valuatn_Dt_dif.Value)
Also I stepped thru the c# side and I did see "no" value not any date value in parm_Valuatn_Dt_dif.Value btw. the actual stored proc does return result set but the sql are quite quite long and complex, has a series of if else, and most have unions
I am not following how you set DateTime to a value of NULL, be it a NULL or DBNull.Value. The following is reported when I attempt to do so: 1) Cannot convert null to 'System.DateTime' because it is a non-nullable value type 2) Cannot implicitly convert type 'System.DBNull' to 'System.DateTime'
Regardless, I get the expected behaviour if I set the parameter value to null or DBNull.Value.
I recommend that you debug the procedure to see what value it is receiving from the client. I think that you will find that it is not being received as a NULL value as you expect.