Adding And Editing Data In ASP.NET Web Pages

4.91 (11 votes)

In the first of my WebMatrix articles, I mentioned that entering data via the database design forms is a bit of a pain. I also promised to look at editing data via a web page at some stage. This article keeps that promise, and looks at adding data too.

Actually, I'll look at adding data first. To add a new book to the database, we need to provide a title, ISBN number, description and we need to add an author and category. The book will acquire a BookId value automatically as a result of making the BookId field in the database an Identity column. The first 3 values require textboxes within a form:

<form action="" method="post">
<div class="row">
    <span class="label"><label for="title">Title:</label></span>
    <input type="text" name="title" id="title" size="50" />
</div>
<div class="row">
    <span class="label"><label for="isbn">ISBN:</label></span>
    <input type="text" name="isbn" id="isbn" size="20" />
</div>
<div class="row">
    <span class="label"><label for="description">Description:</label></span>
    <textarea cols="50" rows="8" name="description" id="description"></textarea>
</div>

So far so good. The author and category are not so straightforward. A selection of authors and categories already exist in the database in their own separate tables. The only part of tthe author or category record that's stored in the book record is the Primary Key value for the author or category, as a Foreign Key value. This relates the appropriate author or category record the the book record. So, when adding an author to a book, for example, all we need is the ID of the author. However, presenting a list of ID values to the user is not much use. How are they going to know which Id belongs to which author? The answer is to use an html <select> element, also known in ASP.NET circles as a dropdown list. A select element contains one or more <option> elements. Each option has a value attribute (which is used to hold the ID), and it includes some text which defines the options available to the user in a more friendly way. In the case of authors, a combination of first name and last name will probably be the most helpful way to do this. So, in the code area at the top of the page, we need to define a Database object, and then query it for the data for the select list:

var db = Database.Open("Books");
var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");

While the SQL looks a little busy, it only returns two values - the AuthorId for each author, and the first name and last name concatenated with a space between, and delivered under the alias "AuthorName". The thing to do next is to apply these to the select list

<div class="row">
    <span class="label"><label for="authorId">Author:</label></span>
    <select name="authorId" id="authorId">
        <option value="">-- Select Author --</option>
    @{
        foreach(var author in authors){
             <option value="@author.AuthorId">@author.AuthorName</option>

        }
    }
    </select>
</div>

The first option is the default one, and is given no value at all. After that, each option tag is given the ID belonging to the matching author in the list. When the form is submitted, only the ID value is posted back (which is all we are after). The same process is followed for applying a select list of categories:

<div class="row">
    <span class="label"><label for="categoryId">Category:</label></span>
    <select name="categoryId" id="categoryId">
        <option value="">-- Select Category --</option>
    @{
        foreach(var category in categories){


             <option value="@category.CategoryId">@category.Category</option>    
        }
    }
    </select>
</div>

Finally the form is finished off:

<div class="row">
    <span class="label">&nbsp;</span>
    <input type="submit" name="action" id="action" value="Add" />
</div>
</form>

When the form is posted back, the values entered by the user are gathered together and inserted into the database:

if (IsPost){
  var sql = "INSERT INTO Books (Title, ISBN, Description, AuthorId, CategoryId) VALUES (@0, @1, @2, @3, @4)";
  db.Execute(sql, Request["title"], Request["isbn"], Request["description"], Request["authorId"], Request["categoryId"]);
}

