locked
run another sql command RRS feed

  • Question

  • User154499744 posted

    I have a button that runs a stored procedure. The click event for this button looks like this.

            protected void btn_Click(object sender, EventArgs e)
            {
                string ConnectionString = ConfigurationManager.ConnectionStrings["someDB"].ConnectionString;
                SqlConnection con = new SqlConnection(ConnectionString);
                SqlCommand cmd = new SqlCommand("dbo.someProcedure", con);
                cmd.CommandType = CommandType.StoredProcedure;
    
                cmd.Parameters.AddWithValue("@someParameter", "someParameterValue");
    
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

    As you can see, it basically runs a certain stored procedure with a certain parameter value.

    How can I do another run with a different parameter value?

    Friday, April 1, 2016 2:08 PM

Answers

  • User3690988 posted

    Before the cmd.Paramaters.AddWithValue try:

    cmd.Parameters.Clear();

    This should get rid of the too many parameters error.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 2:40 PM
  • User1633621018 posted

    Hi,

    If you are sure that you need to execute twice only then probably you can leave it as it is but if it can be three or four or more executions then consider it changing it like below:

      var str = new[] { "A", "B" };
    
                foreach (var item in str)
                {
                    string ConnectionString = ConfigurationManager.ConnectionStrings["someDB"].ConnectionString;
                    SqlConnection con = new SqlConnection(ConnectionString);
                    SqlCommand cmd = new SqlCommand("dbo.someProcedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@someParameter", item);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 2:54 PM

All replies

  • User-286291038 posted

    Hi,

    Do you require to execute another stored procedure in the same button's click event?

    If so, why not try to reuse the same command object mapped to a different storied procedure and execute it before closing the connection?

    Friday, April 1, 2016 2:18 PM
  • User154499744 posted

    Hi,

    Do you require to execute another stored procedure in the same button's click event?

    If so, why not try to reuse the same command object mapped to a different storied procedure and execute it before closing the connection?

    Yes on the same button click, I want to run the same stored proc more than once with various parameters. I'm a newbie so I don't really know how to sequence the codes to do that. I tried something like this, but got an error.

            protected void btn_Click(object sender, EventArgs e)
            {
                string ConnectionString = ConfigurationManager.ConnectionStrings["someDB"].ConnectionString;
                SqlConnection con = new SqlConnection(ConnectionString);
                con.Open();
                SqlCommand cmd = new SqlCommand("dbo.someProcedure", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@someParameter", "someParameterValue_1");
                cmd.ExecuteNonQuery();
                cmd.Parameters.AddWithValue("@someParameter", "someParameterValue_2");
                cmd.ExecuteNonQuery();
                con.Close();
            }

    The error said I specified too many parameters.

    Friday, April 1, 2016 2:26 PM
  • User3690988 posted

    Before the cmd.Paramaters.AddWithValue try:

    cmd.Parameters.Clear();

    This should get rid of the too many parameters error.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 2:40 PM
  • User154499744 posted

    Before the cmd.Paramaters.AddWithValue try:

    cmd.Parameters.Clear();

    This should get rid of the too many parameters error.

    Thanks. That solved it. So is the way I did it the standard approach? Or at least, not clunky?

    Friday, April 1, 2016 2:45 PM
  • User1633621018 posted

    Hi,

    If you are sure that you need to execute twice only then probably you can leave it as it is but if it can be three or four or more executions then consider it changing it like below:

      var str = new[] { "A", "B" };
    
                foreach (var item in str)
                {
                    string ConnectionString = ConfigurationManager.ConnectionStrings["someDB"].ConnectionString;
                    SqlConnection con = new SqlConnection(ConnectionString);
                    SqlCommand cmd = new SqlCommand("dbo.someProcedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@someParameter", item);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 1, 2016 2:54 PM
  • User3690988 posted

    I would wrap the code in using blocks.

    string ConnectionString = ConfigurationManager.ConnectionStrings["someDB"].ConnectionString;
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
     con.Open();
     using (SqlCommand cmd = new SqlCommand("dbo.someProcedure", con))
     {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@someParameter", "someParameterValue_1");
      cmd.ExecuteNonQuery();
      cmd.Parameters.Clear();
      cmd.Parameters.AddWithValue("@someParameter", "someParameterValue_2");
      cmd.ExecuteNonQuery();
     }
    }

    This automatically takes care of disposing resources.  If you do use a foreach loop, I would try putting that after the open, so you open the connection once.  Hopefully, the c# code is OK, I'm a VB'er.

    Friday, April 1, 2016 3:18 PM