How to call an oracle insert function

hi Experts,

I have this senario....

I have a gridview control with data such as
0001
0002
0003
etc

when users clicks on any of the Ids (0001, etc) they are hyperlinked to another page where this sample record is displayed on a gridview

0001 | John  | Date | Result

 the result and data columns are editable and so they can insert new dates and results and hit the save button.
On clicking the save button I would like to call this function

with this c# code

 OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=**; User ID***;Password=***");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.CommandText = "he_entergrades";
        OracleParameter id = new OracleParameter("va_percentage", OracleType.Number);
        id.Direction = ParameterDirection.Input;
        OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Number);
        id.Direction = ParameterDirection.Input;    
        con.Open();    
        cmd.ExecuteNonQuery();

I am currently getting this error

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HE_ENTERGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

please how do fix this

thanks
CREATE OR REPLACE PROCEDURE HE_REPORTING_DATABASE.he_entergrades (va_id IN number, va_percentage in number, va_assessmententrydate in date)
AS 
BEGIN
    update unite.capd_assessment@utest
    set a_assessmententrydate=va_assessmententrydate,
    a_percentage=va_percentage
    where
    a_id=va_id;
    
    commit;

END;

Open in new window

SirReadAlotAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I thought your previous questions were using ODP.Net?

Anyway, check out the following code using ODP.Net:
using System;
using System.Data;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class Bob
{

	public static void Main(string[] args)
	{


			OracleConnection con = new OracleConnection();

			con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud");

			OracleCommand cmd = new OracleCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection = con;
			cmd.CommandText = " he_entergrades ";

			cmd.Parameters.Add("va_id", OracleDbType.Int64).Value = 25000028435389;
			cmd.Parameters.Add("va_percentage", OracleDbType.Int64).Value = 10;
			cmd.Parameters.Add("va_assessmententrydate", OracleDbType.Date).Value = Convert.ToDateTime("01/01/2010");

			con.Open();


			cmd.ExecuteNonQuery();

			con.Close();
			con.Dispose();

	}

}

Open in new window

0
 
DragonSlayerCommented:
Would

 OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Number);

work if replaced by

 OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Date);

?
0
 
SirReadAlotAuthor Commented:
sorry===full code that errors
 OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=**; User ID***;Password=***");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.CommandText = "he_entergrades";
OracleParameter perc = new OracleParameter("va_percentage", OracleType.Number);
        perc.Direction = ParameterDirection.Input;
        OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.Number);
        Date.Direction = ParameterDirection.Input;
  con.Open();    
        cmd.ExecuteNonQuery();  
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
the definition has 3 params, you are only passing in 2 that  I can see

0
 
SirReadAlotAuthor Commented:
no, still errored

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HE_ENTERGRADES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
0
 
sdstuberCommented:
thats is, your procedure needs 2 numbers and a date, you are only sending 2 numbers
0
 
DragonSlayerCommented:
OK, upon further inspection, some other comments:
1. You created OracleConnection twice (you're lucky C# has GC, heh).
2. SP expects 3 parameters and you only entered 2

Here's something that might work:
OracleConnection con = new OracleConnection("user id=***;password=***");
con.Open();
OracleCommand cmd = new OracleCommand("he_entergrades", con);
cmd.CommandType = CommandType.StoredProcedure;
retval.Direction = ParameterDirection.Input;
OracleParameter inval = new OracleParameter("va_id", OracleType.Number);
inval.Direction = ParameterDirection.Input;  
inval.Value = 1; // or whatever id you want to update
cmd.Parameters.Add(inval);
OracleParameter inval = new OracleParameter("va_percentage", OracleType.Number);
inval.Direction = ParameterDirection.Input;  
inval.Value = 84; // or whatever percentage it is
cmd.Parameters.Add(inval);
OracleParameter inval = new OracleParameter("va_assessmententrydate", OracleType.Date);
inval.Direction = ParameterDirection.Input;  
inval.Value = DateTime.Today; // or whatever date
cmd.Parameters.Add(inval);
 
cmd.ExecuteNonQuery();
con.Close();

Open in new window

0
 
DragonSlayerCommented:
Ooops I took a bit too long to enter the code, so many other comments now, heh :-)
0
 
SirReadAlotAuthor Commented:
@sdstuber...

didn't think i needed the 3rd one, but it still errors

cmd.CommandText = "he_entergrades";
        OracleParameter id = new OracleParameter("va_id", OracleType.Number);
        id.Direction = ParameterDirection.Input;
        OracleParameter perc = new OracleParameter("va_percentage", OracleType.Number);
        perc.Direction = ParameterDirection.Input;
        OracleParameter Date = new OracleParameter("va_assessmententrydate", OracleType.DateTime);
        Date.Direction = ParameterDirection.Input;
0
 
SirReadAlotAuthor Commented:
hang on slayer!!
0
 
sdstuberCommented:
yes, you do need all 3 parameters for the procedure you have declared above

should "OracleType.DateTime"  be "OracleType.Data"   ?

also, is HE_REPORTING_DATABASE  as schema name or a package name?
0
 
SirReadAlotAuthor Commented:
HE_REPORTING_DATABASE is a schema
0
 
SirReadAlotAuthor Commented:
Hi Slayer,
I have managed to do this with your  code

 OracleConnection con = new OracleConnection("Data Source=");
        con.Open();
        OracleCommand cmd = new OracleCommand("he_entergrades", con);
        cmd.CommandType = CommandType.StoredProcedure;

      //  retval.Direction = ParameterDirection.Input;
        OracleParameter inval = new OracleParameter("va_id", OracleType.Number);
        inval.Direction = ParameterDirection.Input;
        inval.Value = 15000028435389; // or whatever id you want to update
        cmd.Parameters.Add(inval);
        OracleParameter invalq = new OracleParameter("va_percentage", OracleType.Number);
        invalq.Direction = ParameterDirection.Input;
        invalq.Value = 23; // or whatever percentage it is
        cmd.Parameters.Add(invalq);
        OracleParameter invalw = new OracleParameter("va_assessmententrydate", OracleType.DateTime);
        invalw.Direction = ParameterDirection.Input;
        invalw.Value = DateTime.Today; // or whatever date
        cmd.Parameters.Add(invalw);
        cmd.ExecuteNonQuery();
        con.Close();


but nothing seems to be going into the database
0
 
SirReadAlotAuthor Commented:
hey
>>I thought your previous questions were using ODP.Net?
had to revert to .net provider...


will check your code
0
 
slightwv (䄆 Netminder) Commented:
>>had to revert to .net provider...

Why???  I would strongly suggest you use Oracle's provider for .Net.
0
 
DragonSlayerCommented:
Nothing goes into your database because your SP is an UPDATE, not an INSERT
0
 
SirReadAlotAuthor Commented:
>>had to revert to .net provider...

Why???  I would strongly suggest you use Oracle's provider for .Net.

Had some problems and tight deadlines but will use it again.

Our dba thinks that there is an error with the function and will resolve it soon

0
 
SirReadAlotAuthor Commented:
Hi slightwv,

I had to close this down due to the fact  I thought many had lost interest. I am still facing the same problem as described here....

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26446783.html
0
 
slightwv (䄆 Netminder) Commented:
I saw that question and it looked like a different question.

In the future the correct way to get renewed interest is to click the 'Request Attention' link at the top of the question instead of opening a duplicate one.

Duplicate questions tend to violate the Experts-Exchange rule of 500 points for a single question.
0
 
sdstuberCommented:
The other question does appear to be unrelated this one.  It's about grid iteration
this one was about passing 2 values of the wrong type into a function that needed 3 values.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.