c# doing 2 commands at the same time

Question

I'm trying to put data into my database at the same time but in different tables, I've tried to squish the query into one line but it doesn't seem to work.

private void btnBorrows_Click(object sender, EventArgs e)
{
    string connectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" + @"Data source= C:\Users\Administrator\Desktop\dtbase\Database1.accdb";

    string queryString = "SELECT Availability FROM Books WHERE ID = " + txtbxBookId.Text + "";
    string input1 = "insert into AuditTrail (MemberID, MemberName, BookID, BookTitle, DateBorrowed, ReturnDate, Status) values ('" + txtbxMId.Text + "', '" + txtbxMN.Text + "', '" + txtbxBookId.Text + "', '" + txtbxBookTitle.Text + "', '" + txtbxDateNow.Text + "', '" + txtbxReturn.Text + "', '" + txtbxStatus.Text + "')";
    string input2 = "update Books set Availability = '" + txtbxStatus.Text + "' where ID = " + txtbxBookId.Text + " ";

    try
    {
        OleDbConnection connection = new OleDbConnection(connectionString)
        OleDbCommand command = new OleDbCommand(queryString, connection);
        connection.Open();
        command.Connection = connection;
        command.CommandText = queryString;
        command.CommandText = input1;

        command.Connection = connection;
        command.CommandText = queryString;
        command.CommandText = input2;

        command.ExecuteNonQuery();
        MessageBox.Show("Borrowed!");
        return;
    }
    catch(Exception ex)
    {

    }

}

I don't know how to do it at the same time, I don't know the logic. But the program just jumps into the "input2" and ignores the "input1". The program will work well if I erased one of the two.

You just keep overwriting the value of `command.CommandText` - only the last one is executed — , Mar 04 '18 at 06:33
Stephen is correct, also please check this answer how to use DbCommand with parameters properly [using parameters inserting data into access database](https://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database) — Alexey Klipilin, Mar 04 '18 at 06:49
*At the same time*-> what do you mean? doing a `command.ExecuteNonQuery();` twice will still execute both commands sequentially. — Peter Bons, Mar 04 '18 at 08:08
You are the one ignoring input1. You overwrite it with input2. — paparazzo, Mar 04 '18 at 10:48

kahveci · Answer 1 · 2018-03-04T08:53:43.683

There is more than one problem that you must deal with.

First of all, after adding a query string, you must execute it before modifying it again (if you want to see a result). The "CommandText" property of the same OleDbCommand object gets updated three times but runs only once using the latest query (input2).

I see that two queries (input1 and input2) are related to each other in your business logic. So, you want to run them together. If this is the case, they must be run in a transaction. Otherwise, you may end up cases like having a new record on AuditTrail table, but no update for Availability on Book table. Please refer to this page for more details.

Last but not least, you must not add parameters hardcoded, which may lead to SQL injection and performance problems. I can recommend you take a look at this link and use parameterized SQL.

score 0 · Answer 2 · answered Mar 04 '18 at 10:50

0

You are overwriting command.CommandText before you execute the command

command.Connection = connection;
command.CommandText = input1;
command.ExecuteNonQuery();
command.CommandText = input2;
command.ExecuteNonQuery();

answered Mar 04 '18 at 10:50

paparazzo

44,497
23
105
176

c# doing 2 commands at the same time

2 Answers2