Some how the update command is not working.

Some how the update command is not working.

• Sweeps78

  hi

  I dont have SQL profiler, I using SQL Server Express

  private void UpdatecustomerDetails() { int ID = Convert.ToInt32(Request.QueryString["ID"]); SqlConnection connect = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True"); SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " + " SET [CompanyName]=@Name,[City]=@City " + " WHERE [ID]=@ID", connect); update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50); update_customer_details.Parameters["@Name"].Value = "New Customer Name"; //The old customer name was Test update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50); update_customer_details.Parameters["@City"].Value = "New City"; //The old customer city was Koikoi update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4); update_customer_details.Parameters["@ID"].Value = 4; //Where the customer ID is 4 connect.Open(); int x = update_customer_details.ExecuteNonQuery(); connect.Close(); StatusLabel.Text = x.ToString(); }

  
  
  

• dougzhoez

  Thanks again Sarah

  I will look at this on the weekend since it looks a bit complicated I will need more time.

  I will contact you for assistance

  
  
  

• fiatlux

  Have you reviewed the suggestions in this FAQ post

  FAQ: My database isn't being updated, but no errors occurred in my application
  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=427451&SiteID=1

  It sounds like you could be running into Problem #2 described in that post. If this were the case it should happen for Deletes as well, but I would check it just to make sure this is not happening to you.

  Thanks,
  Sarah

  Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.
  

  
  
  

