CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

 Schoology Facebook        Search CIS Site      Tutorials

A Web App that does Insert, Update, and Delete on a Table



The Goals for this Example


The main goal is to show how, from within a web app, to insert a new record in a database table, update a record, and delete a record. In the process of illustrating this we will change the previous web app, specifically the third one that requires each user to log in with a username and password, so that a user can now create an account, change the password for the account, and even delete the account.

Setting up the Forms


It would be best to have a clean start to this one, so make a new website in Visual Studio. Have it located in a new folder on your W drive, the one for the live web server. You can name the website folder anything reasonable that you like.

Login.aspx

  • Consult this picture of the Login.aspx form to see how it should look.
  • In addition to the items that were on this form in the previous app, the button with ID LoginButton has been joined with a button with ID CreateAccountButton.
  • Make sure that ErrorLabel is not visible.
  • EnableSessionState="True" has to be added to the directive on the first line of this form, when in source view. We need this because we will pass the username used here at login to other forms in this website, when they need to know who the logged in user is that needs to have an account deleted or the password changed. You do not want to ask the user, as a malicious user could give someone else's username (often easily guessable) and then delete that person's account or change that person's password.
  • Speaking of source, here is a listing of the complete source code for this form:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Login.aspx.vb" Inherits="Login" EnableSessionState="True" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Login Page</title>
</head>
<body style="height: 284px; width: 315px">
    <form id="form1" runat="server">
    <div style="height: 272px; width: 305px;">
        <asp:Label ID="Label1" runat="server" Text="Username:"
            style="z-index: 1; left: 29px; top: 53px; position: absolute"></asp:Label>
        <asp:Label ID="Label2" runat="server" Text="Password:"
            style="z-index: 1; left: 31px; top: 119px; position: absolute"></asp:Label>
        <asp:Button ID="LoginButton" runat="server" Text="Login"
            style="z-index: 1; left: 36px; top: 191px; position: absolute; width: 49px; " />
        <asp:TextBox ID="UsernameTextBox" runat="server"
            style="z-index: 1; left: 124px; position: absolute; top: 48px"></asp:TextBox>
        <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password"
            style="z-index: 1; left: 127px; top: 118px; position: absolute"></asp:TextBox>
        <asp:Label ID="ErrorLabel" runat="server" Text="Invalid credentials"
            style="z-index: 1; left: 85px; top: 242px; position: absolute" Visible="False"></asp:Label>
        <asp:Button ID="CreateAccountButton" runat="server"
            style="z-index: 1; left: 130px; top: 192px; position: absolute; width: 157px;"
            Text="Create an account" />
    </div>
    </form>
</body>
</html>

Default.aspx

  • Consult this picture of the Default.aspx form to see how it should look.
  • New items that were not on this page in the previous app include two hyperlink controls. One has the text "Password change" and the other says "Delete my account".
  • The box below contains a listing of the complete source code for this form in case you want to create it quickly.
  • Note that the grid view has AutoGenerateColumns="False" and that the columns are specified within the grid view. As in our previous app, we do not want both. In the old app we turned AutoGenerateColumns off using the smart tag, but here is how to do the same thing in the code.
  • The two hyperlinks can have their NavigateUrl properties set to the desired page we want to reach when the links are clicked, or you can do the same thing with NavigateUrl="~/DeleteAccount.aspx" and the like in the code below. Note that the ~ symbol is here used to mean the current directory, the one that contains the page we are working on, Default.aspx.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Protected Application</title>
