locked
Database is not being Updated RRS feed

  • Question

  • User1118300756 posted

    Hi Again.

    I have a preferences page that has personal details of users which they are allowed to change. This data then gets put into the database but it has to be an update. My code does not show any errors it just does not update my database at all.


        Protected Sub btnUpdateDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateDetails.Click
            Dim DataConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("App_Data/database.mdb"))
            Dim strSQL2 As String
            strSQL2 = "UPDATE [User] SET FirstName='" & txtFirstName.Text & "', LastName='" & txtLastName.Text & "', Gender='" & ddlGender.SelectedValue & "', Nationality='" & ddlNationality.SelectedValue & "', Course='" & ddlCourse.SelectedValue & "', CourseYear='" & ddlYearOfStudy.SelectedValue & "', Residence='" & txtResidence.Text & "' WHERE UserID ='" & Session("UserID") & "'"
    
            Dim DataCommand As New OleDbCommand(strSQL2, DataConnection)
    
            DataConnection.Open()
            DataCommand.ExecuteNonQuery()
            DataConnection.Close()
    
            Response.Redirect("MyPage.aspx")
    
        End Sub

    Any help would be greatly appreciated. Thanks
    
    



    Tuesday, April 13, 2010 10:35 AM

Answers

  • User1907360339 posted

    Please try to put all your code in Page_Lode inside a If Not IsPostBack

    like this:


    Sub Page_Load
        If Not IsPostBack
            ' Validate initially to force the asterisks
            ' to appear before the first roundtrip.
            Validate()
        End If
    End Sub
    


    And try one more thing, change Response.Redirect("MyPage.aspx") to Response.Redirect("MyPage.aspx?test="+DateTime.Now.ToString())


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 13, 2010 7:28 PM

