Adding Data to the Books Table

Adding Data to the Books Table

At this point we have created a database and created a Books table. However, there is currently no information stored in our table! The next step, then, is to add data to the Books table. This can be accomplished either through Visual Web Developer or from an ASP.NET page. In this hour we'll add database records using Visual Web Developer; in Hour 16, "Deleting, Inserting, and Editing Data," we'll see how to add data to a database table from an ASP.NET page.

To edit, delete, or add data to a database table via Visual Web Developer, go to the Database Explorer and expand the Tables folder, which will list the tables of the database. Next, right-click on the table whose data you want to edit and select the Show Table Data option. This action will display the contents of the selected table, as shown in Figure 13.13.

Figure 13.13. The contents of the Books table are shown.

As Figure 13.13 shows, currently there are no records in the Books table. To add a new record to the table, simply click on the text box beneath the BookID column. You can now use the Tab key to move from one field to another, using the keyboard to enter the text you want in the table.

When entering data into these cells, keep in mind that the values you enter must correspond to the particular column's properties. That is, the PageCount column is an int type, meaning that you must enter a number. If you try to enter a value like Scott or one hundred pages, you will get an error message. Similarly, some columns can accept Null values, whereas others are not. Those columns that cannot accept Null values must have a value entered, whereas the ones that can accept Nulls may optionally be left as Null.

Similarly, for certain data types, the format you enter the data is important. For example, the Price column, which is of type money, accepts only numeric values, like 14.95. If you try entering currency signs, you will get an error. Furthermore, when you enter dates, the date format depends on your computer's date/time settings. In the United States, you can enter the date as month/day/year, like 11/19/2005 for November 19, 2005. In many European countries, the format is day/month/year, like 19/11/2005. To circumvent any cultural date/time intricacies, you can always enter a date using the universal format, which is year-month-day, like 2005-11-19 for November 19, 2005.

By the Way

Don't worry if you happen to enter invalid data when trying to create a new record. You won't lose your database's data. Rather, you'll be presented with a helpful error message explaining the problem.

Add a new record to the table by entering Visual Studio Hacks in the Title cell, James Avery in the Author cell, 2005 for YearPublished, 24.95 for Price, and 478 for PageCount. Leave the BookID and LastReadOn cells with their default values, Null. At this point your screen should look similar to Figure 13.14.

Figure 13.14. Our first book in the Books table is James Avery's Visual Studio Hacks.

Note that when you first start entering data in the row's cells, a new, blank row appears below the row you're typing in. Also, whenever you change a cell value and move to the next cell, a little red exclamation point icon appears. This indicates that the value of the cell has changed. If your cursor is still sitting in the PageCount cell and you still see the red exclamation point icons (as is the case in Figure 13.13), the new record has yet to be created. To commit the new record to the database, simply click in the next row (or press the Tab key to move the cursor down to the next row).

Watch Out!

The BookID column is an Auto-increment column, meaning that you cannot specify its value. Rather, you need to let the database system decide what value to use. Therefore, when adding a new record to the Books table, you must leave BookID as Null. If you attempt to enter a value, you will receive an error.

If you left BookID as Null and, when trying to save the record, were told that you needed to provide a value for BookID, then you did not correctly mark the column as Auto-increment. Return to the "Uniquely Identifying Each Record" section and make sure to mark BookID as Auto-increment.

After you have done this, the red exclamation point icons will disappear, and a value will be automatically inserted into the BookID cell (see Figure 13.15). Remember that BookID is an Auto-increment column, meaning its value is automatically inserted by the database. For example, if you add another record to this table, the next record will have a BookID value of 2.

Figure 13.15. A new record has been added to the Books table.

Let's practice by adding a few more records to the Books table. Go ahead and add the following four records:

• The book Create Your Own Website written by Scott Mitchell in 2005. The cost of this book is $19.99 and is 195 pages long. Assume that you last read this book on August 1, 2005.

• The book The Number, by Alex Berenson. This book was published in 2003 at a cost of $24.95, totaling 274 pages in length. Assume that you last read this book on November 14, 2005.

• The book The Catcher in the Rye by J.D. Salinger. Your copy of this book was published in 1991 and costs $6.95. There are 224 pages. Assume you last read this book on June 12, 2004.

• The book Fight Club, by Chuck Palahniuk. This book was published in 1999 and costs $16.95. There are 187 pages. Assume that you have yet to read this book (that means to leave LastReadOn as Null).

After you have entered these four additional rows into the Books table, your screen should look similar to Figure 13.16. Notice that the BookID value, which we never entered, has automatically increased with each new record added.

Figure 13.16. The Books table now contains five rows.

Did you Know?

In addition to your being able to add new records to the Books table, Visual Web Developer also makes it easy to edit a table's existing data through the same interface. To change an existing record's contents, simply click in the appropriate row's area and make any changes needed.

Recall from the beginning of this hour that a database is a collection of structured information that can be efficiently accessed and modified. We've seen how to create the structures to hold information (database tables) and we've looked at how to add data to a table, but we've yet to examine the most useful aspect of databases: efficiently accessing the data stored within. This topic, though, will have to wait until the next hour.