Notice the values @0, @1 etc in the SQL? They are parameter placeholders, and protet against SQL injection, which is an attempt to pass malicious code to the database. When the Database.Execute() method is called, the SQL is passed in first, followed by the source of the parameter values. In this case, all of the values come from the Request.Form collection (although the shorthand version Request[index] is used. Items are referenced by their index, which is the name of the form field. The values are passed in to the method in the same order that their parameters appear in the SQL.

Editing an existing book requires an almost identical form. However, the form needs to know which book is being edited. This detail is passed in the URL to the EditBook.cshtml page. Links are created in the listing page (Default.cshtml):

foreach(var row in db.Query(sql, Request["CategoryID"])){
	<h2>@row.Title</h2>
    <p><strong>Author:</strong> @row.FirstName @row.LastName<br />
    <strong>ISBN:</strong> @row.ISBN <br/>
    <strong>Description:</strong> @row.Description <br />
    <strong>Category: </strong> @row.Category</p>
    <a href="@Href("~/EditBook", row.BookId)">Edit</a>
}

The last line of code shows the Href() helper being used to link to EditBook.cshtml using routing, so the file suffix is not needed, and the Id of the book is appended like this: EditBook/5, or EditBook/7 etc. This value is drawn from the URL using the UrlData() helper and used as a parameter for the SQL that gets the specified book's details, along with the data for the authors and categories drop down lists:

var db = Database.Open("Books"); 
var Id = UrlData[0].AsInt();
var sql = "SELECT Title, ISBN, Description, AuthorId, CategoryId FROM Books WHERE BookId = @0";
var book = db.QuerySingle(sql, Id);
var categories = db.Query("SELECT CategoryId, Category FROM Categories");
var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");

The form itself presents the book to be edited:

<form action="" method="post">
<div class="row">
    <span class="label"><label for="title">Title:</label></span>
    <input type="text" name="title" id="title" value="@book.Title" size="50" />
</div>
<div class="row">
    <span class="label"><label for="isbn">ISBN:</label></span>
    <input type="text" name="isbn" id="isbn" value="@book.ISBN" size="20" />
</div>
<div class="row">
    <span class="label"><label for="description">Description:</label></span>
    <textarea cols="50" rows="8" name="description" id="description">@book.Description</textarea>
</div>
<div class="row">
    <span class="label"><label for="authorId">Author:</label></span>
    <select name="authorId" id="authorId">
    @{
        foreach(var author in authors){
            if(author.AuthorId == book.AuthorId){
                <option value="@author.AuthorId" selected="selected">@author.AuthorName</option>
            } else {
                <option value="@author.AuthorId">@author.AuthorName</option>
            }
        }
    }
    </select>
</div>
<div class="row">
    <span class="label"><label for="categoryId">Category:</label></span>
    <select name="categoryId" id="categoryId">
    @{
        foreach(var category in categories){
            if(category.CategoryId == book.CategoryId){
                <option value="@category.CategoryId" selected="selected">@category.Category</option>
            } else {
                <option value="@category.CategoryId">@category.Category</option>
            }        
        }
    }
    </select>
</div>
<div class="row">
    <span class="label">&nbsp;</span>
    <input type="submit" name="action" id="action" value="Edit" />
</div>



</form>

Within the code for both the author and category dropdowns, the Id of the book to be edited is compared with each Id of the author or category, and if there is a match, selected="selected" is added as an attribute to the option. By the time WebMatrix is released fully, there may be helpers for drop down lists, but this is as good as it gets within Beta 1. Now we have a form that gets the selected book and displays it for editing, but nothing happens if the user clicks submit. The code block needs to be changed at the top of the file to update the selected book with the new values, and then redisplay it, so it now looks like this:

var db = Database.Open("Books");
var Id = UrlData[0].AsInt();
var sql = "";
if (IsPost)
{
  sql = "UPDATE Books SET Title = @0, ISBN = @1, Description = @2, AuthorId = @3, CategoryId = @4 WHERE BookId = @5";
  db.Execute(sql, Request["title"], Request["isbn"], Request["description"], Request["authorId"], Request["categoryId"], Id);
}
sql = "SELECT Title, ISBN, Description, AuthorId, CategoryId FROM Books WHERE BookId = @0";
var book = db.QuerySingle(sql, Id);
var categories = db.Query("SELECT CategoryId, Category FROM Categories");
var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");

A new code test has been applied if the page IsPost(), and in that, the values are obtained from the Request collection and passed in to the SQL to update the database. Then the code continues as before, retrieving the selected book for redisplay with the updates.

 

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 23870

3 Comments

- dotnetcoder

Is it possible to create the data access code in a class file? What would be the proper way to do it?

Thanks for the wonderful articles on WebMatrix.

- Mike

@dotnetcoder

Yes it is possible. The "proper" way to do it would be to use Entity Framework or Linq To SQL, or move up to Web Forms or MVC. I doubt Web Matrix (Web Pages) will offer much by way of layering your applications. The intended target for this platform won't be doing that. Otherwise you can just put your class files in App_Code and reference your objects and methods within the code blocks.

- fision

I know this is an old article, but this few examples helped me lot at my beginning with WebMatrix coding.
More over I will enjoy your other articles too.
If you ever update this article could you please be so kind and make an example for checkboxes?

Greetings from Germany

Fision

Recent Comments

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using asp.net web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...

Harris Boyce 04/07/2017 04:17
In response to Razor Pages - The Elevator Pitch
As a developer of a couple "trivial" web pages applications used by non-profits that wouldn't have I...

Cyrus 28/06/2017 20:25
In response to Razor Pages - Getting Started With The Preview
.net core 2.0 preview 2: <a...

ojorma 17/06/2017 09:24
In response to Razor Pages - The Elevator Pitch
Finally I can say goodbye to webforms...