2 SQL Commands Insert and update to different tables

Question

have 2 sql commands with commands for different tables. Here is my code.

  private void button1_Click(object sender, EventArgs e)
    {
        string txtbx9 = textBox9.Text.ToString();
        string cmbbx2 = comboBox2.SelectedItem.ToString();
        string name = textBox1.Text.ToString();
        string surname = textBox2.Text.ToString();
        string company = textBox3.Text.ToString();
        string txtbx8 = textBox8.Text.ToString();
        string sts = "In House";

            try
            {
                connection.Open();


                MessageBox.Show("Payment approved.");
                richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
                 string rtb = richTextBox1.Text.ToString();

            OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')"; ;
                command.ExecuteNonQuery();
                command.CommandType = CommandType.Text;
                command.CommandText = "UPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";

                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();

        }

How can i do this 2 commands together? The program does the first sql command, but not the second one

You could remove the second command.Connection = connection; connection.Open();, just change the command and execute again, and remember to close the connection afterwards. — Keyur PATEL
– Keyur PATEL, Commented Jun 23, 2017 at 8:24
Is this for MySql, for Sql Server or for MS-Access? With MySql/Sql Server you could make one single command with two sql statements. This is not possible with access. Your problem is the second connection open when the connection is already open — Steve
– Steve, Commented Jun 23, 2017 at 8:25
@stuartd There is one right after the try { and another one before the second ExecuteNonQuery. — Keyur PATEL
– Keyur PATEL, Commented Jun 23, 2017 at 8:26
@KeyurPATEL everything worked! thanks a lot!!! you were correct! — user8110171
– user8110171, Commented Jun 23, 2017 at 8:32

Romano Zumbé · Accepted Answer · 2017-06-23 08:54:33Z

2

You could execute both commends in on command:

try
{
      connection.Open();   

      MessageBox.Show("Payment approved.");
      richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
      string rtb = richTextBox1.Text.ToString();
      command.Connection = connection;
      command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')";
      command.CommandText += "\nUPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";
      command.ExecuteNonQuery();
}

Or just execute them one after the other:

try
{
      connection.Open();   

      MessageBox.Show("Payment approved.");
      richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
      string rtb = richTextBox1.Text.ToString();
      command.Connection = connection;
      command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')";
      command.ExecuteNonQuery();
      command.CommandText = "UPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";
      command.ExecuteNonQuery();
}

EDIT:

As Steve mentioned (and he is absolutely right), parameters should be passed as SqlParameters. The benefits are a better safety against SQL injection and you can be sure, that unexpected inputs like 'O'Neil' don't break your code (parameter names could be better):

try
{
      connection.Open();   

      MessageBox.Show("Payment approved.");
      richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
      string rtb = richTextBox1.Text.ToString();
      command.Connection = connection;
      command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES(@name,@surname,@company,@txtbx8,@txtbx9,@cmbbx2,@rtb,@label12Text,@dateTimePicker1Value,@textBox4Text,@textBox5Text,@textBox6Text')";
      command.Parameters.Add(new SqlParameter("@name",name));
      command.Parameters.Add(new SqlParameter("@surname",surname));
      command.Parameters.Add(new SqlParameter("@company",company));
      command.Parameters.Add(new SqlParameter("@txtbx8",txtbx8));
      command.Parameters.Add(new SqlParameter("@txtbx9",txtbx9));
      command.Parameters.Add(new SqlParameter("@cmbbx2",cmbbx2));
      command.Parameters.Add(new SqlParameter("@rtb",rtb));
      command.Parameters.Add(new SqlParameter("@label12Text",label12.Text.ToString()));
      command.Parameters.Add(new SqlParameter("@dateTimePicker1Value",this.dateTimePicker1.Value.ToString()));
      command.Parameters.Add(new SqlParameter("@textBox4Text",textBox4.Text.ToString()));
      command.Parameters.Add(new SqlParameter("@textBox5Text",textBox5.Text.ToString()));
      command.Parameters.Add(new SqlParameter("@textBox6Text",textBox6.Text.ToString()));
      command.ExecuteNonQuery();
      command.CommandText = "UPDATE guestreg SET g_paidstatus=@paidStatus where g_name =@name and g_status = @status";
      command.Parameters.Add(new SqlParameter("@paidStatus","Paid " + txtbx9));
      command.Parameters.Add(new SqlParameter("@name",name));
      command.Parameters.Add(new SqlParameter("@status",sts));
      command.ExecuteNonQuery();
}

edited Jun 23, 2017 at 8:54

answered Jun 23, 2017 at 8:25

Romano Zumbé

8,1495 gold badges37 silver badges61 bronze badges

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

8 Comments

Steve Over a year ago

This will not work with MS-Access (and you forgot the semicolon between the two command texts)

Romano Zumbé Over a year ago

I didn't know this was for Access. On SQL Server there is no need for a semicolon between commands

Steve Over a year ago

The OP tagged 3 databases. Only him/her knows

Romano Zumbé Over a year ago

Ok, but my second suggestion (after Edit) should work then

Steve Over a year ago

If you don't care to give complete answers and recommend the best practices then you have little interest in making your answer a good reference for future readers and thus you should expect someone that criticize your work

|

Kevin · Accepted Answer · 2017-06-23 08:31:33Z

2

There are many ways to do that but for me the easiest way is to close and re do it like this:

SqlCommand importCommand = new SqlCommand("select * from * ", connection);
        SqlDataReader sqlDR = importCommand.ExecuteReader();
        int index = 0;
        while (sqlDR.Read()) { //something }
        sqlDR.Close();

        index = 0;
        importCommand = new SqlCommand("select * from * ", connection);
        sqlDR = importCommand.ExecuteReader();
        sqlDR.Close();

answered Jun 23, 2017 at 8:31

Kevin

714 bronze badges

Comments

Tien Nguyen Ngoc · Accepted Answer · 2017-06-23 08:26:20Z

You can use this. You have to create command two time.

private void button1_Click(object sender, EventArgs e)
{
string txtbx9 = textBox9.Text.ToString();
string cmbbx2 = comboBox2.SelectedItem.ToString();
string name = textBox1.Text.ToString();
string surname = textBox2.Text.ToString();
string company = textBox3.Text.ToString();
string txtbx8 = textBox8.Text.ToString();
string sts = "In House";

    try
    {
        connection.Open();


        MessageBox.Show("Payment approved.");
        richTextBox1.Text = richTextBox1.Text + "The hotel received " + txtbx9 + " from this guest";
            string rtb = richTextBox1.Text.ToString();

        OleDbCommand command = new OleDbCommand();

        command.Connection = connection;
        command.CommandText = "INSERT INTO billing(g_name,g_surname,g_company,g_totalrate, g_paid, g_typepaid, info, u_add, u_tadd, g_ad, g_dd, g_amountofdays) VALUES('" + name + "','" + surname + "','" + company + "','" + txtbx8 + "', '" + txtbx9 + "', '" + cmbbx2 + "', '" + rtb + "', '" + label12.Text.ToString() + "', '" + this.dateTimePicker1.Value +"','"+textBox4.Text.ToString()+"','"+textBox5.Text.ToString()+"','"+textBox6.Text.ToString()+"')"; ;
        command.ExecuteNonQuery();

        command = new OleDbCommand();
        command.Connection = connection;
        command.CommandText = "UPDATE guestreg SET g_paidstatus='Paid '"+txtbx9+"'' where g_name ='"+name+"' and g_status = '"+sts"'";

        command.ExecuteNonQuery();

}
}

Collectives™ on Stack Overflow

2 SQL Commands Insert and update to different tables

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related