Blog Archives

ASP.NET | Basic Database Manipulation

You want to add a database table in your web application? want to perform ADD, EDIT, DELETE without learning SQL? Here it is…

-Microsoft Access 2007
-Visual Studio 2010

Before we start connecting and populating our database table data in Visual Studio 2010, first we shall make our database using Microsoft Office Access 2007. Follow the instructions below:

Open Microsoft Office Access 2007. There you’ll see a category “New Blank Database” then click the button with a caption “Blank Database” so that Access 2007 will prepare for a database with no data and table yet.

Now, upon clicking it, on the right corner of Access 2007, you will be asked to input your desired Database name with a default “Database1” value. Change it to “Students” instead. Also, you can specify in which path of your computer it will be saved. By default, it is saved in your computer’s My Documents folder. Change it to drive C:\ instead so that it is easy to locate.

Once you finished specifying the Database name and the location in which the file will be saved, click the “Create” button.

By default, Access created a blank database together with a blank table. Blank table means there’s no data inside it. That table is named “Table1”. We will change it to “Section_P1” as we will be inserting names and data from the students of Section P1.

To rename it, first right-click on the Tab that says “Table1”, and choose Save, so that Access will save the database and prompt you for a different table name.

Once the prompt appears, input “Section_P1”, and then press the Ok button.

Now that we have renamed the table, it’s time for us to define what types of data our table will hold. To do that, simply right-click on the Section_P1 tab on the upper left part of access, and choose Design View.

In Design View, we can perform things necessary for our table. We can add, remove or rename fields (in Excel, we call it Columns), specify what types of data a field can hold (numbers, letters, telephone numbers, date), and assign Primary Key (a Field that can hold unique data only). In the newly created table, we are already given one field called “ID”, with AutoNumber data type (meaning, it will automatically insert sequential numbers every time we insert data in other fields). It is also set as the table’s Primary Key, which means that it is not possible to hold data that is already used in the same field.

Let’s add 3 more fields in our table. Add more fields based on the information below:

Field Name Data Type
First_Name Text
Last_Name Text
Stud_Number Text

Specifying some values in the Description is optional. By following the information above, your table should contain those fields now, just like the picture below:

After that, right-click the Section_P1 Table tab on the upper left corner and then choose “Datasheet View” from the menu to go back to the area where you can add data to your table. It is mandatory to save your table first before continuing.

In Datasheet View, you will finally notice that the fields we have added are now appearing in our table.

Now let’s try to add Data in our table. Below is the list of students currently enrolled for the subject CSWD01 section P1. They are names (first name and last name) as well as their student number in alphabetical order. Simply analyze the information and add them to the fields.

After that, your table should be populated like this:

Save it and close Microsoft Access 2007 for now.

The next thing to do is to resume our work in Visual Studio. Load up your Web Application in Visual Studio. Once done, create a new Tab with a title “CSWD01 P1-Students” as well as a web form for it. Refer to the image below:

The form we just created will be the place where we will show the data in our table.
Do you still remember where you save your Database? Coz’ now we will try to connect our database to our project. To do that, in Menu Bar, click Data and then Add New Data Source.

After that, a window will appear in which VS will ask you the type of the data source. By default, the value Database is already selected. Leave it and click Next.

On the next window, you will be asked to choose which Database Model to use. Choose DataSet coz’ that what is needed in our project and click Next.

The next window is where you will specify the datasource. By default, the drop-down list is empty so the choice is to click New Connection. Upon clicking it, a new window will appear wherein you can locate your database file as well as the Data Provider to use. In the Data Provider option, choose OLE DB as we are using Microsoft Access Database and not SQL Database. Click the “Browse” button to locate your Database file.

Our Database is saved in drive C:\ so locate the folder C:\ and click on the database file. Finally, click the Open button.

Once opened, optionally you can click the “Test Connection” button to make sure that the project and the database file can establish connection to each other. Otherwise, click the OK button and you will be redirected back to the wizard. Click Next right after that.

Another page from the wizard will appear asking you to save the connection string to the application configuration file. Simply click Next so that you can choose your Database Object that you want to access in your project. Check both “Tables” and “Views” and click Finish.

After that, you will be brought back to your Web Forms.

Now, to add the Database table to your Web Form, simply, on the left corner of VS, in the same place where you can find the ToolBox, click on Server Explorer.

There, you will see a nested category named “Data Connections” wherein a clickable arrow is right before it. Click it to expand, just like how the picture below is:

Under Tables, there goes our Section_P1 table and under it is the fields we have created earlier (ID, First_Name, Last_Name, Stud_Num). To insert the Table in our Web Form, simply drag-and-drop the Section_P1 label to an area in our form.

The result should be like this, together with a variety of options we can use to manipulate our database table.

However, by default, our Web Application is not able to modify or perform manipulations like adding data, renaming them and saving them to the database source file. To do that, simply check the following:

Enable Paging – So that if the table has more than a lot of values, VB will redesign it in a page-like view. (Optional)
Enable Sorting – If enabled, you can click the field names/columns to sort data.
Enable Editing – This means that we can edit data already stored in the table and update them right after.
Enable Deleting – If enabled, users can remove rows of data in the table.
Enable Selection – Mandatory. This means that we can select a certain row of data.

Right after that, debug the Application by pressing F5. The resulting page should return something like this:

Now we can manipulate our database table using our web app. Try opening the Database file to see whether your changes in your application works. Take note: originally you database file is stored in C:\. However, as we recall, VB asked us to create a copy of our database file in which that file will be used in the project instead of the file stored in C:\. That file is stored inside your Web Application project folder, in a subfolder called App_Data. In my case, it is stored inside C:\Users\kenokenopi\Documents\Visual Studio 2010\Projects\Dayapan\Dayapan\App_Data\Students.accdb

There you go. Have fun with Database 

|> Slipknot – The Heretic Anthem