3 Replies Latest reply: Sep 24, 2013 5:18 PM by landyman RSS

    How to get value by running  in code behind?

    955649
      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
          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

            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

              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.