Database is not being Updated

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

Answer 1

User187056398 posted

http://weblogs.asp.net/stevewellens/archive/2009/10/16/why-sql-updates-fail-three-reasons.aspx 

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

User-1199946673 posted

Still does not wan to work

 

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

Answer 7

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())

Answer 8

User1118300756 posted

Thanks very much guys. That IsPostBack have fixed it.