locked
UPDATE not working when in use with AccessDataSource vb asp.net RRS feed

  • Question

  • User-1969034944 posted

    I am having problems using an UPDATE command in my asp.net project. The project allows a user to log in via a login page which matches the username and password to a record in the database and saves the username as a cookie. I then have a settings page which I want to allow a user to edit their account details but when I use the UPDATE function the query is executed without any errors but the update doesn't occur. I am using an AccessDataSource and an UpdateCommand hopefully someone can find a problem with my code and steer my in the correct direction. Thanks in advance

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    
    
        Dim sqlcmd As String
        sqlcmd = "SELECT FirstName, SecondName, UserName, DOB, Password FROM UserTable WHERE UserName = '" & Request.Cookies("UserInfo")("userName") & "'"
        AccessDataSource1.SelectCommand = sqlcmd
    
        If (Not Page.IsPostBack) Then
    
            Dim sqlcmd2 As String
            sqlcmd2 = "UPDATE [UserTable] SET [FirstName] = ?, [SecondName] = ?, [UserName] = ?, [DOB] = ?, [Password] = ? WHERE [UserID] = ? AND ISNULL (([FirstName] = ?) OR ([FirstName] IS NULL AND ? IS NULL)) AND (([SecondName] = ?) OR ([SecondName] IS NULL AND ? IS NULL)) AND (([UserName] = ?) OR ([UserName] IS NULL AND ? IS NULL)) AND (([DOB] = ?) OR ([DOB] IS NULL AND ? IS NULL)) AND (([Password] = ?) OR ([Password] IS NULL AND ? IS NULL))"
            AccessDataSource1.UpdateCommand = sqlcmd2
    
        End If
    
    End Sub
    <form id="webform" runat="server">
        <div id="content">
         <asp:AccessDataSource ID="AccessDataSource1" DataSourceMode = "DataSet" runat="server" 
                ConflictDetection="CompareAllValues" DataFile="~/Database.mdb" 
                OldValuesParameterFormatString="original_{0}"  
                UpdateCommand = "UPDATE [UserTable] SET [FirstName] = ?, [SecondName] = ?, [UserName] = ?, [DOB] = ?, [Password] = ? WHERE [UserID] = ? AND ISNULL (([FirstName] = ?) OR ([FirstName] IS NULL AND ? IS NULL)) AND (([SecondName] = ?) OR ([SecondName] IS NULL AND ? IS NULL)) AND (([UserName] = ?) OR ([UserName] IS NULL AND ? IS NULL)) AND (([DOB] = ?) OR ([DOB] IS NULL AND ? IS NULL)) AND (([Password] = ?) OR ([Password] IS NULL AND ? IS NULL))">
    
             <UpdateParameters>
                 <asp:Parameter Name="UserID" Type="Int32" />
                 <asp:Parameter Name="FirstName" Type="String" />
                 <asp:Parameter Name="SecondName" Type="String" />
                 <asp:Parameter Name="UserName" Type="String" />
                 <asp:Parameter Name="DOB" Type="String" />
                 <asp:Parameter Name="Password" Type="String" />
                 <asp:Parameter Name="original_UserID" Type="Int32" />
                 <asp:Parameter Name="original_FirstName" Type="String" />
                 <asp:Parameter Name="original_FirstName" Type="String" />
                 <asp:Parameter Name="original_SecondName" Type="String" />
                 <asp:Parameter Name="original_SecondName" Type="String" />
                 <asp:Parameter Name="original_UserName" Type="String" />
                 <asp:Parameter Name="original_UserName" Type="String" />
                 <asp:Parameter Name="original_DOB" Type="String" />
                 <asp:Parameter Name="original_DOB" Type="String" />
                 <asp:Parameter Name="original_Password" Type="String" />
                 <asp:Parameter Name="original_Password" Type="String" />
             </UpdateParameters>
            </asp:AccessDataSource>
            <asp:GridView ID="GridView1" runat="server" DataSourceID="AccessDataSource1">
                <Columns>
                    <asp:CommandField ShowEditButton="True" />
                </Columns>
            </asp:GridView>
    <div>
    </div>
    </div>
    </form>




    Tuesday, March 26, 2013 6:36 PM

All replies

  • User-1716253493 posted
    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
             DataFile="~/Database.mdb"
             UpdateCommand = "UPDATE [UserTable] SET [FirstName] = ?, [SecondName] = ?, [UserName] = ?, [DOB] = ?, [Password] = ? WHERE [UserName] = ?"
             SelectCommand = "SELECT FirstName, SecondName, UserName, DOB, Password FROM UserTable WHERE UserName =?">
             <SelectParameters>
                 <asp:Parameter Name="UserName" />
             </SelectParameters>
             <UpdateParameters>
                 <asp:Parameter Name="FirstName" Type="String" />
                 <asp:Parameter Name="SecondName" Type="String" />
                 <asp:Parameter Name="UserName" Type="String" />
                 <asp:Parameter Name="DOB" Type="String" />
                 <asp:Parameter Name="Password" Type="String" />
                 <asp:Parameter Name="UserName" Type="String" />
             </UpdateParameters>
            </asp:AccessDataSource>
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            AccessDataSource1.SelectParameters("UserName").DefaultValue = Request.Cookies("UserInfo")("userName")
            AccessDataSource1.UpdateParameters("UserName").DefaultValue = Request.Cookies("UserInfo")("userName")
        End Sub

    Tuesday, March 26, 2013 9:01 PM
  • User-1969034944 posted

    its still doing the same thing, should i be using a gridview or would a formview help?

    Wednesday, March 27, 2013 10:23 AM