</head>
<body style="height: 421px; width: 748px; margin-right: 14px;">
    <form id="form1" runat="server">
    <div style="height: 410px">   
        <asp:Label ID="Label1" runat="server" style="z-index: 1; left: 41px; top: 37px; position: absolute"
            Text="Protected Application" Font-Size="X-Large"></asp:Label>
        <asp:Label ID="Label2" runat="server" style="z-index: 1; left: 43px; top: 86px; position: absolute"
            Text="This application allows you to view all of our parts data"></asp:Label>
        <asp:Button ID="ViewDataButton" runat="server"
            style="z-index: 1; left: 45px; top: 124px; position: absolute; height: 25px; right: 901px; width: 99px;"
            Text="View Data" />
        <asp:Label ID="ErrorLabel" runat="server"
            style="z-index: 1; left: 475px; top: 50px; position: absolute; height: 18px; width: 231px;"
            Text="Error in retrieving data" Visible="False"></asp:Label>
        <asp:Button ID="LogoutButton" runat="server"
            style="z-index: 1; left: 248px; top: 122px; position: absolute; height: 25px; right: 733px; width: 64px;"
            Text="Logout" />
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" EmptyDataText="No data found" ForeColor="#333333"
            GridLines="None" style="z-index: 1; left: 41px; top: 185px; position: absolute; height: 223px; width: 701px"
            AutoGenerateColumns="False">
            <AlternatingRowStyle BackColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#EFF3FB" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#F5F7FB" />
            <SortedAscendingHeaderStyle BackColor="#6D95E1" />
            <SortedDescendingCellStyle BackColor="#E9EBEF" />
            <SortedDescendingHeaderStyle BackColor="#4870BE" />
            <Columns>
                <asp:BoundField DataField="PartNumber" HeaderText="PartNumber" ReadOnly="True"
                    SortExpression="PartNumber" />
                <asp:BoundField DataField="Description" HeaderText="Description" ReadOnly="True"
                    SortExpression="Description" />
                <asp:BoundField DataField="Color" HeaderText="Color" ReadOnly="True"
                    SortExpression="Color" />
                <asp:BoundField DataField="Price" HeaderText="Price" ReadOnly="True"
                    SortExpression="Price" DataFormatString="{0:C2}" />
                <asp:BoundField DataField="Category" HeaderText="Category" ReadOnly="True"
                    SortExpression="Category" />
            </Columns>
        </asp:GridView>
        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/PasswordChange.aspx"
            style="z-index: 1; left: 474px; top: 93px; position: absolute">Password change</asp:HyperLink>
        <asp:HyperLink ID="HyperLink2" runat="server" style="z-index: 1; left: 473px; top: 131px; position: absolute"
            NavigateUrl="~/DeleteAccount.aspx">Delete my account</asp:HyperLink>
    </div>
    </form>
</body>
</html>

AddUser.aspx


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="AddUser.aspx.vb" Inherits="AddUser" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Add User</title>
</head>
<body style="height: 219px; width: 336px">
    <form id="form1" runat="server">
    <div style="height: 211px">
        <asp:Label ID="Label1" runat="server" Font-Size="Larger"
            style="z-index: 1; left: 89px; top: 34px; position: absolute" Text="Create an account"></asp:Label>
        <asp:Label ID="ErrorLabel" runat="server"
            style="z-index: 1; left: 205px; top: 176px; position: absolute; height: 19px"
            Text="Add failed" Visible="False"></asp:Label>
        <asp:Label ID="Label3" runat="server"
            style="z-index: 1; left: 38px; top: 87px; position: absolute" Text="Desired username:"></asp:Label>
        <asp:TextBox ID="UsernameTextBox" runat="server"
            style="z-index: 1; left: 185px; top: 90px; position: absolute"></asp:TextBox>
        <asp:TextBox ID="PasswordTextBox" runat="server"
            style="z-index: 1; left: 183px; top: 131px; position: absolute" TextMode="Password"></asp:TextBox>
        <asp:Button ID="AddUserButton" runat="server"
            style="z-index: 1; left: 80px; top: 176px; position: absolute" Text="Add User" />
        <asp:Label ID="Label4" runat="server"
            style="z-index: 1; left: 81px; top: 130px; position: absolute" Text="Password:"></asp:Label>
    </div>
    </form>
</body>
</html>

PasswordChange.aspx

  • Consult this picture of the PasswordChange.aspx form to see how it should look.
  • EnableSessionState="ReadOnly" is added to the directive on the first line of this form, when in source view. We need this because PasswordChange.aspx.vb needs to know the username used at login. That keeps PasswordChange from changing someone else's password. The changed password will be for the account under which the current user logged in.
  • Optional: You might want to strengthen the security of this page by requiring the user to type the correct current password. You might also want to have the person enter the new password twice and then check that these two agree.
  • The listing of the complete source code for this form is in the box below.
  • Note that the PasswordTextBox has its TextMode property set to Password. This keeps the characters typed into this textbox from appearing on the screen (where an onlooker might read them).

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="PasswordChange.aspx.vb"
    Inherits="PasswordChange" EnableSessionState="ReadOnly" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Change Password</title>
