webform1.aspx.vb

使用工具为.net2003

' ---------------------------------------------------------
' Editable Nested DataGrid
' ---------------------------------------------------------
'=======================================================
' FileName		    : WebForm1.aspx
' Description		: Multi Purpose DataGrid, Which can be expandable to many child grids 
' Date of Creation 	: Dec9, 2006
' Author		    : xiazhuqing
'=======================================================
Imports System.Data.SqlClient
Imports Mvc

Public Class WebForm1
    Inherits System.Web.UI.Page
    '--------------Control Declaration ----------
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents lblShowBal As System.Web.UI.WebControls.Label
    Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
    Protected WithEvents Literal1 As System.Web.UI.WebControls.Literal

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

#Region "Variables"
    Dim dgUniqueID As String
    Dim dgEditItemIndex As Int32
    Dim dgCurrentPageIndex As Int32
    Dim dgSortExpression As String
    Private Enum ExceptionType
        Red = 1
        Green = 2
        Yellow = 3
    End Enum    
#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '//Clear the Message if any
        lblMessage.Text = ""

        If Not Page.IsPostBack Then
            BindData()
            'Reset the session variables
            Session("CustomerID") = ""
            Session("PrevItemIndex") = -1
            dgSortExpression = ""
        End If
    End Sub

    '//This procedure binds the DataGrid with Query Details
    Private Sub BindData(Optional ByVal astrSort As String = "", Optional ByVal astrSort1 As String = "")

        Dim lstrSQL As String
        Dim ldtbParentTable As New DataTable
        Dim ldtbChildTable As New DataTable
        Dim ds As New DataSet
        Dim lobjDataRelation As DataRelation
        Dim lintPageSize As Int32
        Dim llngTotalRec As Int32
        Dim conn As SqlConnection
        Dim dcCust As SqlCommand
        Dim daCust As SqlDataAdapter
        Dim dcOrder As SqlCommand
        Dim daOrder As SqlDataAdapter

        Try
            '//Prepare the connection object
            conn = New SqlConnection("Persist Security Info=False;Data Source=219.219.221.30;Initial Catalog=test;User ID=sa;Password=;")
            conn.open()

            'Query to get Customer Details - Parent Query
            lstrSQL = "SELECT [凭证表].[凭证编号] as CustomerID, [凭证表].[会计期间], " & _
                      "[凭证表].[凭证字号],[凭证表].[日期], " & _
                      "[凭证表].[附单据], [凭证表].[制单], " & _
                      "[凭证表].[借方合计], [凭证表].[贷方合计], " & _
                      "[凭证表].[过帐状态], [凭证表].[凭证状态] FROM [凭证表] "

            '//Sort order provided
            If astrSort <> "" Then
                lstrSQL = lstrSQL & " ORDER BY " & astrSort
            Else
                'Default sort order
                lstrSQL = lstrSQL & " ORDER BY [凭证表].[凭证编号] ASC"
            End If

            dcCust = New SqlCommand(lstrSQL, conn)
            daCust = New SqlDataAdapter(dcCust)
            'daCust = New SqlDataAdapter(lstrSQL, conn)
            daCust.Fill(ldtbParentTable)
            ldtbParentTable.TableName = "ParentTable"

            'Query to get Order Details - Child Query
            'lstrSQL = "SELECT [分录表].[科目代码] as ChildCustomerID, [分录表].[借方], [分录表].[贷方], [分录表].[摘要] FROM [分录表]"
            lstrSQL = "SELECT [分录表].[凭证编号] as ChildCustomerID,[分录表].[科目代码], [科目表].[科目名称], [分录表].[借方], [分录表].[贷方], [分录表].[摘要] FROM [分录表] INNER JOIN [科目表] ON [分录表].[科目代码] = [科目表].[科目代码]"

            '//Sort order provided
            If astrSort1 <> "" Then
                lstrSQL = lstrSQL & " ORDER BY " & astrSort1
            Else
                'Default sort order
                lstrSQL = lstrSQL & " ORDER BY [分录表].[科目代码] ASC"
            End If

            dcOrder = New SqlCommand(lstrSQL, conn)
            daOrder = New SqlDataAdapter(dcOrder)
            daOrder.Fill(ldtbChildTable)
            ldtbChildTable.TableName = "ChildTable"

            'Add both these tables to the dataset
            ds.Tables.Add(ldtbParentTable)
            ds.Tables.Add(ldtbChildTable)

            '//Create relation and this relation name should be used on CreateChildView
            Dim dr As New DataRelation("ParentTable_ChildTable", ldtbParentTable.Columns("CustomerID"), ldtbChildTable.Columns("ChildCustomerID"), False)
            dr.Nested = True
            ds.Relations.Add(dr)

            'Set the datasource to parent datagrid
            DataGrid1.DataSource = ds
            DataGrid1.DataBind()

        Catch exc As Exception
            LogMessage(exc)
            Exit Sub
        Finally
            daCust.Dispose()
            dcCust.Dispose()
            daOrder.Dispose()
            dcOrder.Dispose()
            ds.Dispose()
            conn.close()
        End Try
    End Sub

    '//Log messages
    Sub LogMessage(ByVal exc As Exception, Optional ByVal param As Int32 = ExceptionType.Red)
        If param = ExceptionType.Red Then
            lblMessage.Text = exc.Message.ToString().Substring(0, exc.Message.Length - 1)
            lblMessage.ForeColor = System.Drawing.Color.Red
        ElseIf param = ExceptionType.Green Then
            lblMessage.Text = exc.Message
            lblMessage.ForeColor = System.Drawing.Color.Green
        ElseIf param = ExceptionType.Yellow Then
            lblMessage.Text = exc.Message
            lblMessage.ForeColor = System.Drawing.Color.Yellow
        End If
    End Sub

