[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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();

            }
        }
0
prsubject
Asked:
prsubject
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now