</head>
<body style="height: 192px; width: 324px">
    <form id="form1" runat="server">
    <div style="height: 183px; width: 311px">
        <asp:Label ID="Label1" runat="server" Font-Size="X-Large"
            style="z-index: 1; left: 61px; top: 30px; position: absolute"
            Text="Change My Password"></asp:Label>
        <asp:Label ID="Label2" runat="server" style="z-index: 1; left: 27px; top: 85px; position: absolute"
            Text="New password:"></asp:Label>
        <asp:TextBox ID="PasswordTextBox" runat="server" style="z-index: 1; left: 163px; top: 85px; position: absolute"
            TextMode="Password"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" style="z-index: 1; left: 106px; top: 129px; position: absolute"
            Text="Make it so!" />
        <asp:Label ID="ErrorLabel" runat="server" style="z-index: 1; left: 100px; top: 172px; position: absolute"
            Text="An error occured" Visible="False"></asp:Label>
    </div>
    </form>
</body>
</html>

DeleteAccount.aspx

  • Consult this picture of the DeleteAccount.aspx form to see how it should look.
  • Below is a listing of the complete source code for this form.
  • EnableSessionState="ReadOnly" is added to the directive on the first line of this form, just like the last form. This is because we want to be sure to delete the correct account, the one that the user logged in under, rather than trust the user to type in a username on the DeleteAccount.aspx form. A malicious person might try to delete the account of someone else.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="DeleteAccount.aspx.vb" Inherits="DeleteAccount"
    EnableSessionState="ReadOnly" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Delete My Account</title>
</head>
<body style="height: 192px; width: 309px">
    <form id="form1" runat="server">
    <div style="height: 183px; width: 302px">
        <asp:Label ID="Label1" runat="server" Font-Size="X-Large"
            style="z-index: 1; left: 61px; top: 40px; position: absolute"
            Text="Delete My Account"></asp:Label>
        <asp:Button ID="Button1" runat="server" style="z-index: 1; left: 119px; top: 107px; position: absolute"
            Text="Confirm" />
        <asp:Label ID="ErrorLabel" runat="server" style="z-index: 1; left: 94px; top: 161px; position: absolute"
            Text="An error occured" Visible="False"></asp:Label>
    </div>
    </form>
</body>
</html>

The Web.config File

  • Your Web.config file should be modified to fit the outline in the box below.
  • Note well that this is just an outline. Do not copy it over the top of your Web.config file. Rather, copy in certain sections that are complete, if you don't already have them in your Web.config file.
  • One important item is the sessionState tag. Be sure to copy that to the same location of your Web.config file. We need to use the InProc form of session state management as already noted above.
  • You also need the authentication tag indicating Windows forms authentication and the loginUrl of Login.aspx. Copy all of this into the same place in your Web.config file.
  • Debugging is turned on in this outline, but you will eventually want to turn it off once your app is working.
  • As always, customErrors mode="Off" turns off custom errors so that you can see the detailed error messages. When your app is finished, set this to On so that those detailed error messages are hidden from users.
  • You will need 3 locations sections as shown in the outline. Each denies anonymous users access to a particular page. Obviously a user who has not logged in should not be allowed to try to change a password, delete an account, or view the data (found on the Default.aspx page). These 3 sections should be copied into your Web.config file, in the same location as here.
  • You need to copy in your own webapp connection string, probably from one of your earlier example apps, and place it in the connectionStrings section.

<configuration>
   <configSections>
      <!-- Keep whatever Visual Studio puts into this section. -->
   </configSections>
   <system.web>
      <sessionState mode="InProc"/>
      <authentication mode="Forms">
         <forms name="Login" loginUrl="Login.aspx" path="/"/>
      </authentication>
      <compilation debug="true" strict="false" explicit="true" targetFramework="4.5">
         <assemblies>
            <!-- Use whatever Visual Studio puts into this section. -->
         </assemblies>
         <buildProviders>
            <!-- Visual Studio will add this section when you create your models. -->
         </buildProviders>
      </compilation>
      <httpRuntime targetFramework="4.5"/>
      <customErrors mode="Off"/>
   </system.web>
   <location path="Default.aspx">
      <system.web>
         <authorization>
            <deny users="?"/>
         </authorization>
      </system.web>
   </location>
   <location path="PasswordChange.aspx">
      <system.web>
         <authorization>
            <deny users="?"/>
         </authorization>
      </system.web>
   </location>
   <location path="DeleteAccount.aspx">
      <system.web>
         <authorization>
            <deny users="?"/>
         </authorization>
      </system.web>
   </location>
   <connectionStrings>
      <!-- You will need to copy in your webapp connection string.
          Visual Studio will add some entity-related items. -->
   </connectionStrings>
   <entityFramework>
      <!-- Visual Studio will add this section. -->
   </entityFramework>