#Region "DataGrid1 Event handlers"
    '//This procedure handles the Parent Grid command events
    Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand

        Dim strSQL As String

        Select Case e.CommandName

            Case "Expand"               '//Case for expanding the + sign on Parent grid to show the child grid

                Dim img As ImageButton
                Dim exp As PlaceHolder

                'Before expanding, collapse previously opened child grid
                Dim dgItem As DataGridItem
                For Each dgItem In DataGrid1.Items
                    If dgItem.ItemIndex = Session("PrevItemIndex") And dgItem.ItemIndex <> e.Item.ItemIndex Then
                        img = dgItem.Cells(0).FindControl("ImageButton1")
                        If img.ImageUrl = "~/Images/Minus.gif" Then
                            img.ImageUrl = "~/Images/Plus.gif"
                            '科目中的个数
                            exp = dgItem.Cells(4).FindControl("Expanded")
                            exp.Visible = Not exp.Visible
                        End If
                        Exit For
                    End If
                Next

                img = e.Item.Cells(0).FindControl("ImageButton1")
                If img.ImageUrl = "~/Images/Plus.gif" Then
                    img.ImageUrl = "~/Images/Minus.gif"
                Else
                    img.ImageUrl = "~/Images/Plus.gif"
                End If

                exp = e.Item.Cells(2).FindControl("Expanded")
                exp.Visible = Not exp.Visible
                Session("PrevItemIndex") = e.Item.ItemIndex     'Store the ItemIndex in session variable
                Session("CustomerID") = e.Item.Cells(1).Text    'Store the CustomerID

                'Case "Insert"   '//Case statement for Insert Click on Parent Grid
                '    Dim txtCustomerID As TextBox
                '    Dim txtCompanyName As TextBox
                '    Dim txtContactName As TextBox
                '    Dim txtContactTitle As TextBox
                '    Dim txtAddress As TextBox
                '    Dim lstrCustomerID As String
                '    Dim lstrCompanyName As String
                '    Dim lstrContactName As String
                '    Dim lstrContactTitle As String
                '    Dim lstrRFC As String
                '    Dim lstrAddress As String

                '    'Read in the values of the TextBoxes
                '    txtCustomerID = e.Item.FindControl("add_CustomerID")
                '    lstrCustomerID = txtCustomerID.Text.ToUpper
                '    txtCompanyName = e.Item.FindControl("add_kjqj")
                '    lstrCompanyName = txtCompanyName.Text
                '    txtContactName = e.Item.FindControl("add_pzzh")
                '    lstrContactName = txtContactName.Text
                '    txtContactTitle = e.Item.FindControl("add_rq")
                '    lstrContactTitle = txtContactTitle.Text
                '    txtAddress = e.Item.FindControl("add_fdj")
                '    lstrAddress = txtAddress.Text

                '    Dim conn
                '    Dim dcCust

                '    Try
                '        'Check whether the record exists before adding...
                '        strSQL = "SELECT COUNT(*) FROM [Customers] WHERE [Customers].[CustomerID] = '" & lstrCustomerID & "'"
                '        conn = New SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath(ConfigurationSettings.AppSettings("MDBPATH")))
                '        dcCust = New SqlCommand(strSQL, conn)
                '        conn.Open()
                '        Dim count = dcCust.ExecuteScalar
                '        If (count > 0) Then
                '            Throw New Exception("Record already exists...")
                '        End If
                '        dcCust.Dispose()

                '        'Create the SQL statement for addition
                '        strSQL = "INSERT INTO Customers (CustomerID, CompanyName, ContactName, " & _
                '        "ContactTitle, Address) VALUES ('" & lstrCustomerID & "','" & lstrCompanyName & "','" & _
                '        lstrContactName & "','" & lstrContactTitle & "','" & lstrAddress & "')"

                '        dcCust = New SqlCommand(strSQL, conn)
                '        dcCust.ExecuteNonQuery()
                '        LogMessage(New Exception("Record added successfully..."), ExceptionType.Green)