frmshuru.vb

vb.net+SQL开发工具 是一个很好的管理系统的源码

        Dim db As DataBase = New DataBase
        dtDetail = db.RunSelectSQL(strSQL).Table
        Dim dv As DataView = dtDetail.DefaultView
        dv.AllowEdit = False
        dv.AllowNew = False
        dv.AllowDelete = False
        dgdDetail.DataSource = dv
    End Sub
    Public Overrides Sub SaveForUpdate()
        Try
            InsertDetail(Me.txbCode.Text)
            Dim row As Integer
            row = dgdList.CurrentCell.RowNumber
            dgdList.CurrentCell = _
            New DataGridCell((row + 1) Mod bmData.Count, 0)
            '判断是否有更新的数据
            If Me.DataSet11.HasChanges = True Then
                SqlDataAdapter1.Update(DataSet11)
            End If
        Catch ex As Exception
            MessageBox.Show("数据修改失败!")
        End Try
    End Sub
    Public Overrides Sub Clear()
        ' 计算凭证编号(Primary key)
        Dim strID, strID1, strT, strSQL As String
        strID = strID1 = ""
        strSQL = "select max(凭证编号) as 编号 from 凭证表历史"
        Dim db As DataBase = New DataBase
        Dim dv As DataView
        dv = db.RunSelectSQL(strSQL)
        If dv.Item(0)(0).GetType().ToString() = "System.DBNull" Then
            strID = "000001"
        Else
            strT = "000000"
            strID = (CInt(dv(0)(0)) + 1).ToString
            strID = strT.Substring(0, strT.Length - strID.Length) + strID
        End If
        strSQL = "select max(凭证编号) as 编号 from 凭证表"
        dv = db.RunSelectSQL(strSQL)
        If dv.Item(0)(0).GetType().ToString() = "System.DBNull" Then
            strID1 = "000001"
        Else
            strT = "000000"
            strID1 = (CInt(dv(0)(0)) + 1).ToString
            strID1 = strT.Substring(0, strT.Length - strID1.Length) + strID1
        End If
        If strID < strID1 Then
            strID = strID1
        End If

        txbCode.Text = strID
        Dim dvTemp As DataView = db.RunSelectSQL("select 取值 from 系统参数表 where 参数名称 = '当前会计期间'")
        txbPeriod.Text = dvTemp(0)(0).ToString().Trim
        txbState.Text = "未过"
        txbDebitTotal.Text = "0"
        txbLenderTotal.Text = "0"
        txbDate.Text = Now.Date.ToString("yyyy-M-d")
        dtDetail.Rows.Clear()
        ClearTextBox()
        cbbZiHao.Text = ""
        db.Dispose()
    End Sub
    Public Overrides Sub ResetDataGridData()
        If bmData.Position <> -1 Then
            txbCode.Text = dtData.Rows(bmData.Position)("凭证编号")
        End If
    End Sub
    Public Overrides Sub SaveForAdd()
        Dim dr As DataRow = DataSet11.Tables("凭证表").NewRow()
        Try
            dr("凭证编号") = txbCode.Text.Trim
            dr("凭证字号") = cbbZiHao.Text.Trim
            dr("日期") = txbDate.Text.Trim
            dr("会计期间") = txbPeriod.Text.Trim
            dr("过账状态") = txbState.Text.Trim
            dr("借方合计") = txbDebitTotal.Text.Trim
            dr("贷方合计") = txbLenderTotal.Text.Trim
            If txbDebitTotal.Text.Trim <> txbLenderTotal.Text.Trim Then
                MessageBox.Show("借贷双方不平衡,请检查")
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show("数据格式不正确！")
            Exit Sub
        End Try
        Try
            DataSet11.Tables("凭证表").Rows.Add(dr)
            If Me.DataSet11.HasChanges = True Then
                SqlDataAdapter1.Update(DataSet11)
            End If
            InsertDetail(dr("凭证编号").ToString())
        Catch ex As Exception
            MessageBox.Show("数据添加失败!")
        End Try
    End Sub
    Public Overrides Sub DeleteData()
        Try
            '删除当前行的数据
            DeleteDetail()
            Me.dgdDetail.DataSource = Nothing
            DataSet11.Tables("凭证表"). _
            Rows(bmData.Position).Delete()
            If Me.DataSet11.HasChanges = True Then
                '更新DataSet11
                SqlDataAdapter1.Update(DataSet11)
            End If
        Catch ex As Exception
            MessageBox.Show("数据删除失败!")
        End Try

    End Sub