• Enlikil

  Hi,

  I manage to have the application updating using if (IsPostBack == false), some how when the btnCreate button is clicked the function protected void Page_Load(object sender, EventArgs e) runs again/refreshes which retrives the old values in the database. The application was running properly it’s a slight mistake I didn’t pick up (if (IsPostBack == false)).

   This is what the code looks like:-

  protected void Page_Load(object sender, EventArgs e) { if (IsPostBack == false) { int ID = Convert.ToInt32(Request.QueryString["ID"]); SQLEXPRESSCONNECTION SqlDB = new SQLEXPRESSCONNECTION(); SqlConnection connect = new SqlConnection(SqlDB.RETCONNECTION()); SqlCommand cmd_get_customer_details = new SqlCommand("SELECT CompanyName,Code,City FROM app_CustomerDetails WHERE ID=@ID", connect); cmd_get_customer_details.Parameters.Add("@ID", SqlDbType.NVarChar, 50); cmd_get_customer_details.Parameters["@ID"].Value = ID; connect.Open(); SqlDataReader read_customer_details = cmd_get_customer_details.ExecuteReader(); while (read_customer_details.Read()) { txtCustomerName.Text = read_customer_details.GetValue(0).ToString(); txtCustomerCode.Text = read_customer_details.GetValue(1).ToString(); txtCity.Text = read_customer_details.GetValue(2).ToString(); } read_customer_details.Close(); connect.Close(); } } protected void btnCreate_Click(object sender, EventArgs e) { try { int ID = Convert.ToInt32(Request.QueryString["ID"]); SQLEXPRESSCONNECTION get_con = new SQLEXPRESSCONNECTION(); SqlConnection connect = new SqlConnection(get_con.RETCONNECTION()); connect.Open(); SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " + " SET [CompanyName]=@CompanyName,[City]=@City" + " WHERE ([ID]=@ID)", connect); update_customer_details.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 50); update_customer_details.Parameters["@CompanyName"].Value = txtCustomerName.Text; update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50); update_customer_details.Parameters["@City"].Value = txtCity.Text; update_customer_details.Parameters.Add("@ID", SqlDbType.Int); update_customer_details.Parameters["@ID"].Value = ID; int x = update_customer_details.ExecuteNonQuery(); connect.Close(); } catch (Exception ex) { StatusLabel.Visible = true; StatusLabel.Text = ex.Source + " : " + ex.Message; } }

  I thank you all for your help

  
  
  

• Jeremy Jarrell

  Hi Sarah, Thanks for the reply and the link

  My application is a web app and the database is stored in the App_Data\ASPNETDB.MDF and this database is created buy the application whenever I use the login controls within ASP.net and I embed my own tables within this database. whats funny is the ChangePassword Control is working when the user update the password with the new one. I will I assume that the ChangePassword control also uses and update SqL Query if not what is it using so that I can use it as well. Fair enough my application can insert/delete data but cannot update the data. is there a bug in my application.

  I have tried to update the data using following but its not working:-

  • SqlCommand within C#.
  • Using a dataset.xsd
  • Stored procedure

  Whenever I run the stored procedure/SQL Query in VS2005 or SQL Server Mgmt Studio Express it works fine the problem starts when the database is talking to the aplication.

  Is there another way I can use, please help.

  
  
  

• barkingdog

  I don't see anything wrong with your code. I don't know how the ChangePassword control works, but what you are doing should be fine.

  Since you don't have Profiler, the following are a couple options I think you can try:

  (1) Use the built in tracing that comes with .NET 2.0. The following whitepaper contains information on how to do this:

  Data Access Tracing in SQL Server 2005
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/data_access_tracing.asp

  It looks more complicated than it really is -- once you have done it a few times it's very easy to start and stop the tracing. Reading the output is a little more difficult, but I can help you with that part if you can get a trace. You can send me the trace file at the e-mail address in my forum profile, but make sure you REMOVE the "online" from the address.

  (2) In your code, after you execute the update, but before you close the connection, add some code to query the new value you just added, and verify if it shows up as updated at that point. You can do that like this:

  int x = update_customer_details.ExecuteNonQuery(); //existing code, the following assumes this UPDATE is supposed to update the ROW with ID=4
  SqlCommand testUpdate = new SqlCommand("SELECT [CompanyName] FROM [app_CustomerDetails] WHERE [ID]=4", connect);
  string newValue = (string)testUpdate.ExecuteScalar();
  Console.WriteLine("New value: {0}", newValue); //you wouldn't use this in ASP.NET, but you just need some way to get the value from the query -- debugger or logging will work too
  connect.Close(); //existing code

  This is just checking for the current value of one field, because presumably neither one gets updated, so it doesn't matter which you check. If ExecuteNonQuery is returning 1, there is no reason why the update should not have gone through. This will verify that. Now assuming this does show the update, the key is to figure out why you aren't seeing it in the database itself. I think this will prove that at least this code is working correctly.

  In case your code is operating within a transaction as Zlatko suggested, you could also try querying the @@trancount in your code. For example:

  SqlCommand testTran= new SqlCommand("SELECT @@trancount", connect);
  int tranCount = (int)testTran.ExecuteScalar();

  If it's not zero, you are in a transaction. This is where Profiler and/or the built-in tracing would help as well.

  Thanks,
  Sarah

  Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.
  

  
  
  

• wynfred

  Hi, Thanks for the reply

  I have tried that and still no luck, here is a bit a code, I don't know why its not working because the ID is always valid I have tested it.

  private void UpdateCustomerDetails() { int ID = Convert.ToInt32(Request.QueryString["ID"]); SqlConnection connect = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True"); SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " + " SET [CompanyName]=@Name,[City]=@City " + " WHERE [ID]=@ID", connect); update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = txtCustomerName.Text; //txtCustomerName is a textbox update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = txtCity.Text;//txtCity is a textbox update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ID; connect.Open(); int x = update_customer_details.ExecuteNonQuery(); connect.Close(); StatusLabel.Text = x.ToString(); }

  
  
  

• daveW2007

  1. Please display the parameter values that you are setting to make sure you are not updating with the old values.
  2. If you are familiar with the SQL Profiler, please trace the call and verify the successful outcome, and the parameter values.

  Zlatko

  
  
  

• Timmy0614

  Just a thought.

  Are you sure you are looking at the correct database

  Maybe you are updating the table in one database and checking it in another.

  
  
  

• Aaron Oneal

  1. Is there any chance that you have an active System.Transaction.TransactionScope while executing this command If so, it would automatically enlist in that transaction.
  2. Put the following code:

  SqlCommand select = new SqlCommand( “SELECT CompanyName FROM app_CustomerDetails WHERE ID=4”, connect );

  String companyName = select.ExecuteScalar();

  // dump companyName somewhere

  after executing the update command and before closing the connection

  Zlatko

  
  
  

• HuskyNET

  It must be updating it...everything looks good to me.

  can you see what the values are of the parameters (txtCustomerName, txtCity)

  Are they valid values In other words, do they contain values Which database are you connecting to Of course I assume that you have looked at the database to see the values if they have been updated

  Have you tried it this way

  SqlParameter theName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);

  theName.Value = this.txtCustomerName.Text

   

  SqlParameter theCity = new SqlParameter("@City", SqlDbType.NVarChar, 50);

  theCity.Value = this.txtCity.Text

   

  SqlParameter theID= new SqlParameter("@ID", SqlDbType.Int, 4);

  theID.Value = ID

   

  update_customer_details.Parameters.Add(theName);

  update_customer_details.Parameters.Add(theCity);

  update_customer_details.Parameters.Add(theID);

  ..

  //execute non query

   

  Also where are you obtaining "ID" from

  
  
  

• Keith Hill

  Hi, thanks for the reply.

  I'm using the same connection string when I inserting/deleting/updating data in Sql database. so I really don't think there is a mismatch because this code runs fine for deleting and the problem is updating.

  private void deleteUserdetails() { int ID = Convert.ToInt32(Request.QueryString["ID"]); SqlConnection connect = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True"); SqlCommand cmd_delete_customer_details = new SqlCommand("DELETE FROM [app_CustomerDetails] WHERE ID=@ID", connect); cmd_delete_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4); cmd_delete_customer_details.Parameters["@ID"].Value = ID; connect.Open(); int x = cmd_delete_customer_details.ExecuteNonQuery(); connect.Close(); StatusLabel.Text = x.ToString(); }

  any ideas

  
  
  
• Some how the update command is not working.