ADO.NET for OracleParameter[]

Hi,
Can You please tell me the correct way to follow  for the OracleParameter[] :


 protected void Button2_Click(object sender, EventArgs e)
        {
               OracleCommand cmd; OracleConnection  con;
            string strCon = ConfigurationManager.ConnectionStrings["NorthwindConnectionString1"].ConnectionString;
            con = new OracleConnection(strCon);
            cmd = new OracleCommand();


                con.Open(); using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                {
                  #region MyRegion
              cmd.Parameters.Clear();
                   OracleParameter[] parm = new OracleParameter[11];
                    parm[0] = cmd.Parameters.Add("@CompanyName", OracleType.VarChar, 40);
                    parm[0].Value = "ooo";
                    parm[1] = cmd.Parameters.Add("@ContactName", OracleType.VarChar, 30);
                    parm[1].Value = "ooo";
                    parm[2] = cmd.Parameters.Add("@ContactTitle", OracleType.VarChar, 30);
                    parm[2].Value = "ooo";
                    parm[3] = cmd.Parameters.Add("@Address", OracleType.VarChar, 60);
                    parm[3].Value = "ooo";
                    parm[4] = cmd.Parameters.Add("@City", OracleType.VarChar, 15);
                    parm[4].Value = "ooo";
                    parm[5] = cmd.Parameters.Add("@Region", OracleType.VarChar, 15);
                    parm[5].Value = "ooo";
                    parm[6] = cmd.Parameters.Add("@PostalCode", OracleType.VarChar, 10);
                    parm[6].Value = "ooo";
                    parm[7] = cmd.Parameters.Add("@Country", OracleType.VarChar, 15);
                    parm[7].Value = "ooo";
                    parm[8] = cmd.Parameters.Add("@Phone", OracleType.VarChar, 24);
                    parm[8].Value = "ooo";
                    parm[9] = cmd.Parameters.Add("@Fax", OracleType.VarChar, 24);
                    parm[9].Value = "ooo";
                    parm[10] = cmd.Parameters.Add("@HomePage", OracleType.VarChar);
                    parm[10].Value = "ooo";

                    cmd = con.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "testInsertSuppliers";
                    cmd.CommandType = CommandType.Text;
                    foreach (OracleParameter par in parm)
                    {
                        if (par != null)
                        {
                            cmd.Parameters.Add(par);
                        }
                    }           //  cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    da.Fill(ds, "Jobs_Master");


                    con.Close();
      #endregion
                }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                cmd.Parameters.Clear();
                con.Close();

            }
        }
prsubjectAsked:
Who is Participating?
 
käµfm³d 👽Commented:
Try like this:

protected void Button2_Click(object sender, EventArgs e)
        {
               OracleCommand cmd; OracleConnection  con;
            string strCon = ConfigurationManager.ConnectionStrings["NorthwindConnectionString1"].ConnectionString;
            con = new OracleConnection(strCon);
            cmd = new OracleCommand();


                con.Open(); using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                {
                  #region MyRegion
                  cmd.Parameters.Clear();
                OracleParameter[] parm = new OracleParameter[11];
                parm[0] = cmd.Parameters.Add(new OracleParameter("@CompanyName", "ooo"));
                parm[1] = cmd.Parameters.Add(new OracleParameter("@ContactName", "ooo"));
                parm[2] = cmd.Parameters.Add(new OracleParameter("@ContactTitle", "ooo"));
                parm[3] = cmd.Parameters.Add(new OracleParameter("@Address", "ooo"));
                parm[4] = cmd.Parameters.Add(new OracleParameter("@City", "ooo"));
                parm[5] = cmd.Parameters.Add(new OracleParameter("@Region", "ooo"));
                parm[6] = cmd.Parameters.Add(new OracleParameter("@PostalCode", "ooo"));
                parm[7] = cmd.Parameters.Add(new OracleParameter("@Country", "ooo"));
                parm[8] = cmd.Parameters.Add(new OracleParameter("@Phone", "ooo"));
                parm[9] = cmd.Parameters.Add(new OracleParameter("@Fax", "ooo"));
                parm[10] = cmd.Parameters.Add(new OracleParameter("@HomePage", "ooo"));

                    cmd = con.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "testInsertSuppliers";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddRange(parm);

                    
                    //  cmd.ExecuteNonQuery();

                    DataSet ds = new DataSet();
                    da.Fill(ds, "Jobs_Master");


                    con.Close();
      #endregion
                }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                cmd.Parameters.Clear();
                con.Close();

            }
        }

Open in new window



Off-topic:  "Northwind" is a SQL Server example database. Did you just forget to change the connection string name when you set it up?
0
 
prsubjectAuthor Commented:
Hello , thanks for the quick reply.

Actually i am doing the above sample using sqlServer .
 I have follwed the changes as you have specified but  i am getting the following error:

"The SqlParameter is already contained by another SqlParameterCollection."

0
 
CodeCruiserCommented:
On which parameter are you getting the error?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
x77Commented:
You can´nt assign a parameter from any command to other command.

Note that you are using :

   p1 = cmd.Parameters.Add("@CompanyName", OracleType.VarChar, 40);

now p1 is a Cmd parameter

Then you :

     cmd = con.CreateCommand();

You are creating a new Command.
Now you can´nt access to old cmd command, but it exists and you can access it from any previous created parameter.
0
 
prsubjectAuthor Commented:
i am getting error at
  parm[1] = cmd.Parameters.Add(new OracleParameter("@ContactName", "ooo"));
             
. so how should i proceed
0
 
CodeCruiserCommented:
What if you comment out the param[1] and let it proceed to param[2]?
0
 
käµfm³d 👽Commented:
Actually i am doing the above sample using sqlServer .
If you are using Sql Server, then why are you using Oracle commands and parameters?!?!
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.