Tutorial:
MS SQL Server : GridViews
Visual Studio 2005 ASP Web Site Forms Part 1
Dr. Thomas E. Hicks
Computer Science Department
Trinity University
This tutorial will walk you through creating Visual Studio Net ASPX web pages which use Grid Views to render information contained in a Microsoft SQL Server Library database.
Prerequisite Software Configuration:
Visual Studio 2005, or later
SQL Server Management Studio or SQL Server Management Studio Express
Prerequisite Database: [The tutorial below will instruct you on how to get this same database into your Management Studio or Management Studio Express]
Get The Database
I recommend getting placing a copy of this database in either Microsoft Management Studio or Microsoft Management Studio Express so that you can work through this tutorial as you go. A copy of the tutorial can be downloaded below.
Upsize The Database
I recommend that you upsize this database in either Microsoft Management Studio or Microsoft Management Studio Express. If you are unsure how to do this, check out the tutorial below:
Tutorial: MS SQL Server :1] The Library database is small and consists of only 6 tables. The Genre relates to the Books!
2] The Library database is small and consists of only 6 tables. The Majors relate to the Students!
3] A Check-Out Transaction occurs when a Student Checks out Books. A TransactionDetail is created for each and every Book checked out by the Student during a single Check-Out Transaction.
4] A Database Administrator might provide the following glimpse into the Library Database.
1] Open Visual Studio 2005. Create a new Web Site. Using the
mouse, hold down the File Menu and Select New and Select Web Site. [See Below!]
1] Select ASP.NET Web Site. Select the Visual C# Language. Select a location for
the directory. I tend to place all web-oriented applications in
C:\Inetpub\wwwroot. I am calling the directory Library. Using the
mouse, push/select the OK Button. [See Below!]
1] Style Sheets are here to stay. This is not a tutorial on Style Sheets, but I am going to create a super simplistic style sheet to reduce the work load for this tutorial. Using the mouse, hold down the File Menu and Select New and Select File. [See Below!]
2] Select Style Sheet.. I am choosing to name my page StyleSheet.css. Using the mouse, select/push the Add Button. [See Below!]
3] I am creating an ultra simplistic Orange-Blue Style Sheet. Simply copy and paste the code below in your style sheet if you wish to use it with the tutorial. Using the mouse, select/push the Save Button. [See Below!] You will normally add much more to your style sheet.
/*******************************************
* Body Of The Orange-Blue Style Sheet *
*******************************************/
body
{
scrollbar-base-color: #21189C;
scrollbar-arrow-color: #FF8200;
background-color: #FF8200;
Color: #21189C;
font color: #21189C;
font-family: 'sans serif',arial;
font-size: 12px;
font-weight: normal
}
/*******************************************
* Horizontal Rule *
*******************************************/
hr
{
border: 0;
height: 4px;
color: #21189C;
background-color: #21189C;
}
/*******************************************
* Data Table *
*******************************************/
table#Data
{
align:center;
font-family: 'sans serif',arial;
color: #21189C;
color: #21189C;
background-color: #D4D0C8;
border: solid #D4D0C8 4px;
border-spacing: 0px;
font-size: 10px;
font-weight: normal;
text-align: Left;
}
table#Data td
{
background-color: #D4D0C8;
// border: solid #000000 1px;
border: solid #D4D0C8 1px;
padding: 4px;
}
/*******************************************
* TriLeft Table *
*******************************************/
table#TriLeft
{
align:center;
border: solid #D4D0C8 0px;
border-spacing: 0px;
text-align: Left;
width:100%;
}
table#TriLeft td
{
padding: 0px;
}
/*******************************************
* More Bold Header *
*******************************************/
.BoldLabel
{
font-family: 'sans serif',arial;
font-size: 12px;
font-weight: bold;
text-align: Center;
}
|
4] Go to the Document properties of your default page, Default.aspx. Find the Document -> StyleSheet property near the bottom. Click ... to associate StyleSheet.css with form Default.aspx. [See Below!]
5] We have only one style sheet in this application. Using the mouse, select it in the right window. Using the mouse, Select/push the OK Button. [See Below]
6] The Default.aspx form should now be Orange with Blue slide bars. [See Below!]
1] Most data is best organized with Panels. Make sure that you can see the Default.aspx form in the design view. Open the Toolbox and expand the Standard selections. Using the mouse, drag the Panel onto your. Save [See Below!]
2] Adjust the Panel Properties Width to 100% so that the our information may use the entire browser window. Set the default Properties HorizontalAlign to Center so that items will be centered by default. Save. [See Below!]
1] We would like to add an Identification label at the top of our Panel; the Default.aspx form in still in the design view. Open the Toolbox and expand the Standard selections. Using the mouse, drag the Label into the Panel. Save [See Below!]
2] Set the Label Properties Text to
Main Menu. Set the Label Properties CssClass to BoldLabel so that items will be centered by default. Save. [See Below!]
1] We would like to Create a template form for the rest of our form; for consistency, it is best that is start out much like Default.aspx. Using the mouse, hold down the File Menu and Select Save As [See Below!]
2] Enter
Template.aspx. Using the mouse, push/select the Save Button. [See Below!]
1] We are going to make copies of Template.aspx several times during the lab; open it. Using the mouse, hold down the File Menu and Select Save As [See Below!]
2] Enter
Student.aspx. Using the mouse, push/select the Save Button. [See Below!]
3] Set the Label Properties Text to
Listing Of All Students. Save. [See Below!]
1] Although you have probably already
tested the form to see that it works, I am going to formally do so at this time.
Compile and start your web app using either
or 
. [See Below!]
2] The first time you execute the program, you may get the Debugging Not Enabled dialog. Enable it. [See Below!]
3] You should now be able to see the Students.aspx form rendered in your default browser. Note that it is associated with Port 1880. [See Below!]
4] The advantage of launching the page
with
is that you do not have to
stop debugging in order to continue
development; if you do not have on your
toolbar, you can include it with by holding down the Tools Menu and Selecting
Customize and dragging it to your toolbar. The advantage
of launching the page with is that
you can include break points and debugging tools when your code becomes more
complex. [See Below!]
1] We would like to add a GridView to our Panel; the Student.aspx form in still in the design view. Open the Toolbox and expand the Data selections. Using the mouse, drag the GridView into the Panel. Save [See Below!]
2] Associated with the GridView is the GridView Tasks. The
Button, at the top of the GridView object,
can be used to open and close the GridView Tasks. Try it! [See Below!]
3] A data source is required if one is to populate the GridView with data from the database. Hold down the Choose Data Source combo box and Select New data source ... [See Below!]
4] Choose the data source type. Select Database. Using the mouse, push/select the OK Button. [See Below!]
5] Using the mouse, push/select the New Connection Button. [See Below!]
6] This server is NET1; you may enter your server or use the combo box to select your server. Using the mouse, push/select the New Connection Button. [See Below!]
7] Select your Microsoft SQL Server Database. I am using Hicks-Library. [See Below!]
8] I recommend testing the new connection. Your GridView will not work if the connection is bad. Using the mouse, push/select the Test Connection Button. [See Below!]
9] Our connection was successful. Using the mouse, push/select the OK Button. [See Below!]
10] Using the mouse, push/select the OK Button. [See Below!]
11] It is time to choose our Data Connection. Using the mouse, push/select the Next Button. [See Below!]
12] Save our Data Connection string. Using the mouse, push/select the Next Button. [See Below!]
13] Select the table or View. Hold the combo down to select the table for our application. Select the Students table. [See Below!]
14] Selecting * will create a Select * statement which includes all fields in the table. Using the mouse, push/select the Next Button. [See Below!]
15] I encourage you to test the query; You might choose to skip this step if the database is large. Using the mouse, push/select the Test Query Button. [See Below!]
16] I encourage you to test the query; You might choose to skip this step if the database is large. Using the mouse, push/select the Test Query Button. [See Below!]
If you don't like the order of the data, you can use the previous button to go back and specify a custom SQL statement that arranges the data differently.
17] Using the mouse, push/select the Finish Button. [See Below!]
18] Launch the web page. [See Below!]
19] Launch the web page. [See Below!]
1] Perhaps you would like to have the grid in the center of the panel. Set the GridView Properties HorizontalAlign to Center. Save. [See Below!]
2] Note that all of the data in the GridView is also centered.. [See Below!]
1] The CSS can colorize the scroll bars with
Internet Explorer; they have no effect on FireFox. Set
the GridView Properties Height to
400.
Set the Panel Properties ScrollBars to
Vertical.
[See Below!]
2] The CSS can colorize the scroll bars with Internet Explorer;
they have no effect on FireFox. Set the GridView
Properties Height to
400.
Set the GridView Properties ScrollBars to
Vertical.
[See Below!] 
1] Set the GridView Appearance Properties BackColor to White. Set the GridView Appearance Properties BorderColor to Black . [See Below!]
2] As is always the case, you should render your web site with all of the major browsers. Note the differences between Internet Explorer and Firefox [See Below!]
1] Set the GridView Layout Properties CellPadding to 5. Set the GridView Appearance Properties CellSpacing to 3 . [See Below!]
2] As is always the case, you should render your web site with all of the major browsers. Note the differences between Internet Explorer and Firefox [See Below!]
1] Set the GridView HeaderStyle Properties
BackColor to
Navy.
Set the GridView HeaderStyle
Properties BorderColor to
Red.
Set the GridView
HeaderStyle Properties
ForeColor to
White
.
[See Below!]
2] As is always the case, you should render your web site with
all of the major browsers. Note the differences between Internet Explorer and
Firefox
[See Below!]
1] This can make it easier to read data from
rows when there are no lines separating the rows. Set the GridView
AlternatingRowStyle Properties ForeColor to
Black.
[See Below!]
2]
[See Below!]
1] This can make it easier to read data from rows when there are no lines separating the rows. Set the GridView AlternatingRowStyle Properties ForeColor to Black. [See Below!]
2] If the user now touches the underlined title in any column, it re-organizes the GridView in ascending order in accordance with that column. I am about to organize the data by first name. [See Below!]
3] It works! [See Below!]
1] Suppose you add the following style to
your StyleSheet.css
/*******************************************
* Left Justified Data *
*******************************************/
.LeftData
{
text-align: Left;
}
Set the GridView Appearance Properties
CssClass to
LeftData.
[See Below!]
2] If the user now touches the underlined title in any
column, it re-organizes the GridView in ascending order in accordance with that
column. I am about to organize the data by first name. [See Below!]
1] Let us return to Default.aspx. If we were to add Hyperlinks to the Panel in Default.aspx, they would be centered. In order to keep the Main Menu title centered and left justify the hyperlinks, we are going to add a left justified panel to the form. Make sure that you can see the Default.aspx form in the design view. Open the Toolbox and expand the Standard selections. Using the mouse, drag the Panel into your Panel. Save [See Below!]
2] Set the Panel2 Layout Properties Height to 100px. Set the Panel2 Layout Properties Height to 100px. Set the Panel2 Layout Properties HorizontalAlign to Left. Set the Panel2 Layout Properties Width to 100%. [See Below!]
1] We are about to add hyperlinks to our form. Rather than configure multiple links, let us add a Hyperlink format to StyleSheet.css. Add the following code to StyleSheet.css.
/*******************************************
* Black-Blue Hyperlinks *
*******************************************/
A
{ font-family: 'sans serif',arial;
color: Black;
font-size: 16px;
font-weight: bold;
}
A:link
{
color:Black;
}
A:visited
{
color: Black;
}
A:hover
{
text-decoration: none;
color:#21189C;
font-size: 24px;
font-weight: bold;
}
A:active
{
color: red;
text-decoration: none;
}
2] Make sure that you can see the Default.aspx form in the design view. Open the Toolbox and expand the Standard selections. Using the mouse, drag the Hyperlink onto Panel2. Save [See Below!]
3] Set meaningful text to the anchor. Set the Hyperlink Properties Text to List All Students. [See Below!]
4] Link the anchor to the proper URL - Press ... [See Below!]
5] Link the anchor to the proper URL (cont) - Using the mouse, select Students.aspx in the left window. Using the mouse, select/push the OK Button. [See Below!]
6] The Navigation URL is ~/Students.aspx. [See Below!]
7] Default.aspx can be seen below. [See Below!]
8] When the mouse is taken over the hyperlink, you can see the hover effect below! It then launches our Students.aspx page; try it. [See Below!]
1] Tool Tips can provide additional information to the person considering our link. Set the Hyperlink Properties ToolTip to See The Current Listing Of All Non-Deleted Students. [See Below!]
2] When the mouse is taken over the hyperlink, you can see the hover effect below! It then launches our Students.aspx page; try it. [See Below!]