</configuration>

Create the Database Models

  • As in the previous app, create 2 models, one for the User table and one for the Parts table which will be used by Default.aspx.vb.
  • As you have done before, generate a database model by right clicking your App_Code folder and making the usual choices.
  • For the first model, use the names UsersModel, usersEntities, and UsersNamespaceModel or similar. Be careful to remember the second name, usersEntities, as you will need to use it in your VB code.
  • Only select the Users table from among the tables in your database.
  • After that first model has been created, make a second one. This one should be for just the Parts table. Name the three items PartsModel, partsEntities, and PartsNamespaceModel or similar. Again, you will need to use the second of these in your VB code later.
  • Save everything, close Visual Studio, and then reopen it a bit later. This seems to be needed for Visual Studio to recognize the new items usersEntities and partsEntities.

The Code-Behind Files



The Code for Login.aspx.vb

  • Double click on each of the buttons on the Login.aspx form so as to create the outline of a click handler for each of them.
  • The click handler for the LoginButton should be almost the same as that for the same button in the previous app. However, it also needs to save the username into Session state for use later with some of the other forms.
  • The code for the CreateAccountButton is simple. We just redirect to the page for creating a new account.
  • See the code below for these two click handlers.

    Protected Sub LoginButton_Click(sender As Object, e As EventArgs) Handles LoginButton.Click
        ' Consider using a Login control instead of the textboxes and login button.
        Try
            Session("SubmittedUsername") = UsernameTextBox.Text
            Using loginEntities As New usersEntities()
                Dim userMatch = From User In loginEntities.Users
                   Select User.Password, User.Username Where (UsernameTextBox.Text = Username)
                If PasswordTextBox.Text = userMatch.FirstOrDefault.Password Then
                    FormsAuthentication.RedirectFromLoginPage(UsernameTextBox.Text, False)
                Else
                    ErrorLabel.Visible = True
                End If
            End Using
        Catch exception As Exception
            ErrorLabel.Visible = True
        End Try
    End Sub

    Protected Sub CreateAccountButton_Click(sender As Object, e As EventArgs) Handles CreateAccountButton.Click
        Response.Redirect("AddUser.aspx")
    End Sub
End Sub

The Code for Default.aspx.vb

  • Double click on each of the two buttons so as to get the outline for the click handlers.
  • The code should be the same as before, but it is shown again in the box below for completeness.

Protected Sub ViewDataButton_Click(sender As Object, e As EventArgs) Handles ViewDataButton.Click
    Try
        Using pEntities As New partsEntities()
            Dim data = From Part In pEntities.Parts Select Part
            GridView1.DataSource = data.ToList()
            GridView1.DataBind()
        End Using
    Catch exception As Exception
        ErrorLabel.Visible = True
    End Try
End Sub

Protected Sub LogoutButton_Click(sender As Object, e As EventArgs) Handles LogoutButton.Click
    FormsAuthentication.SignOut()
    FormsAuthentication.RedirectToLoginPage("Login.aspx")
End Sub

The Code for AddUser.aspx.vb

  • Double click on the Add User button to create the outline of its click hanlder.
  • The box below gives the code for this.
  • Note that the code creates a new User object containing the Username and Password that the user placed in the textboxes on the AddUser.aspx form.
  • Then this new object is added to the Users collection in loginEntities.
  • SaveChanges() is what causes the new data in the entities collection Users to be written to the corresponding table (namely Users) in the database.
  • Once the new account data has been written to the database, the code redirects the user to the Login.aspx page so that the person can use the new account to log in.

Protected Sub AddUserButton_Click(sender As Object, e As EventArgs) Handles AddUserButton.Click
    Try
        Using loginEntities As New usersEntities()
            Dim U As New User With {.Username = UsernameTextBox.Text, .Password = PasswordTextBox.Text}
            loginEntities.Users.Add(U)
            loginEntities.SaveChanges()
            Response.Redirect("Login.aspx")
        End Using
    Catch exception As Exception
        ErrorLabel.Visible = True
    End Try
