HomeASP.NET Creating an ASP.NET Dynamic Web Page Using...
Creating an ASP.NET Dynamic Web Page Using MS SQL Server 2008 Database (GridView Display)
Dynamic pages (pages that pull, insert, update and delete data or content from a database) are extremely useful in modern websites. They provide a high level of user interactivity that improves user experience. This article will show you how to create such pages in ASP.NET that use a Microsoft SQL Server 2008 database.
For example, the dynamic table/web page in the screen shot below was created using ASP.NET 3.5 in a MS SQL Server 2008 database:
It offers two choices to users. First, they can edit the row data; second, they can delete records. Bear in mind that any update made to the web pages using a browser will automatically be reflected in the database.
This type of application sees lots of implementation on the Internet. You can see it in action on forums, e-commerce websites, administration panels, guest books, etc. If you are beginner in ASP.NET website development, this tutorial is an important introduction to creating database-driven web pages or websites.
This tutorial assumes that you have fully learned the fundamentals of creating ASP.NET web pages. You may wish to read the following articles for a review of its technology and its tools (such as Visual Web Developer Express 2008):
Let’s make a real world project. We're going to develop an ASP.NET 3.5 application that will basically manage the tasks of a home renovation project team online. The page should list all of the tasks and deadlines to be met by each member of the team. The team member in charge (supervisor) has the freedom to edit the tasks, the person in charge of the task, and deadline, depending on the daily progress of the project. In Excel, the design table with the actual tasks/project plan should look like this:
To create a new MS SQL database containing the above table (the file importing method will be covered in a separate topic):
1. Launch Visual Web Developer Express 2008.
2. In the dashboard (under “Recent Projects”), create à web site
3. Under “Visual Studio Installed Templates” select “ASP.NET web site.” And then on the location, go to “File System,” “Language: Visual Basic,” and filename/path enter: E:renovationproject
If you do not have a Drive E in your hard disks, use a different/appropriate Windows hard drive. Your website files, automatically created by Visual Web Developer Express, will be saved in that folder (renovationproject). Finally, click OK.
4. Once the default.aspx file is created, look for “Solutions Explorer,” right click on “ App_Data” and then click “Add new item.”
5. Select “SQL Server Database” and for the name, enter "renovationproject.mdf" but leave the language at default (Visual Basic).
6. Once your database has been created, the next step is to configure the table field names. Go to “Database Explorer” à right on “Tables” and click “Add new table.”
7. MS SQL Server will ask you to enter the column names, data types and allow null settings. Enter the ones from the screen shot below:
The data types' design depends on the type of data that will be entered in the field. After typing it completely, hit the save button and enter renovationproject as the table name.
8. MS SQL database and ASP.NET web applications need a primary unique key identifier to work. This field should have entirely unique records, for example “workid” (which is a sequential number from 1 to the maximum number of records).
Select the workid row (highlighting the entire row) and then click the “Set Primary Key” icon in the toolbar (the one that uses the yellow key icon). After completing the settings, hit the “Save all” button again.
9. Now that the workid is the primary key, you need to instruct MS SQL Server to “automatically” increment values every time a record is inserted. Go to “Identity Specification” under “Column Properties.” Hit expand (+ arrow) and change "Is Identity" values from No to Yes. Hit the “Save all” button again on the toolbar.
10. You can now enter records in the “renovationproject” database table. Go to Database explorer à Tables à right click on “renovationproject” and click “Show table data." Type all of the records shown in this Excel file.
After all items are entered into the database, it should look like the screen shot below:
Finally, click “Save all” button again. You have now successfully created the database and its associated table.
Click “Default.aspx” in Visual Web Developer, and using the source code view, click and drag SqlDataSource (View à Toolboxà Data à SqlDataSource) to between <div></div> tags. After dragging, the updated code markup should now be:
Go to the Design view and right click on the “SqlDataSource- SqlDataSource1” button. Click “Configure Data Source.” And then, in the drop down menu, select “renovationproject.mdf” and click Next. Check “Yes, save this connection as: ConnectionString” and then click Next.
Select “Specify columns from a table or view.” Under the name field, select renovationproject and then check “*”.
Click “Advanced…” à check “Generate INSERT, UPDATE and DELETE statements” and then click OK; after that, proceed to NEXT. Try clicking “Test Query.” If you see the records exactly as they are in the database, then click FINISH. Hit the “Save all” button again.
Step 3: Configure and Add Gridview Controls
The function of GridView controls is to display data from the DataSource controls to the web browser. Add two breaks after </asp:SqlDataSource>
<br/>
<br/>
And then, click and drag the “GridView” (View à Toolbox à Dataà GridView). The associated source code is:
<asp:GridViewID="GridView1"runat="server">
</asp:GridView>
You need to tell GridView from which datasource it needs to extract data. Go to the Design view à right click on the GridView table and click “Show Smart Tag.”
Under “Choose Data Source,” select “SqlDataSource1.” This is the ID of the data source control used earlier. You can even customize the formatting of your GridView table; right click on it, click “Auto format” and select “Snowy Pine.”
To let users edit and delete specific records when they're shown in the web browser, right click on the GridView table in “Design” à “Show Smart Tag” à check the following:
We're nearly done, but you need to add useful content for visitors to properly understand what the page is all about. Say, for example, you add an <h3> tag:
<h3>Alexis Agency Home Renovation Project Code 115</h3>
Place that above the GridView control. And then just below the <h3> tag, add a short <p> containing descriptive text:
<p>Note to Supervisors: Monitor the daily progress of the code 115 renovation project and adjust project details if necessary.</p>
All of this editing can be done in the Design view. Below is the completely edited dynamic web page (with header and p tags) of the project in Visual Web Developer Express Design view:
Step 5: View the project in the browser
Finally you can launch the dynamic web page and view it using a browser: File à View in Browser, you should see something like the one on this page screen shot:
In the screen shot of the provided link above, the supervisor is in the process of editing Patrick's assigned task of repairing John's toilet bowl. And when it's done, clicking “update” can automatically insert that data in the MS SQL Server 2008 database (replacing the old data).
ASP.NET 
ASP.NET Export to Excel and Word using VB.NET and C#
