More Web-Database Examples

More Web-Database Examples

Displaying Images from the Tables

In this example, we want to display images with other information stored in a table. In the following ASP.NET page, we use three controls: a button, a label, and a DataGrid. Upon clicking the button, the SQL statement is executed that displays all information in the Books Table. The Books Table is in the Microsoft Access “Northwind” database. The references to the pictures are in the Books table and the actual pictures are in a folder called “image”. The image folder and the following source code are in the same folder.

Run SelectAllBooks(See results next page)

Folder Arrangement in the Web Server

Reference to image files in the database

Adding Data into the Database

In the example before, when data was added to a DataSet using a DataAdapter, it was not inserted into the database; rather stayed in the memory. In order to select, insert, delete, and update data into a database, we need to use a method of the OleDBCommand object. The following properties and methods are commonly used for database manipulation:

Property / Description
CommandText / Contains the SQL command or the name of a stored procedure.
CommandType / Indicates the type of command to be run: StoredProcedure, TableDirect, or Text. The default is Text.
Method
ExecuteReader / This executes the command and returns a DataReader object.
ExecuteNonQuery / This executes the command but does not return any data. It is used for insert, update, and delete to a database which does not require any values to be returned.

In the following example, we read the first name and last name of a person and add that into the Employee table in the Northwind database. If the input boxes are empty, we display a message to the user. One must verify that the data is added to the database.

Opening Screen

Screen with incomplete data

Screen after completing input boxes

Run a version of the Program to display data before and after the insertion.

Run AddDataBaseDataViewCode

Updating Database Data

Updating a database is similar to adding data into the database. In the following example, we first retrieve LastName from the Employee table of the Northwind database to a drop-down list using the Page_Load routine. Upon selecting a LastName, the EmployeeId, LastName, and FirstName of the from the Employee table is displayed in three input boxes, as well as in a DataGrid control.

The user can then change the LastName and FirstName of the Employee. Upon pressing an update button, the Employee table is updated with the changed values. The update statement as well as the new data from the database is also displayed for user verification.

Run UpdatingDatabaseDataView Code

Screen with the drop-down data

Screen after selecting an employee

Screen after update

Deleting Database Data

Deleting database data is similar to updating data into the database. The following example is very similar to the previous codes, except the SQL string for the Delete statement. Again, first we retrieve CustomerID and ContactName from the CustomerData table of the Northwind database to a drop-down list using the Page_Load routine.

Upon selecting a ContactName, more attribute values of the customer is displayed in three input boxes, as well as that in a DataGrid control. The user can then delete the record from the CustomerData table by pressing the Delete button.

Upon pressing the button, the record is deleted from the database, a SQL statement is responded back to the user, and the modified CustomerData table is displayed to the user for verification.

Again, apart from some changes of variable names, the only difference in the code from the previous one, is the SQL statement in the subroutine btnDelete_Click:

commandString = "DELETE from CustomerData WHERE CustomerID = '" & employID & "'"

Run DeleteDatabaseDataViewcode

Opening Screen to select a person

Screen after Selecting a person from the list

Screen after pressing the Delete button

Using two ASP files in Deleting and Viewing

In this version of the file, the last part of the ASP.NET functionality – to display the database results after the deletion – is performed through a separate ASPX file:SelectCustomerAll.aspx. The file is invoked through a ASP.NET hyperlink control, as illustrated below, at the bottom of the page: Verify the Deleted Customer.

Run DeleteDatabaseDataTest

Displaying results from SelectCustomerAll.aspx

1