Web Development 101: Part 7, Creating an Edit Data Page

Up to now you've created your movies page, styled it, made it data driven, and then created a form that you could use to add movies to your database. The next step will be to create a very similar form that you can use to edit your existing list of movies.

Let's take a look at the application as it now stands:

As you can see it has a list of movies, with the ability to add a new movie via a link at the bottom. In order to have a mouseover effect, we made each movie entry in the list a hyperlink, using the <a> tag. It would make sense that if we want to edit a movie, that we should just use this hyperlink, so let's follow that process here.

Create the Edit Page

First, let's create a new CSHTML page in WebMatrix, and call it EditMovie.cshtml. This page will eventually have a form that is populated with the details for the movie you selected, and when you change those details, the changes will be submitted back to the database.

So replace the default content in EditMovie.cshtml with such a form. It's going to look very similar to the one you created in the last Chapter.

<h1>Edit a Movie</h1>
<form action="" method="post">
    <p>Name:<input type="text" name="formName" /></p>
    <p>Genre:<input type="text" name="formGenre" /></p>
    <p>Year:<input type="text" name="formYear" /></p>
    <p><input type="submit" value="Edit Movie" /></p>
</form>

Call the Edit Page from the Movies List

Ok, so we now have the basic foundation for the edit form. But how do we intialize the form with the contents of the database for the particular movie you selected? Well, first, let's figure out how to tell this page which movie you want to edit, and to do that we have to go back to our dataMovies.cshtml page.

As you might recall, we had the list items written out like this:

<li><a href="#">@row.Name, @row.Genre, @row.ReleaseYear</a></li>

The Hyperlink went nowhere because the href is just "#". So let's make the hyperlink go to our EditMovie.cshtml page, like this:

<li><a href="EditMovie.cshtml ">@row.Name, @row.Genre, @row.ReleaseYear</a></li>

This is nice, but, whatever movie you select, it will always call EditMovie.cshtml, and this page will have no idea what movie you are editing. But, the dataMovies.cshtml page already knows which movie -- because you've selected it, so you can use this to pass the ID of the movie you've selected to EditMovie.cshtml like this:

EditMovie.cshtml?id=<something>

And because we already know what the id of the current row is (@row.id), we can use Razor to write out the ID as we write out the list, so we change our <li> to look like this:

<li><a href="EditMovie.cshtml?id=@row.id">@row.Name, @row.Genre, @row.ReleaseYear</a></li>

Now when we look at dataMovies.cshtml, we'll get the following:

It doesn't really look any different, but look what happens when we look at the HTML code for this page. This isn't the .cshtml page you see in WebMatrix, but instead the HTML that gets generated by the server (from the instructions in the cshtml), and sent to the browser.

In Internet Explorer 9 you can view this by right clicking anywhere on the page and selecting 'View Source'

   1:  <!DOCTYPE html>
   2:  <html lang="en">
   3:    <head>
   4:      <meta charset="utf-8" />
   5:      <title>My Favorite Movies</title>
   6:      <link rel="stylesheet" type="text/css" href="movies-html5.css" />
   7:    </head>
   8:   
   9:  <body>
  10:    <header>
  11:      <h1>A list of my Favorite Movies</h1>
  12:    </header>
  13:   
  14:    <div id="movieslist">
  15:    <ol>
  16:      <li><a href="EditMovie.cshtml?id=1">It&#39;s a wonderful life, Comedy, 1946</a></li>
  17:      <li><a href="EditMovie.cshtml?id=2">Lord of the Rings, Drama, 2001</a></li>
  18:      <li><a href="EditMovie.cshtml?id=3">The Fourth World, Anime, 2012</a></li>
  19:      <li><a href="EditMovie.cshtml?id=4">The Lion King, Family, 1994</a></li>
  20:      <li><a href="EditMovie.cshtml?id=5">Forrest Gump, Comedy, 1994</a></li>
  21:      <li><a href="EditMovie.cshtml?id=6">The Million Year Journey, Anime, 2014</a></li>
  22:    </ol>
  23:   
  24:    <a href="AddMovie.cshtml">Add a new movie</a>
  25:  </div>
  26:   
  27:    <footer>
  28:   
  29:      This site was built using Microsoft WebMatrix. 
  30:   
  31:      <a href="http://web.ms/webmatrix">Download it now.</a>
  32:   
  33:    </footer>
  34:   
  35:  </html>
  36:   

