How can I run two SQL commands with a single connection?

Question

I need to insert data in one table and update id in second table using add button:

private void addButton_Click(object sender, EventArgs e)
{
    con.Open();
    cmd = new SqlCommand("Insert Into Rent(toolId, customerId, custName, Fee, date, dueDate) Values('" + toolIdComboBx.Text + "', '" + custIdTxtBx.Text + "', '" + custNameTxtBx.Text + "', '" + feeTxtBx.Text + "', '" + dateTimePicker2.Text + "', '" + dateTimePicker1.Text + "')", con);

    dr = cmd.ExecuteReader();

    if (dr.Read())
    {
        con.Close();
        con.Open();

        cmd = new SqlCommand("Update Inventory Set Available = 'No' Where ToolId =  = '" + toolIdComboBx.Text + "' ");

        cmd.ExecuteNonQuery();
    }

    con.Close();
    DisplayData();
}

It is always helpful if you post any errors you get or explain unexpected behaviors. Also, please format your code so that it's actually readable and for all that is holy please parameterize your queries!!. — Broots Waymb
– Broots Waymb, Commented Apr 29, 2020 at 17:44
You should only use ExecuteReader with a SELECT. Update and Insert should both use ExecuteNonQuery — jdweng
– jdweng, Commented Apr 29, 2020 at 17:47
What are you hoping to accomplish by checking the result of dr.Read() from an INSERT? Are you only trying to UPDATE if a row was inserted? The intent of the code isn't very clear. — Code Stranger
– Code Stranger, Commented Apr 29, 2020 at 17:52
Learn to parameterize your sql statements - concatenating in the manner you use is open to sql injection. — SMor
– SMor, Commented Apr 29, 2020 at 17:55
I would like to suggest using the Entity Framework. Using it you will have much more flexibility. — Arsen Khachaturyan
– Arsen Khachaturyan, Commented Apr 29, 2020 at 18:22

Paul Keister · Accepted Answer · 2020-04-29 18:01:58Z

I can see a few issues here

Always, always, always use parameterized queries (props to @broots-waymb) and never, ever concatenate user input into a SQL command
Use the using keyword to automatically clean up any object with a Dispose() method, which includes SqlConnection and SqlCommand - this ensures proper cleanup in the presence of exceptions; also it just easier to write correctly
Use ExecuteNonQuery() if you're not expecting a recordset to be returned. As @jdweng pointed out the only query that returns a recordset is a SELECT statement (stored procedures might also). The meaning of Read() is this code is unclear, my guess is that it will always return false
Be careful when your database schema contains one table (Inventory) whose state is dependent on the state of another table (Rent). Consider strategies to avoid this, but if you can't, then you should consider wrapping the update to both tables in a database transaction to make sure the state of your system is consistent

devio · Accepted Answer · 2020-04-29 18:07:04Z

1

You cannot close a connection if it has a open SqlDataReader.

Why do you read from an INSERT statement? What do you expect?

Also, use parameterized queries.

Update

There is no result value from INSERT, so use ExecuteNonQuery() instead. That way, the connection is available for the next SqlCommand

edited Apr 29, 2020 at 18:07

answered Apr 29, 2020 at 17:46

devio

37.3k7 gold badges85 silver badges147 bronze badges

1 Comment

Teddy Over a year ago

I want to update the Available column in the Inventory table after the insert is complete

Teddy · Accepted Answer · 2020-04-29 18:31:38Z

0

Thanks guys! I figured it out

con.Open();

        using (cmd = new SqlCommand("Insert Into Rent(toolId, customerId, custName,

Fee, date, dueDate) Values('" + toolIdComboBx.Text + "', '" + custIdTxtBx.Text + "', '" +

custNameTxtBx.Text + "', '" + feeTxtBx.Text + "', '" + dateTimePicker2.Text + "', '" +

dateTimePicker1.Text + "')", con))

        {
            cmd.ExecuteNonQuery();
        }

        using (cmd = new SqlCommand("Update Inventory Set Available = 'No' Where ToolId  = '" + toolIdComboBx.Text + "' ", con))

        {
            cmd.ExecuteNonQuery();
        };

        con.Close();

        DisplayData();

answered Apr 29, 2020 at 18:31

Teddy

132 bronze badges

1 Comment

Alan Samet Over a year ago

A word of caution on using text embedded like that. You are opening yourself for a SQL Injection attack by not using parameters. A user could enter " ' OR 1=1 " in your textbox and update all rows. The correct way to do this is to use parameters. "WHERE ToolId = @ToolId" and cmd.Parameters.AddWithValue("@ToolId", toolIdComboBx.Text)

Collectives™ on Stack Overflow

How can I run two SQL commands with a single connection?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related