Microsoft ASP .NET Professional Projects - Premier Press

<asp:Label Text='<%# Container.DataItem("code_category") %>' runat="server"/> </ItemTe mplate>

<EditItemTemplate>

<asp:TextBox id="edit_group" Text='<%# Container.DataItem("code_category") %>' runat="server"/>

</EditItemTemplate>

</asp:TemplateColumn> <asp:TemplateColumn HeaderText="Type" >

<ItemTemplate>

<asp:Label Text='<%# Container.DataItem("type") %>' runat="server"/> </ItemTemplate>

<EditItemTemplate>

<asp:TextBox id="edit_type" BorderStyle="None" Readonly="True" Text='<%# Container.DataItem("type")

%>'runat="server"/>

</EditItemTemplate>

</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Opening" > <ItemTemplate>

<asp:Label Text='<%# Container.DataItem("opening") %>' runat="server"/> </ItemTemplate>

<EditItemTemplate>

<asp:TextBox id="edit_opening" Text='<%# Container.DataItem("opening") %>' runat="server"/>

</EditItemTemplate>

</asp:TemplateColumn> <asp:TemplateColumn HeaderText="Closing" >

<ItemTemplate>

<asp:Label Text='<%# Container.DataItem("closing") %>' runat="server"/> </ItemTemplate>

<EditItemTemplate>

<asp:TextBox id="edit_closing" BorderStyle="None" Readonly="True" Text='<%# Container.DataItem("closing") %>' runat="server"/>

</EditItemTemplate>

</asp:TemplateColumn>

</Columns>

<HeaderStyle BackColor="DarkRed" ForeColor="White" Font-Bold="true"> </HeaderStyle>

<ItemStyle ForeColor="DarkSlateBlue"> </ItemStyle>

<AlternatingItemStyle BackColor="Beige"> </AlternatingItemStyle>

</asp:DataGrid>

</form>

</body>

</html>

Masters2.vb is the Code Behind file for this form. Here is its listing:

Masters2.vb

Option Strict Off

Imports System

Imports System.Collections

Imports System.Text

Imports System.Data

Imports System.Data.OleDb

Imports System.Web.UI

Imports System.Web.UI.WebControls

Public Class BaseClass

Inherits System.Web.UI.Page

Protected Grid1 as DataGrid

Protected Message as label

Dim myConnection As OleDbConnection

Dim myCommand As OleDbDataAdapter

Dim ds As New DataSet

Dim ConnStr As String

Dim SQL As String

Sub Page_Load(Source As Object, E As EventArgs)

ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User

ID=sa;"

myConnection = New OleDbConnection(ConnStr) if NOT (isPostBack)

rebind end if

End Sub

Sub ReBind()

SQL = "select m.*, g.code_display as category "

SQL = SQL + "from masters m, groups g "

SQL = SQL + " where m.code_category = g.code_value" myCommand = New OleDbDataAdapter(SQL, myConnection) 'use Fill method of DataSetCommand to populate dataset myCommand.Fill(ds, "masters")

'Binding a Grid

Grid1.DataSource=ds.Tables("masters").DefaultView

Grid1.DataBind()

End Sub

'——————— New Events (additions to Masters1.vb)—————————————-

Sub Grid1_Edit(Sender As Object, E As DataGridCommandEventArgs)

Grid1.EditItemIndex = E.Item.ItemIndex

ReBind()

End Sub

Sub Grid1_Cancel(Sender As Object, E As DataGridCommandEventArgs)

Grid1.EditItemIndex = -1

ReBind()

End Sub

Sub RunSql(sql as string)

'This is a placeholder for the functionality we will code in Masters3.vb

response.write(sql)

End Sub

End Class

I need to explain quite a number of things here, so I will run down the code contained in the files Masters2.aspx and Masters2.vb one step at a time.

Look at the tags that create the DataGrid in the aspx file. The first column that I have specified is the EditCommandColumn. This is an ASP.NET generated column and it creates the "Edit" link button. When in Edit mode it creates the "OK" and "Cancel" buttons. There are three events (OnEditCommand, OnCancelCommand, OnUpdateCommand) that correspond to these buttons. I have coded three functions that get fired when these events get executed (Grid1_edit, Grid1_Cancel,

Grid1_update).

Template columns are used to organize a DataGrid's columns , much like an XSL template. It allows the developer to set the properties for the column. Each column can have two templates: An ItemTemplate and an EditItemTemplate. An

ItemTemplate displays the value of the column in a read-only manner. When the grid goes into the Edit mode, the EditItemTemplate is displayed, and you can change the column value. Think of the ItemTemplate and EditItemTemplate as two separate controls (with separate ids). The following code shows what the "Name" column looks like:

<asp:TemplateColumn HeaderText="Name" >

<ItemTemplate>