#End Region
#Region "private function"
    Private Sub SetDetailDataGrid(ByVal iCount As Integer)
        LoadDataForDetail()
        '定义一个DataGrid表样式
        Dim ts As New DataGridTableStyle
        Dim aColumnTextColumn As DataGridTextBoxColumn
        Dim numCols As Integer = dtDetail.Columns.Count
        Dim i As Integer
        For i = 2 To numCols - 1

            aColumnTextColumn = New DataGridTextBoxColumn
            aColumnTextColumn.MappingName = _
            dtDetail.Columns(i).ColumnName
            aColumnTextColumn.HeaderText = _
            dtDetail.Columns(i).ColumnName
            aColumnTextColumn.NullText = ""
            ts.GridColumnStyles.Add(aColumnTextColumn)
        Next
        ts.AlternatingBackColor = Color.LightGray
        ts.AllowSorting = False
        ts.MappingName = dtDetail.TableName
        dgdDetail.TableStyles.Add(ts)
        Dim dv As DataView = dtDetail.DefaultView
        dv.AllowEdit = False
        dv.AllowNew = False
        dv.AllowDelete = False
        '设置数据源
        dgdDetail.DataSource = dtDetail
        SetTextBoxData()
    End Sub
    Private Sub dgdDetail_CurrentCellChanged(ByVal sender _
    As Object, ByVal e As EventArgs) _
    Handles dgdDetail.CurrentCellChanged
        If iCurrentLine <> dgdDetail.CurrentCell.RowNumber Then
            iCurrentLine = dgdDetail.CurrentCell.RowNumber
            SetTextBoxData()
        End If
    End Sub
    Private Sub SetTextBoxData()
        Try
            If dtDetail.Rows(iCurrentLine)("科目代码") Is Nothing Then
                Return
            Else
                txbSubjectCode.Text = _
                dtDetail.Rows(iCurrentLine)("科目代码")
                txbSubjectName.Text = _
                dtDetail.Rows(iCurrentLine)("科目名称")
                txbSummary.Text = dtDetail.Rows(iCurrentLine)("摘要")
                txbDebit.Text = dtDetail.Rows(iCurrentLine)("借方")
                txbLender.Text = dtDetail.Rows(iCurrentLine)("贷方")
            End If
        Catch ex As Exception
        End Try
    End Sub
    Private Sub txbSubjectCode_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txbSubjectCode.TextChanged

        Dim strCode As String = txbSubjectCode.Text.Trim
        If strCode = "" Then
            Return
        End If
        Dim strSQL As String = "select 科目名称 from 科目表 where 科目代码=" + strCode
        Dim db As DataBase = New DataBase
        Dim dv As DataView = db.RunSelectSQL(strSQL)
        If dv.Table.Rows.Count = 0 Then
            Me.txbSubjectName.Text = ""
            Return
        End If
        Me.txbSubjectName.Text = dv(0)(0).ToString().Trim()
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If ValidateData() = False Then
            MessageBox.Show("数据格式不确")
            Return
        End If
        Try
            Dim dr As DataRow = dtDetail.NewRow
            dr("摘要") = txbSummary.Text.Trim
            dr("科目代码") = txbSubjectCode.Text.Trim
            dr("科目名称") = txbSubjectName.Text.Trim
            dr("借方") = txbDebit.Text.Trim
            dr("贷方") = txbLender.Text.Trim
            dtDetail.Rows.Add(dr)
            dgdDetail.DataSource = dtDetail
            dgdDetail.CurrentRowIndex = dtDetail.Rows.Count - 1
            UpdateMoney()
        Catch ex As Exception
            MessageBox.Show("数据格式不正确")
        End Try
        ClearTextBox()
    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If ValidateData() = False Then
            MessageBox.Show("数据格式不确")
            Return
        End If
        If dgdDetail.CurrentRowIndex = -1 Then
            Return
        End If
        Try
            Dim iRow As Integer = dgdDetail.CurrentRowIndex
            dtDetail.Rows(iRow)("摘要") = txbSummary.Text.Trim
            dtDetail.Rows(iRow)("科目代码") = txbSubjectCode.Text.Trim
            dtDetail.Rows(iRow)("科目名称") = txbSubjectName.Text.Trim
            dtDetail.Rows(iRow)("借方") = txbDebit.Text.Trim
            dtDetail.Rows(iRow)("贷方") = txbLender.Text.Trim
            UpdateMoney()
        Catch ex As Exception
            MessageBox.Show("数据格式不正确")
        End Try
        ClearTextBox()
    End Sub
    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        Try
            '获得DataGrid当前被选中的行号
            Dim iRow = dgdDetail.CurrentRowIndex
            '弹出对话框让用户确认要删除记录
            '如果用户确认要删除记录
            If MessageBox.Show("你确认要删除此条记录吗？", "Confirm", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                '从商品清单表删除当前被选中记录()
                ClearTextBox()
                dtDetail.Rows.RemoveAt(iRow)
            Else
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show("删除数据出错")
        End Try
        UpdateMoney()
        ClearTextBox()
    End Sub
    Private Function ValidateData() As Boolean
        If txbSubjectCode.Text.Trim = "" Or _
            txbSubjectName.Text.Trim = "" or _
          (txbDebit.Text.Trim = "0" And txbLender.Text.Trim = "0") Then
            Return False
        End If
        Return TRUE
    End Function
    Private Sub ClearTextBox()
        txbSummary.Text = ""
        txbSubjectCode.Text = ""
        txbSubjectName.Text = ""
        txbDebit.Text = "0"
        txbLender.Text = "0"
    End Sub
    Private Function CalMoney() As Single()
        Dim Result(2) As Single
        Result(0) = 0
        Result(1) = 0
        Dim i As Integer
        For i = 0 To dtDetail.Rows.Count - 1
            Result(0) += CSng(dtDetail.Rows(i)("借方"))
            Result(1) += CSng(dtDetail.Rows(i)("贷方"))
        Next
        Return Result

    End Function
    Private Sub UpdateMoney()
        Dim Result() As Single = CalMoney()
        Me.txbDebitTotal.Text = Result(0).ToString("f")
        Me.txbLenderTotal.Text = Result(1).ToString("f")
    End Sub
    Private Sub InsertDetail(ByVal code As String)
        If Me.txbLenderTotal.Text.Trim <> Me.txbDebitTotal.Text.Trim Then
            MessageBox.Show("借贷双方不平衡,请检查")
            Return
        End If
        If dtDetail.Rows.Count <= 0 Then
            Return
        End If
        '首先删除与当前的凭证编号关联的分录表的记录
        Dim db As DataBase = New DataBase
        Dim strSQL As String = "delete from 分录表 where 凭证编号='"
        strSQL += code + "'"
        db.RunDelOrInsSQL(strSQL)
        strSQL = ""
        For Each dr As DataRow In dtDetail.Rows
            strSQL += " insert into 分录表(凭证编号,摘要,科目代码,"
            strSQL += " 借方, 贷方) values('" + code + "','"
            strSQL += dr("摘要").ToString().Trim + "','"
            strSQL += dr("科目代码").ToString().Trim + "',"
            strSQL += dr("借方").ToString().Trim + ","
            strSQL += dr("贷方").ToString().Trim + ")"
        Next
        db.RunDelOrInsSQL(strSQL)
        db.Dispose()
    End Sub
    Private Sub DeleteDetail()
        Dim db As DataBase = New DataBase
        Dim strSQL As String = "delete from 分录表 where 凭证编号='"
        strSQL += txbCode.Text.Trim + "'"
        db.RunDelOrInsSQL(strSQL)
        db.Dispose()
    End Sub
#End Region
End Class