Run multiple SQL Commands on button click EDIT SOLVED

Question

I have searched here for how to run multiple sql commands on this post and for me it's not clear.

I want to run two SQL queries on button1_click. 1st is delete from table tehnicke_karakteristike where sifra_proizvoda=@sifraProizvoda and 2nd is delete from table proizvod where sifra_proizvoda=@sifraProizvoda. sifra_proizvoda is FK to tehnicke_karakteristike.

Here is my code:

        private void button1_Click(object sender, EventArgs e)
    {
       konekcija = new SqlConnection(Konekcija.konString);

        SqlCommand komanda = new SqlCommand("DELETE FROM tehnicke_karakteristike WHERE sifra_proizvoda = @sifra_proizvoda", konekcija);
        SqlParameter sifraParam = new SqlParameter("@sifra_proizvoda", SqlDbType.Int);

        SqlCommand komanda1 = new SqlCommand("DELETE FROM proizvod WHERE sifra_proizvoda = @sifra_proizvoda", konekcija);
        SqlParameter sifraParam1 = new SqlParameter("@sifra_proizvoda", SqlDbType.Int);

        komanda.Parameters.Add(sifraParam);
        komanda1.Parameters.Add(sifraParam1);

        sifraParam.Value = tbSifra.Text;
        sifraParam1.Value = tbSifra.Text;
        try
        {
            konekcija.Open();
            komanda.ExecuteNonQuery();
            komanda1.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

        }
        finally
        {
            konekcija.Close();
        }

    }

When i run this, only first command pass.

Some advice: look at "Dapper". Writing ADO.NET code yourself isn't fun, interesting, or useful. — Marc Gravell
– Marc Gravell, Commented Jun 12, 2020 at 23:26

Janilson · Accepted Answer · 2020-06-12 23:17:16Z

0

You're missing komanda1.ExecuteNonQuery() after komanda.ExecuteNonQuery(), which is why only komanda gets executed while komanda1 doesn't.

Also, it's probably a good idea to wrap those 2 commands in a transaction if you're running them like this

answered Jun 12, 2020 at 23:17

Janilson

1,2232 gold badges11 silver badges29 bronze badges

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

5 Comments

BeginnerCoding Over a year ago

i add that command line after komanda and komanda1 and i get this error: System.InvalidOperationException: 'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.'

John Wu Over a year ago

Think you need to call connection.Open()

Janilson Over a year ago

@BeginnerCoding is the komanda1 execute inside the try segment?

BeginnerCoding Over a year ago

@Lucas no it is not

Janilson Over a year ago

@BeginnerCoding then it should be. You're closing the connection inside the finally segment that gets executed after the try segment, you want to run both commands before closing the connection

Kate · Accepted Answer · 2020-06-12 23:21:53Z

When i run this, only first command pass.

That is logical: you are executing the query only once:

komanda.ExecuteNonQuery();

Therefore it's the latest assignment that prevails.

You have two queries but a shared parameter, so what you could do is reuse the query by just changing the SQL. If you have an instantiated SqlCommand with a connection property, and parameters set, you can change the CommandText property and run a different query on that SqlCommand. Not tested but I would try something like this:

    SqlCommand komanda = new SqlCommand("DELETE FROM tehnicke_karakteristike WHERE sifra_proizvoda = @sifra_proizvoda", konekcija);
    SqlParameter sifraParam = new SqlParameter("@sifra_proizvoda", SqlDbType.Int);
    komanda.Parameters.Add(sifraParam);
    sifraParam.Value = tbSifra.Text;

    // execute the 1st statement
    komanda.ExecuteNonQuery();

    // reuse the command
    komanda1.CommandText = "DELETE FROM proizvod WHERE sifra_proizvoda = @sifra_proizvoda"
    // execute the 2nd statement
    komanda.ExecuteNonQuery();

That makes the code a bit more compact but you can perfectly define two distinct SqlCommand objects - which you did. Then each has to be executed once.

Collectives™ on Stack Overflow

Run multiple SQL Commands on button click EDIT SOLVED

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related