<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
    <script language="VB" runat="server">
	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()
	    
	    sql = "SELECT  code_display, closing, "
	    sql = sql + " dr_amount =  CASE type  WHEN 'A'   THEN "
	    sql = sql + " closing  WHEN 'E'   THEN closing  ELSE  0     END, "
	    sql = sql + "  cr_amount = CASE type  WHEN 'I'  "
	    sql = sql + "  THEN closing   WHEN 'L'   THEN closing  ELSE  0   END "
	    sql = sql + " From Masters"  
	    myCommand = New OleDbDataAdapter(SQL, myConnection)
	    '使用Fill方法
	    myCommand.Fill(ds, "Masters")
	    '绑定DataGrid	
	     Grid1.DataSource=ds.Tables("Masters").DefaultView
	     Grid1.DataBind()
	    '查询所有
	     sql =  "SELECT 'Total' as nothing ,"
	     sql = sql + " (Select sum(closing) From masters "
	     sql = sql + " where type in('A','E')) as dr_total , "
	     sql = sql + " (Select sum(closing) From masters  "
	     sql = sql + " where type in('I','L')) as cr_total "
	     myCommand = New OleDbDataAdapter(SQL, myConnection)
	     myCommand.Fill(ds, "totals")
            '绑定 DataGrid	
             Grid2.DataSource=ds.Tables("totals").DefaultView
             Grid2.DataBind()
    	End Sub
    </script>
<body>
<h3><font face="Verdana">试算平衡 </font></h3>
<form runat=server>
<asp:HyperLink runat="server" Text="Masters" 
  NavigateUrl="masters3.aspx"></asp:HyperLink>&nbsp
<asp:HyperLink runat="server" Text="Transactions" 
  NavigateUrl="selection.aspx"></asp:HyperLink>&nbsp
<asp:HyperLink runat="server" Text="Home" 
  NavigateUrl="default.aspx"></asp:HyperLink>
<br> <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"
   >
  <Columns>
   <asp:BoundColumn HeaderText="Account" 
   DataField="code_display">
     <HeaderStyle Width="150px">
    </HeaderStyle>
   </asp:BoundColumn>
   <asp:BoundColumn HeaderText="Debit Amount" 
   DataField="dr_amount" >
   <HeaderStyle Width="150px">
    </HeaderStyle>
   </asp:BoundColumn>
   <asp:BoundColumn HeaderText="Credit Amount"
   DataField="cr_amount" >
   <HeaderStyle Width="150px">
   </HeaderStyle>
   </asp:BoundColumn>
 </Columns>
 <HeaderStyle BackColor="teal" ForeColor="white" Font-Bold="true">
  </HeaderStyle>
 <ItemStyle ForeColor="DarkSlateBlue">
  </ItemStyle>
 <AlternatingItemStyle BackColor="Beige">
 </AlternatingItemStyle>
</asp:DataGrid>

 <!----所有 //---->
<asp:DataGrid id="Grid2" runat="server"
    AutoGenerateColumns="false"
    BackColor="White"
    BorderWidth="1px" BorderStyle="Solid" BorderColor="Tan"
    CellPadding="2" CellSpacing="0"
    Font-Name="Verdana" Font-Size="8pt"
    >
<Columns>
    <asp:BoundColumn HeaderText="" DataField="nothing">
      <HeaderStyle Width="150px">
      </HeaderStyle>
    </asp:BoundColumn> 
</Columns>
<Columns>
    <asp:BoundColumn HeaderText="" DataField="dr_total">
      <HeaderStyle Width="150px">
      </HeaderStyle>
    </asp:BoundColumn>
    <asp:BoundColumn HeaderText="" DataField="cr_total" >
	  <HeaderStyle Width="150px">
	  </HeaderStyle>
    </asp:BoundColumn>
 </Columns>
  <ItemStyle BackColor="teal" foreColor="white" Font-Bold="true">
  </ItemStyle>
</asp:DataGrid>
</form></body>
</html>
