update info

Banishah · July 4th, 2009, 01:58 AM

HI.....now i am trying to update the contact name and phone in the Customers table by not changing the customer ID(primary key)....so here is my code...look for the highlighted error()

protectedvoid Button1_Click(object sender, EventArgs e)
{
SqlConnection conCust;
SqlCommand cmdUpdate,cmdSelect;
SqlDataReader dtrCust;
string conStr = ConfigurationManager.ConnectionStrings["ConnectionCust"].ConnectionString;
conCust = newSqlConnection(conStr);

cmdSelect = newSqlCommand("Select Customerid From Customers",conCust);
cmdUpdate = newSqlCommand("Update Customers set ContactName=@contName,phone=@con_phone Where CustomerID=@id)",conCust);
conCust.Open();
dtrCust = cmdSelect.ExecuteReader();

if (dtrCust.HasRows)
{
cmdUpdate.Parameters.AddWithValue("@contName", TextBox2.Text);
cmdUpdate.Parameters.AddWithValue("@con_phone", TextBox3.Text);

}
else
{
Label1.Text = "Customer ID cannot be updated...</br> Enter id that already exist";
}
cmdUpdate.ExecuteNonQuery();//error appear here(invalidOperationException)

conCust.Close();
Label1.Text = "Record is updated";

}
it says... there is already an open DataReader associated with this Command which must be closed first.....how am i going to close it?and i want to clarify something if i want to update the info in the table without changing the customer ID....i must read(sqlDataReader) it right?

Imar · July 4th, 2009, 03:48 AM

Hi there,

You can close the reader by calling Close() on it.

However, in your situation there's a better alternative. Don't check up front if the record exists. Just perform a regular UPDATE immediately without executing a SELECT first, and then check the return value of ExecuteNonQuery to see if any records were affected.

Code:

 
if (cmdUpdate.ExecuteNonQuery() > 0)
{
  // found
}
else
{
  // not found
}

More info:
http://msdn.microsoft.com/en-us/libr...enonquery.aspx

Hope this helps,

Imar