This discussion is archived
3 Replies Latest reply: Sep 24, 2013 3:18 PM by landyman RSS

How to get value by running  in code behind?

955649 Newbie
Currently Being Moderated
Hi all,

i am running one stored procedure which return value.

i am calling that stored procedure like this.

Dim retvalue As Integer
dtformat.ShortDatePattern = "MM/dd/yyyy"
Dim YourDate As DateTime = Convert.ToDateTime(dt, dtformat).ToString("MMM/dd/yyyy")
Dim hsh As New Hashtable
objDataTier.OpenDbConnection()
con1.Open()
Dim oc As New OracleCommand("spSelectStartDay")
oc.CommandType = CommandType.StoredProcedure
oc.Parameters.Add("v_pidate", OracleType.DateTime).Value = dt
retvalue = oc.Parameters("v_poRetval").Value.ToString()

but not getting value in retvalue variable?

how to get the value in that.

my stored procedure is here.

create or replace
Procedure spSelectStartDay
(
v_pidate IN DATE DEFAULT NULL ,
v_poRetVal OUT NUMBER
)
AS
v_dpart NUMBER(10,0);
v_date DATE;
v_startweek NUMBER(10,0);
BEGIN
v_dpart := TO_Char(v_pidate,'DD') ;
v_dpart := v_dpart - 1 ;
v_date := Trunc(Trunc(v_pidate, 'MM')+1,'MM');
v_startweek := TO_CHAR(to_date(v_date, 'dd-MM-yy'), 'd');
v_poRetVal := v_startweek ;
RETURN;
END;

thanks
  • 1. Re: How to get value by running  in code behind?
    Tridus Journeyer
    Currently Being Moderated
    The first thing that comes to mind is that you haven't executed the query in that code.
  • 2. Re: How to get value by running  in code behind?
    ReubenC Explorer
    Currently Being Moderated

    You may want to specify the parameter direction (IN,OUT) in your dot.net code.  I'm not sure, but you may need to add both parameters.

  • 3. Re: How to get value by running  in code behind?
    landyman Newbie
    Currently Being Moderated

    You need to add both parameters. You should create the parameter, then specify a direction. In C# (taken from a script that executes an anonymous block, but will work for your purposes)

     

                    using (OracleConnection oCon = new OracleConnection(_connectionString))
                    {
                        oCon.Open();
                        using (OracleCommand oCmd = new OracleCommand())
                        {
                            oCmd.Connection = oCon;
                            oCmd.CommandType = CommandType.Text;
                            oCmd.CommandText = sql;
                            oCmd.BindByName = true;

                            OracleParameter opS = new OracleParameter("p_s", OracleDbType.Varchar2, 100, s, ParameterDirection.Input);
                            OracleParameter opC = new OracleParameter("p_c", OracleDbType.Int32, c, ParameterDirection.Input);
                            OracleParameter opA = new OracleParameter("v_a", OracleDbType.BinaryDouble, ParameterDirection.InputOutput);

                            oCmd.Parameters.Add(opS);
                            oCmd.Parameters.Add(opC);
                            oCmd.Parameters.Add(opA);

                            oCmd.ExecuteNonQuery();

                            a = double.Parse(oCmd.Parameters["v_a"].Value.ToString()); // note there are better ways to do this... it's quick and dirty to parse the string value.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points