<asp:Label Text='<%# Container.DataItem("code_display") %>' runat="server"/>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox id="edit_name" Text='<%# Container.DataItem("code_display") %>'

runat="server"/>

</EditItemTemplate>

</asp:TemplateColumn>

The use of the Execute keyword to run a stored procedure will only work with MS SQL Server databases. In order to call a stored procedure in other databases, you need to use the Command object, set its CommandType property to

StoredProcedure and use the Parameters property to access input and output parameters and return values.

The Container means the parent control, which in this case is the grid. I am binding the label to the code_display DataItem of the grid.

The grid needs to be told which row is being edited. This is done by setting the EditItemIndex property of the DataGrid to the index of the button that was clicked as in the Grid1_edit event. To cancel the editing just set the EditItemIndex to -1 as in the Grid1_cancel event. Note that in each case I have to rebind the DataGrid for the changes to take effect.

The Grid1_update event gets fired when the "OK" button is clicked. I have written a stored procedure, p_masters, which takes care of the business of adding and updating rows. I will discuss this procedure in detail in Chapter 5, "Input Validation." For the moment, it is sufficient to know that the call syntax is as follows:

Execute p_masters @code_value, @code_display, @code_category, @type, @opening, @closing.

When I pass the procedure a code_value (the primary key), it updates the record with that code_value with the new values. If I pass it a null code_value, it inserts a new record.

Using a stored procedure is a great way to encapsulate the insert/update functionality. Your form becomes very lean; it is now only concerned with extracting appropriate values and passing them onto the stored procedure. The stored procedure can do validation, multiple table updates, and much more.

I will not actually call the procedure but I will build the syntax and write it to the screen. Also note that I am only dealing with the "update" mode. I will incorporate the record addition and deletion functionality and work with real calls to the procedure.

In the "DataGrid Tag" I specified that the DataKeyField was equal to the "code_value". This is the primary key and it is extracted as follows:

Dim code_value as string = Grid1.DataKeys.Item(E.Item.ItemIndex).ToString

In the EditItemTemplate of each column, I gave each column a unique id. Thus, the name column had an id = edit_name when in Edit mode. I find this control using the FindControl method and assign it to a textbox, where I can access its text property.

Dim myTextBox as textbox

Dim code_value as string

myTextBox = E.Item.FindControl("edit_name")

code_display = mytextbox.text

To build the stored procedure call, I build the following string:

sql = "Execute p_masters " + code_value + ", '" + code_display + " ',"

sql = sql + code_category + ", '" + type +"' ," + opening + "," + closing

Using the Execute keyword I am telling our database to execute a SQL command (specified in the sql string). I find this syntax very convenient to use. You could call the stored procedures using the command object and setting the parameters to be passed to the stored procedure. However, I find that I need to write much longer code that way, as I have to write a line of code for each parameter. Here, I can make the procedure call in two lines of code.

Caution

Sorting and Paging

The DataGrid includes features that allow you to set up sorting and paging functionality. When the sorting functionality is enabled, links appear under the column header names. When you click on a column link, that column sorts the grid.

The paging functionality of the DataGrid allows you to set the number of records that can be displayed per page. Users can then navigate to different recordsets by clicking on the paging links that appear at the bottom of the DataGrid. Th ese links can appear as numeric links or VCR-type "next" and "previous" buttons.

I will base my discussion on an example I have developed, the code of which is contained in the file PagingSorting.aspx and its associated Code Behind file PagingSorting.vb. These files can be found in the samples folder for this chapter on the book's Web site at www.premierpressbooks.com/downloads.asp.

Sorting

The DataGrid allows you to sort the columns by clicking on a link below the columns. Setting the AllowSorting property to true triggers this built-in mechanism and it is as follows:

<asp:DataGrid id="Grid1" runat="server"

AllowSorting="true"

OnSortCommand="MyDataGrid_Sort">

When this property is set to true, the DataGrid renders the column captions with a LinkButton. If you now click on a column, the OnSortEvent is fired. This event contains the following code:

Sub MyDataGrid_Sort(sender As Object, e As DataGridSortCommandEventArgs)

SortField = e.SortField

ReBind

End Sub

The SortField variable is a Public (string) variable that holds the name of the column by which the DataGrid is to be sorted. It is first set in the Page_Load event and later whenever the user clicks on a sortable column. The Page_Load setting is as follows:

Public SortField As String

Sub Page_Load(Source As Object, E As EventArgs)

If NOT (isPostBack)

If SortField = "" Then

SortField = "code_display"

End If

ReBind

End If

End Sub

The rebind function uses the SortField to sort the DataView to which the DataGrid is bound. It refreshes the DataGrid to reflect the rows sorted by the new sort field in the sub called Rebind as follows:

Sub ReBind()

'DataSetCommand

SQL = "select m.*, g.code_display as category "

SQL = SQL + "from masters m, groups g "

SQL = SQL + " where m.code_category = g.code_value"

