CODEWITHSUNDEEP
c#sqlasp.netvisual-studiosqlcommand
Bogdan Zubar
Bogdan Zubar

Reputation: 73

Run multiple sqlcommands

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


    }

Upvotes: 1

Views: 68

Answers (2)

Bogdan Zubar
Bogdan Zubar

Reputation: 73

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

Upvotes: -1

Jonathan
Jonathan

Reputation: 817

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.

Upvotes: 2

Related Questions