End Sub

The Code for PasswordChange.aspx.vb

  • Double click on the button on the PasswordChange.aspx form to get the outline for its click handler.
  • The box below gives the code for this.
  • The code looks in the Users entity set in loginEntities for an object whose Username field matches the username, as saved in Session state, for the currently logged-in user.
  • The unnamed (anonymous) function passed in as a parameter to Single is a lambda expression (or lambda for short). For background on lambda expressions in VB see http://msdn.microsoft.com/en-us/library/bb531253(v=vs.110).aspx.
  • Single is used to pick out the first such matching object, should there be more than one. We expect that there will be exactly one, of course.
  • Next, the password typed into the PasswordTextBox on the PasswordChange form is copied to the password field of the object just found in the previous step.
  • SaveChanges() is then used to write this updated data to the database.
  • The code for this click handler ends by logging the user out and redirecting to the Login.aspx page, where the person can try logging in with the changed password.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Dim SubmittedUsername As String = Session("SubmittedUsername")
        Using loginEntities As New usersEntities()
            Dim userMatch = loginEntities.Users.Single(Function(U) U.Username = SubmittedUsername)
            userMatch.Password = PasswordTextBox.Text
            loginEntities.SaveChanges()
            ' Log the user out and have the person sign in with the new password:
            FormsAuthentication.SignOut()
            Response.Redirect("Login.aspx")
        End Using
    Catch exception As Exception
        ErrorLabel.Visible = True
    End Try
End Sub

The Code for DeleteAccount.aspx.vb

  • Double click on the button on the DeleteAccount.aspx form to get the outline for its click handler.
  • The box below gives the code for this.
  • As in the code to handle changing the password, this one begins by pulling the username from Session state, the username that the user provided when logging in to this app.
  • The code next looks in the Users entity set in loginEntities for an object whose Username field matches that username from Session state.
  • Single is used to pick out the first such matching object, should there be more than one. We expect that there will be exactly one, of course.
  • The Remove function is used to remove that matching object from the Users collection in loginEntities.
  • Then SaveChanges() is used to make the same change to the corresponding table (Users) in the database.
  • The code for this click handler ends by logging the user out and redirecting to the Login.aspx page, in case the person wants to create a new account in order to continue to use this application.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Dim SubmittedUsername As String = Session("SubmittedUsername")
        Using loginEntities As New usersEntities()
            Dim userMatch = loginEntities.Users.Single(Function(U) U.Username = SubmittedUsername)
            loginEntities.Users.Remove(userMatch)
            loginEntities.SaveChanges()
            ' Log the user out:
            FormsAuthentication.SignOut()
            Response.Redirect("Login.aspx")
        End Using
    Catch exception As Exception
        ErrorLabel.Visible = True
    End Try
End Sub

Final Items

  • Save your files, build your web site, and then try it out, both locally and then on our live web server. For the latter, you will as usual have to have your folder converted to an IIS web app by whoever runs your web server.
  • Use SQL Server Management Studio to look at the Users table in your database. Verify when you test your web app that the changes you do in the app actually do change the data in the Users table.
  • Also ask to have IIS require the use of SSL to access this web app. Since we are using plain text passwords (and usernames), it does not make sense to transmit these over the network unencrypted.
  • Requiring the use of SSL adds two things: the encryption of any data flowing between the user's browser and the web server as well as a digital certificate that identifies the server.
  • To access your web app you must now use https instead of http at the start of the URL.
  • If you turned on error reporting or debugging in your Web.config file, turn them off now. To return your Web.config file to normal (no debugging and no displaying of error messages in the browser), edit the file so that the customErrors and compilation sections look like what is shown in the box below. Note that either On or RemoteOnly for customErrors will protect users not on the same machine from the technical details of errors and protect your website from hackers who would like to see those detailed error messages!

      <compilation debug="false" strict="false" explicit="true" targetFramework="4.5"/>
      <customErrors mode="On"/>


Back to the main page for ASP.NET 4.5 Web Apps



Author: Br. David Carlson
Last updated: October 18, 2017
Disclaimer

Saint Vincent College - Computing and Information Systems Department
300 Fraser Purchase Road • Latrobe, PA 15650