myCommand = New OleDbDataAdapter(SQL, myConnection)

'use Fill method of DataSetCommand to populate dataset

myCommand.Fill(ds, "masters")

'Sort accounding to sortField

Dim dv2 As DataView

dv2 = ds.Tables("masters").DefaultView

dv2.Sort = SortField

Grid1.DataSource= dv2

Grid1.DataBind()

End Sub

You need to set the SortField property in the column templates. For example for the code_display column to participate in sorting, you have to set the template as follows:

<asp:BoundColumn HeaderText="Account" DataField="code_display" SortExpression="code_display">

<HeaderStyle Width="150px">

</HeaderStyle>

</asp:BoundColumn>

Paging in DataGrid

The DataGrid has a built-in pager control which displays a user-defined number of pages per page and also numeric or "next/previous" buttons at the bottom of the DataGrid. Clicking on these links displays the next set of pages and so on. To enable paging you set a number of properties as follows:

<asp:DataGrid id="Grid1" runat="server"

AllowPaging="True"

PageSize="5"

PagerrStyle-Mode="NumericPagesi"

PagerrStyle-HorizontalAlign="Right"

PagerrStyle-NextPageTText="Next"

PagerrStyle-PrevPageTText="Prev"

OnPageIIndexChanged="MyDataGrid_Page"&>

The AllowPaging property must be set to true to enable paging. The PageSize property sets the number of records per page. A PageSize of 5 implies that only five records per page will be shown. If you leave out the PagerStyleMode="NumericPages" property then instead of numeric links at the bottom you get two links; next and previous. The PagerStyle-NextPageText and the

PagerStyle-PrevPageText properties are descriptive captions for these two links and they can be any text you want.

You are required to code one event. This is the OnPageIndexChanged event, which fires off the MyDataGrid_Page event. You simply call the rebind function in this event as follows:

Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)

ReBind

End Sub

Here is the compete source listing:

PagingSorting.aspx

<%@Page Language="VB" Inherits="BaseClass" Src="PagingSorting.vb" %> <html>

<head>

<title>Masters DataGrid 1</title> </head>

<body>

<br>

<form runat=server>

<b>Sorting and Paging</b><br> <asp:DataGrid id="Grid1" runat="server"

AutoGenerateColumns="false"

BackColor="White"

BorderWidth="1px" BorderStyle="Solid" BorderColor="Tan"

CellPadding="2" CellSpacing="0"

Font-Name="Verdana" Font-Size="8pt"

AllowPaging="True"

PageSize="5"

PagerStyle-Mode="NumericPages"

PagerStyle-HorizontalAlign="Right"

PagerStyle-NextPageText="Next"

PagerStyle-PrevPageText="Prev"

OnPageIndexChanged="MyDataGrid_Page"

AllowSorting="true"

OnSortCommand="MyDataGrid_Sort"

>

<Columns>

<asp:BoundColumn HeaderText="Account" DataField="code_display" SortExpression="code_display">

<HeaderStyle Width="150px">

</HeaderStyle>

</asp:BoundColumn>

<asp:BoundColumn HeaderText="Group" DataField="category" SortExpression="category">

<HeaderStyle Width="150px"> </HeaderStyle>

</asp:BoundColumn>

<asp:BoundColumn HeaderText="Type" DataField="type"> <HeaderStyle Width="50px">

</HeaderStyle>

</asp:BoundColumn>

<asp:BoundColumn HeaderText="Opening" DataField="opening"> <HeaderStyle Width="50px">

</HeaderStyle>

</asp:BoundColumn>

<asp:BoundColumn HeaderText="Closing" DataField="closing"> <HeaderStyle Width="50px">

</HeaderStyle>

</asp:BoundColumn>

</Columns>

<HeaderStyle BackColor="DarkRed" ForeColor="White" Font-Bold="true"> </HeaderStyle>

<ItemStyle ForeColor="DarkSlateBlue"> </ItemStyle>

<AlternatingItemStyle BackColor="Beige"/> </asp:DataGrid>

</form>

</body>

</html>

The Code Behind for this form is as follows:

PagingSorting.vb

Imports System

Imports System.Collections

Imports System.Text

Imports System.Data

Imports System.Data.OleDb

Imports System.Web.UI

Imports System.Web.UI.WebControls

Public Class BaseClass

Inherits System.Web.UI.Page

Protected Grid1 as DataGrid

Dim myConnection As OleDbConnection

Dim myCommand As OleDbDataAdapter

Dim ds As New DataSet

Dim ConnStr As String

Dim SQL As String

Public SortField As String

Sub Page_Load(Source As Object, E As EventArgs)

ConnStr = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=ASPNET;User

ID=sa;"

myConnection = New OleDbConnection(ConnStr) if NOT (isPostBack)

If SortField = "" Then

SortField = "code_display"

End If rebind

end if