Creating a Database for our site - Part 4

Now that we know how to display the records on our web page, we are going to learn how we can edit, delete or add records in our database. Of course, we can do these tasks by opening the table in the VWD IDE but it would be a lot easier if we do them through our application.

Before we proceed, we should ask ourselves a basic question. Do we want all our users to have the rights of adding, deleting and editing the data? We certainly do not want this. Only a select group of users, say the administrators of the site, should have these rights. In other words, the page in which these actions will happen should be visible to only a selected group of users.

In the next lesson, we will learn how to provide such a security system to our site. In this part however, we will prepare for this. There is nothing much to do. All we need is to create this page (the page for editing, deleting and adding records) inside a folder.

Let us see how to do this:

• Open the Solution explorer window. Right click on the project name and from the menu, select the `New Folder' option. A new folder is now added to our project. Give it a suitable name.

• Now right click on this new folder and select the `Add New Item...' option. Just as we have seen so far, this will open up the dialog box from where we can add a new page (web form) to our project. This time, it is not necessary to check the `Select master page' checkbox, as this page will not be visible to general members or anonymous visitors of our site. Only the administrators of the site will use it.

That is all we need to make this page secure. We will learn how to implement the security in the next lesson. For now, let us see how we can add the features of editing, deleting and adding records.

The first step is to add a data source control. The data source control connects to the database for retrieving the data. We know how to add and configure the SqlDataSource control. We will do the same now, only changing one small configuration option.

• Drag and drop the SqlDataSource control on the page. From the smart tag menu, select the Configure data source option.
• In the first dialog box, extend the drop down list and select the name of the connection string we created earlier. Click on the `Next' button.
• In the next screen, select the checkbox with the * symbol. This means that we want to select all the columns of the table, including the primary key column (BookSerialNo). The equivalent SQL statement is:

SELECT * FROM [Books]

Which can be interpreted as -

Select all from the table `Books'.

Now comes the important part. Click on the button `Advanced'. A dialog box will pop up.

Check both the checkboxes here. This will allow us to add, delete and update the records. Click on the OK button. Then click the `Next' button to proceed.

• In the last step, we can test the result of our query. Click the `Finish' button to complete the configuration of the SqlDataSource control.
• Drag and drop a GridView control on the page. From its smart tag menu, expand the `Choose Data Source' drop down list and select SqlDataSource1 option. This is the data source we have just configured. Next, check the checkboxes `Enable Paging', `Enable Sorting', `Enable Editing' and `Enable Deleting'. This will add the Editing and Deleting features to the GridView control.

Notice that there is no direct option available in the GridView control to insert (add) a new record. For this, we need another data bound control. We will use the DetailsView control.

• Drag and drop the DetailsView control from the `Data' section of the toolbox. From its smart tag menu, expand the `Choose Data Source' drop down list and select SqlDataSource1. Next, check the `Enable Inserting' checkbox.

We are now ready to test our page. Before that, we can use the `Auto Format' option for both the GridView and DetailsView controls to provide a better look. We can also add simple text captions for both the controls.

Now is the time to test our page. Save the project. Run the page using the `Ctrl+F5' keys.

In the browser, click on the `Edit' link of a row. All the fields of the row will be available for editing in text boxes. We can keep the changes by clicking on the `Update' link or discard them by clicking on the `Cancel' link. Easy! Similarly, clicking on the `Delete' link will delete the record.

Click on the `New' link in the DetailsView control. We will get text boxes corresponding to each field for entering data. To keep the newly added record, click on the `Insert' link. To discard the new data, click on the `Cancel' link.

We have achieved the tasks of editing, deleting and adding records with a few simple steps and, once again, without writing any code. This is the power and simplicity provided by ASP.NET 2.0!