Databases
StackOverflow2013
Postsdbo
PODatabase not updating after UPDATE SQL statement in ASP.net
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase not updating after UPDATE SQL statement in ASP.net
    primarykey
    data
    text
    <p>I currently have a problem attepting to update a record within my database. I have a webpage that displays in text boxes a users details, these details are taken from the session upon login. The aim is to update the details when the user overwrites the current text in the text boxes.</p> <p>I have a function that runs when the user clicks the 'Save Details' button and it appears to work, as i have tested for number of rows affected and it outputs 1. However, when checking the database, the record has not been updated and I am unsure as to why.</p> <p>I've have checked the SQL statement that is being processed by displaying it as a label and it looks as so:</p> <pre><code>UPDATE [users] SET [email] = @email, [firstname] = @firstname, [lastname] = @lastname, [promo] = @promo WHERE [users].[user_id] = 16 </code></pre> <p>The function and other relevant code is:</p> <pre><code>Sub Page_Load(sender As Object, e As EventArgs) usernameLabel.text = session.contents.item("UserName") if usernameLabel.text = "" then logoutButton.Visible = False loggedInAsLabel.Visible = False else labelGuest.Visible = False linkLogin.Visible = False linkRegister.Visible = False end if emailBox.text = session.contents.item("Email") firstBox.text = session.contents.item("FirstName") lastBox.text = session.contents.item("LastName") promoBox.text = session.contents.item("Promo") End Sub Sub Button1_Click(sender As Object, e As EventArgs) changeDetails(emailBox.text, firstBox.text, lastBox.text, promoBox.text) End Sub Function changeDetails(ByVal email As String, ByVal firstname As String, ByVal lastname As String, ByVal promo As String) As Integer Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Documents an"&amp; _ "d Settings\Paul Jarratt\My Documents\ticketoffice\datab\ticketoffice.mdb" Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString) Dim queryString As String = "UPDATE [users] SET [email]=@email, [firstname]=@firstname, [lastname]=@lastname, "&amp; _ "[promo]=@promo WHERE ([users].[user_id] = " + session.contents.item("ID") + ")" Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_email As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_email.ParameterName = "@email" dbParam_email.Value = email dbParam_email.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_email) Dim dbParam_firstname As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_firstname.ParameterName = "@firstname" dbParam_firstname.Value = firstname dbParam_firstname.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_firstname) Dim dbParam_lastname As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_lastname.ParameterName = "@lastname" dbParam_lastname.Value = lastname dbParam_lastname.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_lastname) Dim dbParam_promo As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_promo.ParameterName = "@promo" dbParam_promo.Value = promo dbParam_promo.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_promo) Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try labelTest.text = rowsAffected.ToString() if rowsAffected = 1 then labelSuccess.text = "* Your details have been updated and saved" else labelError.text = "* Your details could not be updated" end if End Function </code></pre> <p>Any help would be greatly appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJuan Mellado
    1. USRonnie
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase not updating after UPDATE SQL statement in ASP.net
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave you checked to ensure that session.contents.item("ID") is what you expect it to be? If it is not equal to [users].[user_id] in any of the records, nothing will be updated.
      singulars
      1. PODatabase not updating after UPDATE SQL statement in ASP.net
      1. USDavid
    2. COYes I have, I output the full SQL query to a string to test it and the statement is correct. Also the rowsAffected variable is set to 1 after the function.
      singulars
      1. PODatabase not updating after UPDATE SQL statement in ASP.net
      1. USRonnie
    3. COI am wondering if it has anything to do with the fact that the textboxes already have the user details in them, when the page is loaded and then it requires the user to overwrite the textbox text? Could this be a problem area?
      singulars
      1. PODatabase not updating after UPDATE SQL statement in ASP.net
      1. USRonnie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload