Update The Database Using ASP.Net Web Pages 2

Introduction

Today we'll learn to update the data in the database using ASP.NET Web Pages 2. So far we've done Inserting & Validating Record and now we will edit the information that is saved in the database.

In that context we'll select a specific record and update the record from the database using WebMatrix. We'll also use the hidden field to store the information in here. So let's proceed with the following.

Working with Editing Record

In this section we'll provide a link to edit the record in the Cricketers page. Use the following procedure to do that.

Step 1: Open the Cricketers.cshtml page

Step 2: modify the GetHtml() method with the highlighted code below:

@CricGrid.GetHtml(
     tableStyle: "CricGrid",
     headerStyle: "head",
     alternatingRowStyle: "alt",
     columns: CricGrid.Columns(
           CricGrid.Column("Name"),
           CricGrid.Column("Team"),
           CricGrid.Column("Grade"),
           CricGrid.Column(format: @<a href="~/[email protected]">Update</a>)
      )
)

In the code above there is a new column added in the WebGrid in which a link is added named Update. When the button is clicked by the user the corresponding record ID is passed as you can see in the following URL:

http://localhost:31257/EditCricketer?id=9

Step 3: Run the page and you'll see the Update link in the grid as shown below:

Update Page

Creating Edit Page

We've done the process of linking the Edit page from the Cricketers Page. Now we've to create the Edit page using following procedure.

Step 1: Create a new UpdateCricketer page and replace the code with the code below:

<body>
   <h1>Edit Cricketer</h1>
   @Html.ValidationSummary()
   <form method="post">
        <fieldset>
             <legend>Cricketer Information</legend>
             <table>
                  <tr>
                      <td style="width: 100px"><label for="name">Name:</label></td>
                      <td><input type="text" name="name" value="@name" /></td>
                  </tr>
                  <tr>
                      <td><label for="team">Team:</label></td>
                      <td><input type="text" name="team" value="@team" /></td>
                  </tr>
                  <tr>
                      <td><label for="grade">Grade:</label></td>
                      <td><input type="text" name="grade" value="@grade" /></td>
                  </tr>
                  <tr>
                      <td colspan="2"><input type="submit" name="BtnSubmit" value="Save Changes" /></td>
                  </tr>
             </table>
             <input type="hidden" name="CricID" value="@Cricid" />
        </fieldset>        
   </form> 
</body>

Now run the page.

Update Cricketer

Step 2: Now we add the code in the code block to read the single movie.

@{
    var name ="";
    var team ="";
    var grade ="";
    var Cricid ="";
    if(!IsPost){
        if(!Request.QueryString["ID"].IsEmpty()){
            Cricid = Request.QueryString["ID"];
            var Cric_Db = Database.Open("Cricketer Site");
            var CommandText = "SELECT * FROM Cricketers WHERE ID = @0";
            var Cric_SelectedRow = Cric_Db.QuerySingle(CommandText, Cricid);
            name= Cric_SelectedRow.Name;
            team= Cric_SelectedRow.Team;
            grade= Cric_SelectedRow.Grade; 
        }
        else{
            Validation.AddFormError("Invalid Selection!!");
        }
    }
}

Now you can run your Cricketers page then click on the Update link, the Update page will open however nothing will happen when you click on "Save Changes" because the Update query is missing.

Step 3: Add the code to update the cricketer with the user's changes. Open the UpdateCricketers.cshtml page and add the following code after the "If" block:

if(IsPost){
    Validation.RequireField("name", "Cannot be bank!!");
    Validation.RequireField("team", "Cannot be bank!!");
    Validation.RequireField("grade", "Cannot be bank!!");  
    Validation.RequireField("CricID", "Cannot be bank!!"); 
    name = Request.Form["name"]; 
    team = Request.Form["team"]; 
    grade = Request.Form["grade"]; 
    Cricid = Request.Form["Cricid"]; 
    if(Validation.IsValid()){
        var Cric_Db = Database.Open("Cricketer Site");
        var CommandText = "UPDATE Cricketers SET Name=@0, Team=@1, Grade=@2 WHERE ID=@3";
        Cric_Db.Execute(CommandText, name, team, grade, Cricid);
        Response.Redirect("~/Cricketers");
    }
}

