UPDATE statement No Effect on Accdb DB. RSS

• • Reply
  

  kingster113

  Member

  14 Points

  3 Posts

  UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 06:25 AM|LINK

  I'd been researching on how to solve this issue, but no solid solution was ever provided. I have an UPDATE statement that is suppose to run on my aspx page. The INSERT INTO statements on other pages work flawlessly. But when it comes to the UPDATE statement, nothing gets updated. The page simply reloads with no changes to the database, nor did it return any errors. Below is my code:

  I desperately need help on this.

  string connectionString = "provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + Page.Server.MapPath("App_Data\\HEC.accdb");
  
          System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString);
  
          System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();
  
          conn.Open();
  
          cmd = new System.Data.OleDb.OleDbCommand("UPDATE [member] SET [userID]=usr,[pass]=pss,[mobilePhone]=mp,[email]=em WHERE [userID]=lbl" , conn);
  
          cmd.Parameters.AddWithValue("lbl", Session["Username"].ToString());
          cmd.Parameters.AddWithValue("usr", txtusername.Text);
          cmd.Parameters.AddWithValue("pss", txtnewpass.Text);
          cmd.Parameters.AddWithValue("mp", txtmobile.Text);
          cmd.Parameters.AddWithValue("em", txtemail.Text);
  
          cmd.ExecuteNonQuery();
  
          conn.Close();

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 06:38 AM|LINK

  OleDb parameters are recognized by their position, not by their name. That means that you should add them in the same order they (first) appear in your command..

  cmd.Parameters.AddWithValue("usr", txtusername.Text);
          cmd.Parameters.AddWithValue("pss", txtnewpass.Text);
          cmd.Parameters.AddWithValue("mp", txtmobile.Text);
          cmd.Parameters.AddWithValue("em", txtemail.Text);
          cmd.Parameters.AddWithValue("lbl", Session["Username"].ToString());

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 06:43 AM|LINK

  By the way

  For a better syntax, refer to this article:

  http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

  • Edited by hans_v on Sep 22, 2011 06:43 AM
• • Reply
  

  kingster113

  Member

  14 Points

  3 Posts

  Re: UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 07:06 AM|LINK

  Thank you for your help hans. :) After fixing what you advised, it was working. But only the password column got updated. The mobilePhone and email were still in its default values. I would need to highlight that the txtmobile and txtemail had session values loaded into them on page_load event. I thought that by typing new values into the respective textboxes would result in changes, but that was not the case.

  //txtmobile.Text = Session["Mobile"].ToString();
  //txtemail.Text = Session["Email"].ToString();

  By commenting the two lines above, new values that I updated into the database were present.

  Hans, could you explain why is this happening? Why won't the new data get inserted if I had session values loaded into them?

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 08:45 AM|LINK

  Try

  cmd.Parameters.AddWithValue("usr", "NewuserName");
          cmd.Parameters.AddWithValue("pss", "NewPassword");
          cmd.Parameters.AddWithValue("mp", "1234567890");
          cmd.Parameters.AddWithValue("em", "test@test.com");
          cmd.Parameters.AddWithValue("lbl", Session["Username"].ToString());

  To see if the update is working. If it is working (I wouldn't know why not?), then you need to take a closer look which values you're passing to the parameters....

  • Marked as answer by decker dong - msft on Sep 24, 2011 12:00 AM
• • Reply
  

  kingster113

  Member

  14 Points

  3 Posts

  Re: UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 12:14 PM|LINK

  Tried that. It works.

  The thing is that if beforehand I'd retrieve information from DB and fill it to say, txtmobile (through the use of Session, as demonstrated above). And I then proceed to change the value in txtmobile and hit the update button, nothing will get updated.

  Now what I did now was, instead of updating the txtmobile, I created another textbox (txtnew_mobile) beside txtmobile. And I place whatever value I want to update mobilePhone field in my ACCDB, that field gets updated.

  • Marked as answer by decker dong - msft on Sep 24, 2011 12:00 AM
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: UPDATE statement No Effect on Accdb DB.

  Sep 22, 2011 02:31 PM|LINK

  kingster113

  Tried that. It works.

  Good, then it must be something else in your code.

  Without showing us the rest of the code it's hard to tell where the problem is?