Reputation: 13
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
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
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
Reputation: 13097
I can see a few issues here
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 correctlyExecuteNonQuery()
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 falseUpvotes: 1