This code runs only when the user clicks on the "Save Changes" button; that is, when the form is posted.

Let's make the ID stronger. The first thing is to ensure that not only the ID passed to the page but also that ID is an integer. If Cricid is pass to the QuerySingle method then it is not certain that it is an actual id of a cricketer and no results are returned. We can check that the row variable is null before getting the value.

So modify your code as shown below:

if(!IsPost){
  if(!Request.QueryString["ID"].IsEmpty() && Request.QueryString["ID"].IsInt()){
     Cricid = Request.QueryString["ID"];
     var Cric_Db = Database.Open("Cricketer Site");
     var CommandText = "SELECT * FROM Cricketers WHERE ID = @0";
     var Cric_SelectedRow = Cric_Db.QuerySingle(CommandText, Cricid);
     if(Cric_SelectedRow != null){
         name= Cric_SelectedRow.Name;
         team= Cric_SelectedRow.Team;
         grade= Cric_SelectedRow.Grade;
     }
     else{
         Validation.AddFormError("Invalid Selection!!");
     }
  }
  else{
     Validation.AddFormError("Invalid Selection!!");
  }
}

Add a Link

At last add a link to get back to the Cricketers list as in the following::

Add link in Page

Complete Code

@{
    var name ="";
    var team ="";
    var grade ="";
    var Cricid ="";
    if(!IsPost){
        if(!Request.QueryString["ID"].IsEmpty() && Request.QueryString["ID"].IsInt()){
            Cricid = Request.QueryString["ID"];
            var Cric_Db = Database.Open("Cricketer Site");
            var CommandText = "SELECT * FROM Cricketers WHERE ID = @0";
            var Cric_SelectedRow = Cric_Db.QuerySingle(CommandText, Cricid);
            if(Cric_SelectedRow != null){
                name= Cric_SelectedRow.Name;
                team= Cric_SelectedRow.Team;
                grade= Cric_SelectedRow.Grade;
            }
            else{
                Validation.AddFormError("Invalid Selection!!");
            }
        }
        else{
            Validation.AddFormError("Invalid Selection!!");
        }
    }
    if(IsPost){
        Validation.RequireField("name", "Cannot be bank!!");
        Validation.RequireField("team", "Cannot be bank!!");
        Validation.RequireField("grade", "Cannot be bank!!");  
        Validation.RequireField("CricID", "Cannot be bank!!");
        name = Request.Form["name"]; 
        team = Request.Form["team"]; 
        grade = Request.Form["grade"]; 
        Cricid = Request.Form["Cricid"]; 
        if(Validation.IsValid()){
            var Cric_Db = Database.Open("Cricketer Site");
            var CommandText = "UPDATE Cricketers SET Name=@0, Team=@1, Grade=@2 WHERE ID=@3";
            Cric_Db.Execute(CommandText, name, team, grade, Cricid);
            Response.Redirect("~/Cricketers");
        }
    }
}
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Edit Cricketer</title>
    </head>
    <body>
       <h1>Edit Cricketer</h1>
        @Html.ValidationSummary()
        <form method="post">
            <fieldset>
                <legend>Cricketer Information</legend>
                <table>
                    <tr>
                        <td style="width: 100px"><label for="name">Name:</label></td>
                        <td><input type="text" name="name" value="@name" /></td>
                    </tr>
                    <tr>
                        <td><label for="team">Team:</label></td>
                        <td><input type="text" name="team" value="@team" /></td>
                    </tr>
                    <tr>
                        <td><label for="grade">Grade:</label></td>
                        <td><input type="text" name="grade" value="@grade" /></td>
                    </tr>
                    <tr>
                        <td colspan="2"><input type="submit" name="BtnSubmit" value="Save Changes" /></td>
                    </tr>
                </table>
                <input type="hidden" name="CricID" value="@Cricid" />
            </fieldset>
            <a href="~/Cricketers.cshtml">Back to List</a>
        </form>
    </body>
</html>

Summary

In this article, we created a form with which we can update the information in the database using ASP.NET Web Pages 2. Thanks for reading.

Update The Database Using ASP.Net Web Pages 2

Reactions

Comments