CODEWITHSUNDEEP
asp.net-2.0
stack_pointer is EXTINCT
stack_pointer is EXTINCT

Reputation: 2393

multiple instances of named argument in list

I'm getting this error in ASP.NET application (whereas it works fine in Oracle)

PLS-00703::multiple instances of named argument in list

I've attached the code here. It shows error in the last line i.e. cmd.ExecuteNonQuery()

    cmd.Parameters.AddWithValue("in_prog_id", 2);
    cmd.Parameters.AddWithValue("in_sllr_co_id", 1);
    cmd.Parameters.AddWithValue("in_sllr_purch_loc_nbr", 1);
    cmd.Parameters.AddWithValue("in_byr_co_id", Convert.ToInt32(co_id));
    cmd.Parameters.AddWithValue("in_byr_purch_loc_nbr", purch_loc_nbr);
    cmd.Parameters.AddWithValue("in_ing_id", ing_id);
    cmd.Parameters.AddWithValue("in_per_id", per_id);
    cmd.Parameters.AddWithValue("in_ing_purch_qty", "12");
    cmd.Parameters.AddWithValue("in_pop_sl_trck_nbr", "ECAP");
    cmd.Parameters.AddWithValue("in_pop_sl_cmnt_txt",  dstemp.Tables["ECAP"].Rows[i][6].ToString());
    cmd.Parameters.AddWithValue("in_chg_by", 1333);
    cmd.Parameters.AddWithValue("in_ing_rev_amt", dstemp.Tables["ECAP"].Rows[i][5].ToString());
    cmd.Parameters.AddWithValue("in_sl_typ", "ME");
    cmd.Parameters.AddWithValue("in_dir_sale_ind", "DIR-SL");
    cmd.Parameters.AddWithValue("in_intg_sl_ind", "N");  ***//character as input***
    cmd.ExecuteNonQuery();

This is my method inside which the above statements hold in.

private void call_proc(int i, DataSet dstemp, OracleCommand cmd)
{


}

and I invoke this method as follows:

    cmd1 = conn3.CreateCommand();
    cmd1.CommandText = "pkg_sale.cre_pop_sl";
    cmd1.CommandType = CommandType.StoredProcedure;
    for (int i = 0; i < ds.Tables["ECAP"].Rows.Count; ++i)
        call_proc(i, ds, cmd1);

Is there any problem in this?

Upvotes: 1

Views: 5847

Answers (3)

Uchitha
Uchitha

Reputation: 1038

I came across another scenario where this could happen if you are using ODP.Net. If you are using any utility classes to generate your command (Like I do in my project), it could be doing some 'smart' work behind the scene to derive parameters from the stored procedure.

 OracleCommandBuilder.DeriveParameters((OracleCommand)command);

This statement injects the parameters in to the Command object and if you add your parameters to the same cmd object (Like you would normally do) it will have 2 parameters with the same name.

Upvotes: 0

Cerebrus
Cerebrus

Reputation: 25775

This appears to be a common error with Oracle stored procedures and the most common cause is that the developer neglected to call Parameters.Clear before repeated invocation of the code.

Either that, or one or more of your parameters is being repeated someplace else.

Edit (after OP's post)

It appears to be the first possibility because you are calling the function in a loop. Just Clear the parameters within the looped function and it should work.

See this reference

Upvotes: 1

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391576

Are you reusing the instance in cmd for multiple SQL statements perhaps?

There's no duplicates in what you've posted here so I doubt this code alone is causing the problem.

Upvotes: 1

Related Questions