Microsoft ASP .NET Professional Projects - Premier Press
.pdf
Sub RunSql(sql as string) try
Dim mycommand2 As New OleDbCommand(sql,myConnection) myConnection.Open()
myCommand2.ExecuteNonQuery()
myConnection.Close() 'turn off editing Grid1.EditItemIndex = -1
Catch ex As OleDbException ' SQL error
Dim errItem As OleDbError
Dim errString As String
For Each errItem In ex.Errors errString += ex.Message + "<br/>"
Next
Message.Text = "SQL Error.Details follow:<br/><br/>" & errString Message.Style("color") = "red"
Catch myException as Exception Response.Write("Exception: " + myException.ToString()) Message.Style("color") = "red"
End try rebind
response.write(sql) End Sub
The following is the complete code listing:
Masters3.aspx
<%@Page Language="VB" Inherits="BaseClass" Src="masters3.vb" %> <%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %> <html>
<script language="VB" runat="server">
Sub Grid1_Update(sender As Object, e As DataGridCommandEventArgs) Dim sql As string
Dim code_display As String
Dim code_category As String
Dim type As String
Dim opening As String
Dim closing As String
Dim myTextBox As TextBox
'This is the key value : Retrieved from the DataKey, since it's a read only field Dim code_value as string = Grid1.DataKeys.Item(E.Item.ItemIndex).ToString myTextBox = E.Item.FindControl("edit_name")
code_display = mytextbox.text
myTextBox = E.Item.FindControl("edit_group") code_category = mytextbox.text
myTextBox = E.Item.FindControl("edit_type") type = mytextbox.text
myTextBox = E.Item.FindControl("edit_opening") opening = mytextbox.text
myTextBox = E.Item.FindControl("edit_closing") closing = mytextbox.text
'Now execute stored procedure
sql = "Execute p_masters " + code_value + ", '" + code_display + "'," sql = sql + code_category + ", '" + type +"' ," + opening + "," + closing RunSql(sql)
End Sub
Sub add_click(Source As Object, E As EventArgs)
Dim sql As string
If acode_display.text = "" or acode_category.SelectedItem.Text = "" then response.write("Incomplete information")
exit sub end if
SQL = "Execute p_masters @code_value=NULL,"
SQL = SQL + " @code_display = '" + acode_display.text + "' , @group_name = '" SQL = SQL + acode_category.SelectedItem.Text + "' , @type = NULL ,"
SQL = SQL + " @opening =" + aopening.text + ", @closing = 0"
RunSql(sql)
'reset values acode_display.text = "" aopening.text = "" hidePanel()
End Sub
Sub add_show(Source As Object, E As EventArgs)
AddPanel.visible = true
End Sub
Sub Grid1_delete(sender As Object, e As DataGridCommandEventArgs)
Dim code_value as string = Grid1.DataKeys.Item(E.Item.ItemIndex).ToString Dim sql As string
sql = "Delete from masters where code_value = " + cstr(code_value) RunSql(sql)
End Sub </script>
<body style="font: 10pt verdana"> <form runat="server">
<asp:ValidationSummary runat=server headertext="There were errors on the page:" />
<asp:HyperLink runat="server" Text="Trial Balance" NavigateUrl="TrialBalance.aspx"> </asp:HyperLink> 
<asp:HyperLink runat="server" Text="Transactions" NavigateUrl="selection.aspx"> </asp:HyperLink> 
<asp:HyperLink runat="server" Text="Home" NavigateUrl="default.aspx"> </asp:HyperLink>
<h3><font face="Verdana">Chart of Accounts </font></h3> <asp:Label id="Message" runat="server"/>
<asp:Button id="Addshow" text="Add Account" onclick="add_show" runat="server" /> <table width="95%">
<tr>
<td valign="top">
<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" OnEditCommand="Grid1_Edit" OnCancelCommand="Grid1_Cancel" OnUpdateCommand="Grid1_Update" OnDeleteCommand = "Grid1_delete" DataKeyField="code_value"> <Columns> <asp:EditCommandColumn
EditText="Edit"
CancelText="Cancel"
UpdateText="OK"
ItemStyle-Wrap="false"
HeaderText="Edit"
HeaderStyle-Wrap="false"/>
<asp:ButtonColumn Text="Delete" CommandName="Delete" HeaderText="Delete"/>
<asp:BoundColumn HeaderText="Account #" ReadOnly="true" DataField="code_value"/>
<asp:TemplateColumn HeaderText="Name" > <ItemTemplate>
<asp:Label Text='<%# Container.DataItem("code_display") %>' runat="server"/>
</ItemTemplate>
<EditItemTemplate> <asp:RequiredFieldValidator runat=server
controltovalidate=edit_Name errormessage="Name is required.">*
</asp:RequiredFieldValidator> <asp:TextBox id="edit_name"
Text='<%# Container.DataItem("code_display") %>' runat="server"/>
</EditItemTemplate>
</asp:TemplateColumn> <asp:TemplateColumn HeaderText="Group" >
<ItemTemplate>
<asp:Label Text='<%# Container.DataItem("category") %>' runat="server" />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="edit_group" BorderStyle="None"
Readonly="True" 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>
</td>
<td valign="top">
<!---- insert row logic-------->
<asp:Panel id="AddPanel" runat="server" Visible="false"> <table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana"> Add a New Account:</td>
</tr>
<tr>
<td nowrap>Name: </td>
<td><asp:TextBox id="acode_display" runat="server" /></td> <td> <asp:RequiredFieldValidator runat=server
controltovalidate=acode_display errormessage="Name is required.">*
</asp:RequiredFieldValidator></td>
</tr>
<tr>
<td nowrap>Group: </td>
<td><asp:DropDownList DataTextField = "code_display" DataValueField = "code_value" id="acode_category"
runat="server" /></td> </tr>
<tr>
<td nowrap>Opening Value: </td>
<td><asp:TextBox id="aopening" value = "0" runat="server" /></td> </tr>
<tr>
<td style="padding-top:15">
<asp:Button id="SubmitDetailsBtn" text="Submit" onclick="add_Click" runat="server" />
</td>
</tr>
</table>
</asp:Panel>
<!------Insert Logic ends --------> </td>
</tr>
</table>
</form>
</body>
</html>
Masters3.vb is the Code Behind file and is as follows:
Masters3.vb (Code Behind)
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
Protected acode_category as dropdownlist
Protected AddPanel as Panel
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); " Connstr = Connstr + " Initial Catalog=ASPNET;User ID=sa;" myConnection = New OleDbConnection(ConnStr)
if NOT (isPostBack) rebind
end if End Sub
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")
'Binding a Grid
Grid1.DataSource=ds.Tables("masters").DefaultView
Grid1.DataBind()
SQL = "Select * from groups order by code_display" myCommand = New OleDbDataAdapter(SQL, myConnection) myCommand.Fill(ds, "groups")
'populate drop down list acode_category.DataSource=ds.Tables("groups").DefaultView acode_category.DataBind()
hidePanel() End Sub
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 hidePanel()
If AddPanel.visible = true then
AddPanel.visible = false
end if End Sub