See how, when the look was created, the value of the ID for this particular row was written out to the HTML? Now, when EditMovie.cshtml loads, we can take this ID and use it to find the particular record we're interested in.

Finishing the Edit Page

So let's go back to EditMovie.cshtml.

Remember earlier we saw that if you put a @{ at the top of the page, and wrote code within that, then the code would execute when the page loaded? Well that's the perfect place to put code to read the ID that was in the URL of the page, and then use that to find the name, genre and release year of this movie.

When you call a page with the syntax that we're using (i.e. PageName.cshtml?<Parameter>=<Value>), you can find out the value of the parameter using the Request object. So, for example for EditMovie.cshtml?id=6, we can use the following code:

var id=Request["id"];

This code says to create a local variable, called 'id' and use the value of the parameter (also called 'id') to initialize it. WebMatrix is smart enough to realize that both id's are different based on the context of how they're being used.

Now that we have the 'id', we can use it with a 'SELECT' query in SQL to find the record for that movie.

   1:  var id=Request["id"];
   2:  var SQLSELECT = "SELECT * FROM Favorites where ID=@0";
   3:  var db = Database.Open("Movies");
   4:  var Movie = db.QuerySingle(SQLSELECT,id);
   5:  var MovieName=Movie.Name;
   6:  var MovieGenre=Movie.Genre;
   7:  var MovieYear=Movie.ReleaseYear;

Pretty straightforward, right? We say 'Select * from Favorites where the ID field is equal to the ID that we passed in' and then run that against the database. As we only want 1 record, we say db.QuerySingle to get a single record.

Then the query is executed, and the Name, Genre and ReleaseYear values are loaded into local variables.

This is all very well, but, the problem is that the values are in variables, and not in the form, so how does the user edit them? Well -- the answer is simple -- remember that this code executes before the page renders, so we have the variables before we write out the HTML. And because of that, we can initialize the form with these values. The form uses <input> fields to give us the text boxes, and these support a 'value' property. We can now use this with the variables directly in order to initialize them.

Here's the full code for the page so far.

   1:  @{
   2:    var id=Request["id"];
   3:    var SQLSELECT = "SELECT * FROM Favorites where ID=@0";
   4:    var db = Database.Open("Movies");
   5:    var Movie = db.QuerySingle(SQLSELECT,id);
   6:    var MovieName=Movie.Name;
   7:    var MovieGenre=Movie.Genre;
   8:    var MovieYear=Movie.ReleaseYear;
   9:  }
  10:   
  11:  <h1>Edit a Movie</h1>
  12:  <form action="" method="post">
  13:    <p>Name:<input type="text" name="formName" value="@MovieName" /></p>
  14:    <p>Genre:<input type="text" name="formGenre" value="@MovieGenre" /></p>
  15:    <p>Year:<input type="text" name="formYear" value="@MovieYear" /></p>
  16:    <p><input type="submit" value="Edit Movie" /></p>
  17:  </form>
  18:   

Now when you run the page, you'll see that it is initialized with the movie values. Try it by running dataMovies.cshtml first, like this:

Then, select a movie. You can see from the screenshot above that I'm selecting 'The Fourth World' and it's calling EditMovie.cshtml with an id of 3.

The page then loads and looks like this:

You can type into the boxes to change any of the values, but when you press 'Edit Movie', nothing happens. You might recall, from the 'Add Movie' page in the last chapter, that the button triggers a form submit, which is an HTTP POST (as opposed to the HTTP GET which occurred when the page was called from the hyperlink), which needs to be processed.

The way you did this earlier was using an if(IsPost) code block, so let's do the same here.

   1:  if(IsPost){
   2:    MovieName=Request["formName"];
   3:    MovieGenre=Request["formGenre"];
   4:    MovieYear=Request["formYear"];
   5:  }
   6:   

The form uses the values formName, formGenre and formYear (I prefix with an 'f' to remember that these come from the form) for the Name, Genre and Release Year values. These are passed up in the Request object. If the user has edited these in any way, the new values will be in here, so we can update the database with them.

To update a database you use the SQL 'UPDATE' command that has this syntax:

UPDATE table SET column=value, column=value, column=value... WHERE key=value

So, in our cases, we have 3 columns that we're updating for the ID that we already know, so here's the code to create the query and execute it:

   1:  var SQLUPDATE = "UPDATE Favorites Set Name=@0, Genre=@1, ReleaseYear=@2 WHERE id=@3";
   2:  db.Execute(SQLUPDATE, MovieName, MovieGenre, MovieYear,id);

This uses paramters in SQL (@0, @1 etc., not to be confused with the '@' that marks the start of a Razor block) to make the SQL easier to read. Just remember that the @ values are replaced, in order, with the values within the db.Execute() code, so, in this case MovieName is @0, MovieGenre is @1 etc.

Finally, once the movie is edited, it's a good idea to redirect back to the list page so that we can see the results of our editing, updated dynamically. You did the same in the add movies page. Here's the code:

Response.Redirect("dataMovies.cshtml");

And for your convenience here's the full code for the EditMovies.cshtml page:

   1:  @{
   2:      var id=Request["id"];
   3:      var SQLSELECT = "SELECT * FROM Favorites where ID=@0";
   4:      var db = Database.Open("Movies");
   5:      var Movie = db.QuerySingle(SQLSELECT,id);
   6:      var MovieName=Movie.Name;
   7:      var MovieGenre=Movie.Genre;
   8:      var MovieYear=Movie.ReleaseYear;
   9:   
  10:      if(IsPost){
  11:        MovieName=Request["formName"];
  12:        MovieGenre=Request["formGenre"];
  13:        MovieYear=Request["formYear"];
  14:        var SQLUPDATE = "UPDATE Favorites Set Name=@0, Genre=@1, ReleaseYear=@2 WHERE id=@3";
  15:        db.Execute(SQLUPDATE, MovieName, MovieGenre, MovieYear,id);
  16:        Response.Redirect("dataMovies.cshtml");
  17:      }
  18:  }
  19:   
  20:  <h1>Edit a Movie</h1>
  21:    <form action="" method="post">
  22:      <p>Name:<input type="text" name="formName" value="@MovieName" /></p>
  23:      <p>Genre:<input type="text" name="formGenre" value="@MovieGenre" /></p>
  24:      <p>Year:<input type="text" name="formYear" value="@MovieYear" /></p>
  25:      <p><input type="submit" value="Edit Movie" /></p>
  26:    </form>

So now, let's see what happens when we run it. Assuming you've started with the dataMovies.cshtml page, and selected to edit one of the movies, you should see this:

If you now edit the 'Year' value to change from 2012 to 2011, and press the Edit Movie button, the database will be updated, and you'll be redirected to the list screen, where you can now see the release year is 2011.

clip_image010

And you've now successfully edited a record in your database!

Summary

When creating data driven web sites, it's important not to just give your users the ability to View and Add records, but also to Edit them. In this chapter you saw how easy it is to do this using WebMatrix, by creating an Edit page, and using a parameter to initialize this page with the desired record. You saw how to use Razor code to initialize the form with the right values from the database, and then to accept the HTTP POST with any updated values and write them back to the database. Once this was done you saw how to redirect from the form back to the list, and see your updates instantly. In the next step, you'll do the next logical thing -- and give your users the ability to delete records from the database too!

Continue to Part 8 where you’ll learn how to create a delete page.

You can discuss this article using the adjacent Facebook talkback.

For technical questions please visit our discussion forums, where we have a vibrant community of developers like you, as well as Microsoft engineers who are ready to answer your questions!