CODEWITHSUNDEEP
c#sql-serversqlconnectionsqlcommand
Teddy
Teddy

Reputation: 13

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

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

Upvotes: 1

Views: 161

Answers (3)

Teddy
Teddy

Reputation: 13

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

Upvotes: 0

devio
devio

Reputation: 37225

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

Upvotes: 1

Paul Keister
Paul Keister

Reputation: 13097

I can see a few issues here

  1. Always, always, always use parameterized queries (props to @broots-waymb) and never, ever concatenate user input into a SQL command
  2. 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
  3. 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
  4. 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

Upvotes: 1

Related Questions