How to execute 2 Sql statements with one button click

Question

I've managed to run this query using wamp.

INSERT INTO guest (guestno,familyname)
    VALUES(NULL,'Damn');      

INSERT INTO reservation (reservationno, guestno)
    VALUES(NUll,LAST_INSERT_ID())

However If I separately execute these 2 insert statements I will have a foreign key constraint. I think the both of them need to be executed at the same time.

My questions are:

How to incorporate this into my c# winform code?
Is it possible to have 2 insert statements on one button?

When the user presses "add reservation" I would like the two MySQl query's to be executed.

Here's my insert statement:

private void button7_Click(object sender, EventArgs e)
        {
    string connectionString =
            "Server=localhost;" +
        "Database=sad;" +
        "User ID=root;" +
        "Password=root;" +
        "Pooling=false";

        IDbConnection dbcon;
        dbcon = new MySqlConnection(connectionString);
        dbcon.Open();

        IDbCommand dbcmd = dbcon.CreateCommand();
        string sql = "<insert statement>";
        dbcmd.CommandText = sql;
        IDataReader reader = dbcmd.ExecuteReader();
        reader.Read();

}

UPDATED VERSION (DOESN'T WORK)

string connectionString =
         "Server=localhost;" +
         "Database=sad;" +
     "User ID=root;" +
     "Password=root;" +
     "Pooling=false";
    Form3 f3 = new Form3();

        IDbConnection dbcon;
        dbcon = new MySqlConnection(connectionString);
        dbcon.Open();
        IDbCommand dbcmd = dbcon.CreateCommand();
        string sql = "insert into guest (guestno, familyname) values (null, '" + textBox6.Text + "'); insert into reservation (reservationno, guestno) values (null, LAST_INSERT_ID())";
        dbcmd.CommandText = sql;

        IDataReader reader = dbcmd.ExecuteReader();
        reader.Read();
        MessageBox.Show("Added Guest Reservation Successfully");
        f3.guestList();
        f3.reservationList();

Updated No.3 (STILL DOESN'T WORK)

string connectionString =
             "Server=localhost;" +
             "Database=sad;" +
             "User ID=root;" +
             "Password=root;" +
             "Pooling=false";

            IDbConnection dbcon;
            dbcon = new MySqlConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            dbcmd = new MySqlCommand("CreateGuestAndReservation", dbcon);
            dbcmd.CommandType = CommandType.StoredProcedure;
            dbcmd.Parameters.AddWithValue("familyName", "foo");
            dbcmd.ExecuteNonQuery();
enter code here

Try creating a stored procedure, which will allow you to perform multiple things with only one execution call to MYSQL. — Michael Eakins
– Michael Eakins, Commented Sep 13, 2011 at 12:51

p.campbell · Accepted Answer · 2011-09-13 15:25:07Z

1

You can't execute more than one statement on a given MySqlCommand.

Your best bet all around (maintainability, performance, readability) is to:

create a MySQL stored procedure for your 2 SQL statements.
call your stored proc using ExecuteNonQuery().

DELIMITER //
CREATE PROCEDURE CreateGuestAndReservation
(
   IN familyName VARCHAR(255)
)
BEGIN
    insert into guest (guestno, familyname) 
     values (null, familyName); 

    insert into reservation (reservationno, guestno) 
    values (null, LAST_INSERT_ID());

END//
DELIMITER ;

Call it from your WinForms code like this:

dbcon.Open();
cmd = new MySqlCommand("CreateGuestAndReservation", dbcon);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("?familyName", "foo");
cmd.Parameters.Add("?familyName", MySqlDbType.VarChar,255).Value = "foo";
cmd.ExecuteNonQuery();

edited Sep 13, 2011 at 15:25

answered Sep 13, 2011 at 4:41

p.campbell

101k70 gold badges264 silver badges326 bronze badges

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

3 Comments

rj tubera Over a year ago

I am having a syntax error whenever I try to create the procedure. error near at line 10

rj tubera Over a year ago

It still has errors sir. The .Addwithvalue and the new Mysql Command. I've updated my post so that you can see what I've done

rj tubera Over a year ago

Thank you very much mister campbell :)

Austin Salonen · Accepted Answer · 2011-09-13 04:24:54Z

0

The code below should work but I suspect you may have already tried it given that you are asking for help?

string sql = "INSERT INTO guest (guestno,familyname) VALUES(NULL,'Damn'); INSERT INTO reservation (reservationno, guestno) VALUES(NUll,LAST_INSERT_ID())";

If you need parameters, try this:

string sql = "INSERT INTO guest (guestno,familyname) VALUES(NULL,?familyName); INSERT INTO reservation (reservationno, guestno) VALUES(NUll,LAST_INSERT_ID())";
...
dbcmd.Parameters.Add("@familyName", MySqlDbType.VarChar, 80).Value = _familyName;

EDIT: You may need to run 2 insert commands. See here.

edited Sep 13, 2011 at 4:24

answered Sep 13, 2011 at 3:38

Austin Salonen

50.4k16 gold badges112 silver badges140 bronze badges

3 Comments

rj tubera Over a year ago

What will I put there? I don't know how to execute my queries

rj tubera Over a year ago

I tried dbcmd.ExecuteNonQUery() however it still doesn't work

rj tubera Over a year ago

Yes, It only adds the insert statement for guest table not for reservation

Valentin Kuzub · Accepted Answer · 2011-09-13 05:18:37Z

0

I would suggest having a way to get ids other than relying on automatic id generation like autoincrements of mysql and sql server, which are very limiting. If you use a HILO id generator you first obtain id, and then execute a couple of inserts in a single transaction no problem, since you know your parent id beforehand.

It will not solve your immediate problem, but it will help tremendeously in future with your application, especially if storing parent-children like data is going to occur often.

answered Sep 13, 2011 at 5:18

Valentin Kuzub

12.2k7 gold badges60 silver badges95 bronze badges

Comments

LakshmijiV · Accepted Answer · 2013-02-26 10:46:15Z

Try this, it will work:

    private void button56_Click(object sender, EventArgs e) {
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into stholidays values('" + dateTimePicker12.Text + "','" + dateTimePicker20.Text + "','" + dateTimePicker13.Text + "','" + mbk + "','" + dateTimePicker14.Text + "','" + dateTimePicker15.Text + "','" + lt + "','" + dateTimePicker16.Text + "','" + dateTimePicker17.Text + "','" + ebk + "','" + dateTimePicker18.Text + "','" + dateTimePicker19.Text + "','" + textBox105.Text + "','" + textBox106.Text + "','" + textBox107.Text + "','" + dd + "','" + textBox104.Text + "')", con);
        SqlCommand cmd1 = new SqlCommand("insert into holidays values('" + dd + "','" + ms + "','" + day + "','" + textBox104.Text + "')", con);
        cmd.ExecuteNonQuery();
        cmd1.ExecuteNonQuery();
        con.Close();    
    }

Collectives™ on Stack Overflow

How to execute 2 Sql statements with one button click

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related