CODEWITHSUNDEEP
c#databasevisual-studio-2010insert
rj tubera
rj tubera

Reputation: 757

How to execute 2 Sql statements with one button click

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:

  1. How to incorporate this into my c# winform code?
  2. 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

Upvotes: 0

Views: 7223

Answers (4)

LakshmijiV
LakshmijiV

Reputation: 11

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

Upvotes: 0

p.campbell
p.campbell

Reputation: 100567

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

Upvotes: 1

Valentin Kuzub
Valentin Kuzub

Reputation: 12073

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.

Upvotes: 0

Austin Salonen
Austin Salonen

Reputation: 50215

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.

Upvotes: 0

Related Questions