Call Oracle Function + C#

[gtyagi]

CREATE FUNCTION get_bal(Daate1 in date,dev in number )
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT VOUCHER_NO 
      INTO acc_bal 
      FROM TBL_PAYMENT_TRN 
      WHERE FROM_DATE <  Daate1 AND
      DEVELOPMENT_AREA_ID = dev; 
      RETURN(acc_bal); 
    END;
/

I wnat to pass the date as a paremeter to the oracle.

 OracleCommand objCmd = new OracleCommand("get_bal", con);
                    objCmd.CommandType = CommandType.StoredProcedure;

                    objCmd.Parameters.Add("Daate1", OracleDbType.Date).Value = DateTime.Parse(ds.Tables[0].Rows[0]["FROM_DATE"].ToString()).Date ;
                    objCmd.Parameters.Add("dev", OracleDbType.Int64).Value = Session["DEV_AREA_ID"];
                    objCmd.Parameters.Add("return_value", OracleDbType.Int64).Direction = ParameterDirection.ReturnValue;
                    con.Open();
                    objCmd.ExecuteNonQuery();
                    s = objCmd.Parameters["return_value"].Value.ToString();

DateTime.Parse(ds.Tables[0].Rows[0]["FROM_DATE"].ToString()).Date ;
{10/31/2010 12:00:00 AM}
    Date: {10/31/2010 12:00:00 AM}
    Day: 31
    DayOfWeek: Sunday
    DayOfYear: 304
    Hour: 0
    Kind: Unspecified
    Millisecond: 0
    Minute: 0
    Month: 10
    Second: 0
    Ticks: 634240800000000000
    TimeOfDay: {00:00:00}
    Year: 2010

Everytime the error comes not a valid month,u can see abve in Month -10 is coming,then y it is not valid?

[SeanGrebey]

Oracle dates are a pain in the ass. You probably need to specify in your oracle function exactly what the date format is going to be, e.g.:

TO_DATE(:Mydate,'MM/DD/YYYY HH12:MI:SS AM')

If I was to take a wild guess I'd say it is probably getting your month and day crossed, so it is erroring on month 31.

[gep13]

Hey,

First up, use DateTime.TryParse, not Parse.

That way you will know if there is a error parsing the Date.

Where exactly is the error getting thrown? At the Database level?

Gary