All replies

  • User-1199946673 posted

    You're using quotes aroung all values, but I think some fields are numeric  (courseyear, userID) so you do not need to use quotes. Instead of concatenating a SQL statement and thinking whether or not to use quotes, always use parameterized queries!

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

    Tuesday, April 13, 2010 11:33 AM
  • User1118300756 posted

    Thanks for the responses guys.

    Both UserID and CourseYear are actually strings so they need to have the quotes. Also, regarding that article it seems a really good way but I never understand how the query should be set up. Do I just create a normal query in access or do I need to put [] in the fields in the query?

    Thanks.

    Tuesday, April 13, 2010 11:50 AM
  • User-1199946673 posted

    regarding that article it seems a really good way but I never understand how the query should be set up. Do I just create a normal query in access or do I need to put [] in the fields in the query?
     


    Protected Sub btnUpdateDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateDetails.Click
      Using DataConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=|DataDirectory|database.mdb"))
        Dim strSQL2 As String = "UPDATE [User] SET FirstName=@FirstName, LastName=@LastName, Gender=@Gender, Nationality=@Nationality, Course=@Course, CourseYear=@YearOfStudy, Residence=@Residence WHERE UserID=@UserID"
        Using DataCommand As New OleDbCommand(strSQL2, DataConnection)
            DataCommand.Parameters.AddWithValue("FirstName", txtFirstName.Text)
            DataCommand.Parameters.AddWithValue("LastName", txtLastName.Text)
            DataCommand.Parameters.AddWithValue("Gender", ddlGender.SelectedValue)
            DataCommand.Parameters.AddWithValue("Nationality", ddlNationality.SelectedValue)
            DataCommand.Parameters.AddWithValue("Course", ddlCourse.SelectedValue)
            DataCommand.Parameters.AddWithValue("YearOfStudy", ddlYearOfStudy.SelectedValue)
            DataCommand.Parameters.AddWithValue("Residence", txtResidence.Text)
            DataCommand.Parameters.AddWithValue("UserID", Session("UserID") )
            DataConnection.Open()
            DataCommand.ExecuteNonQuery()
        End Using
      End Using
      Response.Redirect("MyPage.aspx")
    End Sub


    Tuesday, April 13, 2010 12:34 PM
  • User1118300756 posted

    Still does not wan to work. Because it is a page that shows the settings of the user I have it that when the page loads the values already in the database are shown on the page. Would this stop the database from updating?

    This is the code for the whole page:

    Imports System.Data.OleDb
    Imports System.Data
    Imports System.Data.Common
    Partial Class Preferences
        Inherits System.Web.UI.Page
    
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim strSQL As String
            Dim DataConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("App_Data/database.mdb"))
            strSQL = "SELECT * FROM [User] WHERE (UserID ='" & Session("UserID") & "')"
            Dim DataCommand As New OleDbCommand(strSQL, DataConnection)
    
            DataConnection.Open()
    
            Dim DBReader As OleDbDataReader = DataCommand.ExecuteReader()
    
            If DBReader.Read() Then
    
                If DBReader("UserID") Is DBNull.Value Then
                    txtUniversityID.Text = ""
                Else
                    txtUniversityID.Text = DBReader("UserID")
                End If
    
                If DBReader("FirstName") Is DBNull.Value Then
                    txtFirstName.Text = ""
                Else
                    txtFirstName.Text = DBReader("FirstName")
                End If
    
                If DBReader("LastName") Is DBNull.Value Then
                    txtLastName.Text = ""
                Else
                    txtLastName.Text = DBReader("LastName")
                End If
    
                If DBReader("Gender") Is DBNull.Value Then
                    ddlGender.SelectedIndex = "0"
                Else
                    ddlGender.SelectedValue = DBReader("Gender")
                End If
    
                If DBReader("Nationality") Is DBNull.Value Then
                    ddlNationality.SelectedIndex = "0"
                Else
                    ddlNationality.SelectedValue = DBReader("Nationality")
                End If
    
                If DBReader("Course") Is DBNull.Value Then
                    ddlCourse.SelectedIndex = "0"
                Else
                    ddlCourse.SelectedValue = DBReader("Course")
                End If
    
                If DBReader("CourseYear") Is DBNull.Value Then
                    ddlYearOfStudy.SelectedIndex = "0"
                Else
                    ddlYearOfStudy.SelectedValue = DBReader("CourseYear")
                End If
    
                If DBReader("Residence") Is DBNull.Value Then
                    txtResidence.Text = ""
                Else
                    txtResidence.Text = DBReader("Residence")
                End If
            End If
            DBReader.Close()
            DataConnection.Close()
    
        End Sub
    
    
    
        Protected Sub btnUpdateDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateDetails.Click
            Using DataConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("App_Data/database.mdb"))
                Dim strSQL2 As String = "UPDATE [User] SET FirstName=@FirstName, LastName=@LastName, Gender=@Gender, Nationality=@Nationality, Course=@Course, CourseYear=@YearOfStudy, Residence=@Residence WHERE UserID=@UserID"
                Using DataCommand As New OleDbCommand(strSQL2, DataConnection)
                    DataCommand.Parameters.AddWithValue("FirstName", txtFirstName.Text)
                    DataCommand.Parameters.AddWithValue("LastName", txtLastName.Text)
                    DataCommand.Parameters.AddWithValue("Gender", ddlGender.SelectedValue)
                    DataCommand.Parameters.AddWithValue("Nationality", ddlNationality.SelectedValue)
                    DataCommand.Parameters.AddWithValue("Course", ddlCourse.SelectedValue)
                    DataCommand.Parameters.AddWithValue("YearOfStudy", ddlYearOfStudy.SelectedValue)
                    DataCommand.Parameters.AddWithValue("Residence", txtResidence.Text)
                    DataCommand.Parameters.AddWithValue("UserID", Session("UserID"))
                    DataConnection.Open()
                    DataCommand.ExecuteNonQuery()
                    DataConnection.Close()
                End Using
            End Using
    
            Response.Redirect("MyPage.aspx")
        End Sub
    
    End Class
    



    Tuesday, April 13, 2010 12:55 PM
  • User-1199946673 posted

    Still does not wan to work
     

    Put your code in a Try Catch Block, and catch the error...

    Tuesday, April 13, 2010 7:18 PM
  • User1907360339 posted

    Please try to put all your code in Page_Lode inside a If Not IsPostBack

    like this:


    Sub Page_Load
        If Not IsPostBack
            ' Validate initially to force the asterisks
            ' to appear before the first roundtrip.
            Validate()
        End If
    End Sub
    


    And try one more thing, change Response.Redirect("MyPage.aspx") to Response.Redirect("MyPage.aspx?test="+DateTime.Now.ToString())


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 13, 2010 7:28 PM
  • User1118300756 posted

    Thanks very much guys. That IsPostBack have fixed it.

    Tuesday, April 13, 2010 8:15 PM