Run multiple sqlcommands

Question

I have a problem with executing of multiple sqlcommands. I need to update data several times. The frist sql query regards for both scenarios if,else; but then the second and third sqlcommand will be in if or else accordingly. The problem is that only one sqlcommand works properly, those which is located inside loop.

public void Button_Submit_Onclick(object sender, EventArgs e)
    {

        for (int i = 0; i < GridView2.Rows.Count; i++)
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["TestDeductionsConnectionString2"].ToString();
            int recordid = Convert.ToInt32(GridView2.DataKeys[i].Values[0]);

            CheckBox cbox = (CheckBox)GridView2.Rows[i].FindControl("CheckBox1");
            bool private1 = Convert.ToBoolean(cbox.Checked); 

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update DetailCosts set private='" + private1 + "' where recordid=" + recordid;

            con.Open();
            if (private1==true)
            {
                cmd.CommandText = "Update DetailCosts set privateCost=Costs where recordid=" + recordid;
                cmd.Parameters.AddWithValue("@private1", SqlDbType.Bit).Value = private1;
                cmd.Parameters.AddWithValue("@recordid", SqlDbType.Int).Value = recordid.ToString();

            }
            else
            {
                cmd.CommandText = "Update DetailCosts set privateCost=0 where recordid=" + recordid;
                cmd.Parameters.AddWithValue("@recordid", SqlDbType.Int).Value = recordid.ToString();
                cmd.Parameters.Add("@private1", SqlDbType.Bit).Value = private1;

            }
            cmd.ExecuteNonQuery();
            con.Close();
        }


    }

Firstly, you are only executing one query. Secondly, I think you've misunderstood the need for parameters. If you're just using a command string then just substitute the values in the string. Thirdly, loads of people will shortly lecture you about SQL injection. — spodger
– spodger, Commented Jun 12, 2018 at 7:30

Jonathan · Accepted Answer · 2018-06-12 07:26:42Z

2

You need to execute every command you want executed so you should add a

cmd.ExecuteNonQuery();

after your first query to have it also be executed.

If you want both to be executed and not if one fails you should implement transactions and wrap both queries in a transaction.

answered Jun 12, 2018 at 7:26

Jonathan

9237 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bogdan Zubar · Accepted Answer · 2018-06-12 10:44:32Z

-1

Here is a working code:

public void Button_Submit_Onclick(object sender, EventArgs e)
    {

        for (int i = 0; i < GridView2.Rows.Count; i++)
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["TestDeductionsConnectionString2"].ToString();
            int recordid = Convert.ToInt32(GridView2.DataKeys[i].Values[0]);
            //bool private1 = Convert.ToBoolean(GridView2.FindControl("CheckBox1"));
            CheckBox cbox = (CheckBox)GridView2.Rows[i].FindControl("CheckBox1");
            bool private1 = Convert.ToBoolean(cbox.Checked); 

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandText = "Update DetailCosts set private='" + private1 + "' where recordid=" + recordid;

            cmd.Parameters.AddWithValue("@private1", SqlDbType.Bit).Value = private1;
            cmd.Parameters.AddWithValue("@recordid", SqlDbType.Int).Value = recordid.ToString();
            cmd.ExecuteNonQuery();

            if (private1==true)
            {
                //DateTime date = DateTime.Now;
                //cmd.CommandText = "Update AprovedCosts set AprovedCosts.AprovalUser = ";

                cmd.CommandText = "Update DetailCosts set privateCost=Costs where recordid=" + recordid;
                cmd.ExecuteNonQuery();
            }
            else
            {

                cmd.CommandText = "Update DetailCosts set privateCost=0 where recordid=" + recordid;
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }

answered Jun 12, 2018 at 10:44

Bogdan Zubar

7312 bronze badges

1 Comment

Jonathan Over a year ago

Please review the comment on parameter binding and SQL injection from @spodger above on the parameter binding as you are still building the SQL query manually after which you think you are parameter binding, which you are not. In this way you are prone to SQL injections by manually constructing

Collectives™ on Stack Overflow

Run multiple sqlcommands

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related