frmzongfen.vb

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

        Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM 本期汇总账簿 WHERE (科目代码 = @Original_科目代码) AND (会计期间 = @Original_会计期间 OR @O" & _
        "riginal_会计期间 IS NULL AND 会计期间 IS NULL) AND (余额 = @Original_余额 OR @Original_余额 IS" & _
        " NULL AND 余额 IS NULL) AND (余额方向 = @Original_余额方向 OR @Original_余额方向 IS NULL AND 余" & _
        "额方向 IS NULL) AND (期初余额 = @Original_期初余额 OR @Original_期初余额 IS NULL AND 期初余额 IS NU" & _
        "LL) AND (本年借方累计 = @Original_本年借方累计 OR @Original_本年借方累计 IS NULL AND 本年借方累计 IS NUL" & _
        "L) AND (本年贷方累计 = @Original_本年贷方累计 OR @Original_本年贷方累计 IS NULL AND 本年贷方累计 IS NULL" & _
        ") AND (本期借方合计 = @Original_本期借方合计 OR @Original_本期借方合计 IS NULL AND 本期借方合计 IS NULL)" & _
        " AND (本期贷方合计 = @Original_本期贷方合计 OR @Original_本期贷方合计 IS NULL AND 本期贷方合计 IS NULL)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_科目代码", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "科目代码", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_会计期间", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "会计期间", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_余额", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "余额", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_余额方向", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "余额方向", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_期初余额", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "期初余额", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本年借方累计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本年借方累计", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本年贷方累计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本年贷方累计", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本期借方合计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本期借方合计", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本期贷方合计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本期贷方合计", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=CS;packet size=4096;integrated security=SSPI;data source=CS;persis" & _
        "t security info=False;initial catalog=caiwubook"
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO 本期汇总账簿(科目代码, 会计期间, 期初余额, 本期借方合计, 本期贷方合计, 本年借方累计, 本年贷方累计, 余额, 余额方向) VA" & _
        "LUES (@科目代码, @会计期间, @期初余额, @本期借方合计, @本期贷方合计, @本年借方累计, @本年贷方累计, @余额, @余额方向); SELE" & _
        "CT 科目代码, 会计期间, 期初余额, 本期借方合计, 本期贷方合计, 本年借方累计, 本年贷方累计, 余额, 余额方向 FROM 本期汇总账簿 WHERE " & _
        "(科目代码 = @科目代码)"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@科目代码", System.Data.SqlDbType.VarChar, 20, "科目代码"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@会计期间", System.Data.SqlDbType.Int, 4, "会计期间"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@期初余额", System.Data.SqlDbType.Money, 8, "期初余额"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本期借方合计", System.Data.SqlDbType.Money, 8, "本期借方合计"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本期贷方合计", System.Data.SqlDbType.Money, 8, "本期贷方合计"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本年借方累计", System.Data.SqlDbType.Money, 8, "本年借方累计"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本年贷方累计", System.Data.SqlDbType.Money, 8, "本年贷方累计"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@余额", System.Data.SqlDbType.Money, 8, "余额"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@余额方向", System.Data.SqlDbType.VarChar, 4, "余额方向"))
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT 科目代码, 会计期间, 期初余额, 本期借方合计, 本期贷方合计, 本年借方累计, 本年贷方累计, 余额, 余额方向 FROM 本期汇总账簿"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE 本期汇总账簿 SET 科目代码 = @科目代码, 会计期间 = @会计期间, 期初余额 = @期初余额, 本期借方合计 = @本期借方合计, 本期贷" & _
        "方合计 = @本期贷方合计, 本年借方累计 = @本年借方累计, 本年贷方累计 = @本年贷方累计, 余额 = @余额, 余额方向 = @余额方向 WHERE " & _
        "(科目代码 = @Original_科目代码) AND (会计期间 = @Original_会计期间 OR @Original_会计期间 IS NULL AND" & _
        " 会计期间 IS NULL) AND (余额 = @Original_余额 OR @Original_余额 IS NULL AND 余额 IS NULL) AN" & _
        "D (余额方向 = @Original_余额方向 OR @Original_余额方向 IS NULL AND 余额方向 IS NULL) AND (期初余额 =" & _
        " @Original_期初余额 OR @Original_期初余额 IS NULL AND 期初余额 IS NULL) AND (本年借方累计 = @Origi" & _
        "nal_本年借方累计 OR @Original_本年借方累计 IS NULL AND 本年借方累计 IS NULL) AND (本年贷方累计 = @Origin" & _
        "al_本年贷方累计 OR @Original_本年贷方累计 IS NULL AND 本年贷方累计 IS NULL) AND (本期借方合计 = @Origina" & _
        "l_本期借方合计 OR @Original_本期借方合计 IS NULL AND 本期借方合计 IS NULL) AND (本期贷方合计 = @Original" & _
        "_本期贷方合计 OR @Original_本期贷方合计 IS NULL AND 本期贷方合计 IS NULL); SELECT 科目代码, 会计期间, 期初余额" & _
        ", 本期借方合计, 本期贷方合计, 本年借方累计, 本年贷方累计, 余额, 余额方向 FROM 本期汇总账簿 WHERE (科目代码 = @科目代码)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@科目代码", System.Data.SqlDbType.VarChar, 20, "科目代码"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@会计期间", System.Data.SqlDbType.Int, 4, "会计期间"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@期初余额", System.Data.SqlDbType.Money, 8, "期初余额"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本期借方合计", System.Data.SqlDbType.Money, 8, "本期借方合计"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本期贷方合计", System.Data.SqlDbType.Money, 8, "本期贷方合计"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本年借方累计", System.Data.SqlDbType.Money, 8, "本年借方累计"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@本年贷方累计", System.Data.SqlDbType.Money, 8, "本年贷方累计"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@余额", System.Data.SqlDbType.Money, 8, "余额"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@余额方向", System.Data.SqlDbType.VarChar, 4, "余额方向"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_科目代码", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "科目代码", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_会计期间", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "会计期间", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_余额", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "余额", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_余额方向", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "余额方向", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_期初余额", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "期初余额", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本年借方累计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本年借方累计", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本年贷方累计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本年贷方累计", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本期借方合计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本期借方合计", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_本期贷方合计", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "本期贷方合计", System.Data.DataRowVersion.Original, Nothing))
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("zh-CN")
        '
        'frmZongfen
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(520, 273)
        Me.Controls.Add(Me.grbSearch)
        Me.Controls.Add(Me.ToolBar1)
        Me.Controls.Add(Me.dgdList)
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.Name = "frmZongfen"
        Me.Text = "总分类账查询"
        Me.grbSearch.ResumeLayout(False)
        CType(Me.dgdList, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
        Select Case e.Button.Text
            Case "首记录"
                '返回首记录
                bmData.Position = 0
            Case "上记录"
                If bmData.Position <> 0 Then
                    bmData.Position -= 1
                End If
            Case "下记录"
                If bmData.Position <> bmData.Count Then
                    bmData.Position += 1
                End If
            Case "尾记录"
                '返回尾记录
                bmData.Position = bmData.Count
            Case "退出"
                '关闭本窗体
                Me.Close()
        End Select
        Try
            Me.dgdList.CurrentRowIndex = bmData.Position
        Catch ex As Exception
        End Try
    End Sub
    Private Sub dgdList_CurrentCellChanged(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles dgdList.CurrentCellChanged
        '判断bmData是否为Nothing
        If bmData Is Nothing Then
            Exit Sub
        End If
        If bmData.Position <> dgdList.CurrentCell.RowNumber Then
            bmData.Position = dgdList.CurrentCell.RowNumber
        End If
    End Sub

    Private Sub frmZongfen_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.SetDataGrid()
    End Sub


    Private Sub SetDataGrid()
        LoadData()
        '定义一个DataGrid表样式
        Dim ts As New DataGridTableStyle
        Dim aColumnTextColumn As DataGridTextBoxColumn
        Dim numCols As Integer = dtData.Columns.Count
        Dim i As Integer
        For i = 0 To numCols - 1

            aColumnTextColumn = New DataGridTextBoxColumn
            aColumnTextColumn.MappingName = _
            dtData.Columns(i).ColumnName
            aColumnTextColumn.HeaderText = _
            dtData.Columns(i).ColumnName
            aColumnTextColumn.NullText = ""
            aColumnTextColumn.ReadOnly = True
            ts.GridColumnStyles.Add(aColumnTextColumn)
        Next
        ts.AlternatingBackColor = Color.LightGray
        ts.AllowSorting = False
        ts.MappingName = dtData.TableName
        dgdList.TableStyles.Add(ts)
        Dim dv As DataView = dtData.DefaultView
        dv.AllowNew = False
        dv.AllowDelete = False
        'dv.AllowEdit = False
        '设置数据源
        dgdList.DataSource = dtData.DefaultView
        bmData = Me.BindingContext(DataSet11, "本期汇总账簿")

    End Sub
    Private Sub LoadData()
        Dim strFilter As String
        If Me.txbOne.Text.Trim.Length = 0 Then
            If Me.cbbCheck.Checked = True Then
                strFilter = "where 本期借方合计 <>0 or 本期贷方合计<>0"
            End If
        Else
            strFilter = "where 科目代码=" + Me.txbOne.Text.Trim
            If Me.cbbCheck.Checked = True Then
                strFilter += " and (本期借方合计 <>0 or 本期贷方合计<>0)"
            End If
        End If
        '清空DataSet11
        DataSet11.Clear()
        SqlConnection1.ConnectionString = DataBase.sConn
        SqlDataAdapter1.SelectCommand.CommandText _
        = "select * from 本期汇总账簿  " + strFilter
        '根据查询条件重新填充DataSet11
        SqlDataAdapter1.Fill(DataSet11)
        dtData = DataSet11.Tables("本期汇总账簿")
        bmData = Me.BindingContext(DataSet11, "本期汇总账簿")
        lblCount.Text = "记录数:" + dtData.Rows.Count.ToString()
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        LoadData()
        dgdList.DataSource = dtData.DefaultView
    End Sub

    Private Sub cbbCheck_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbbCheck.CheckedChanged
        LoadData()
        dgdList.DataSource = dtData.DefaultView
    End Sub
End Class