form5.vb

第五章 家庭财务程序开发

        Me.Label3.Text = "项目名称"
        '
        'Label4
        '
        Me.Label4.Location = New System.Drawing.Point(32, 112)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(64, 16)
        Me.Label4.TabIndex = 27
        Me.Label4.Text = "匹配码"
        '
        'DataGrid2
        '
        Me.DataGrid2.DataMember = ""
        Me.DataGrid2.DataSource = Me.Outgoing1.Outgoing
        Me.DataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid2.Location = New System.Drawing.Point(208, 40)
        Me.DataGrid2.Name = "DataGrid2"
        Me.DataGrid2.ReadOnly = True
        Me.DataGrid2.Size = New System.Drawing.Size(368, 288)
        Me.DataGrid2.TabIndex = 28
        '
        'Outgoing1
        '
        Me.Outgoing1.DataSetName = "outgoing"
        Me.Outgoing1.Locale = New System.Globalization.CultureInfo("zh-CN")
        Me.Outgoing1.Namespace = "http://www.tempuri.org/outgoing.xsd"
        '
        'OleDbConnection1
        '
        Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=finance.mdb;" & _
        "Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLE" & _
        "DB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet O" & _
        "LEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Globa" & _
        "l Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System" & _
        " Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on " & _
        "Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" & _
        ""
        '
        'outgoing
        '
        Me.outgoing.InsertCommand = Me.OleDbInsertCommand1
        Me.outgoing.SelectCommand = Me.OleDbSelectCommand1
        Me.outgoing.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Outgoing", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("项目名称", "项目名称"), New System.Data.Common.DataColumnMapping("匹配码", "匹配码"), New System.Data.Common.DataColumnMapping("类型", "类型"), New System.Data.Common.DataColumnMapping("单位", "单位")})})
        '
        'OleDbInsertCommand1
        '
        Me.OleDbInsertCommand1.CommandText = "INSERT INTO Outgoing(单位, 类型, 匹配码, 项目名称) VALUES (?, ?, ?, ?)"
        Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("单位", System.Data.OleDb.OleDbType.VarWChar, 5, "单位"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("类型", System.Data.OleDb.OleDbType.VarWChar, 10, "类型"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("匹配码", System.Data.OleDb.OleDbType.VarWChar, 10, "匹配码"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("项目名称", System.Data.OleDb.OleDbType.VarWChar, 15, "项目名称"))
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT 单位, 类型, 匹配码, 项目名称 FROM Outgoing"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
        '
        'Form5
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(586, 346)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid2, Me.Label4, Me.Label3, Me.TextBox3, Me.TextBox2, Me.Label2, Me.ComboBox1, Me.Label1, Me.TextBox1, Me.Button5, Me.Button4, Me.Button3, Me.Button2, Me.Button1, Me.TabControl1})
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow
        Me.Name = "Form5"
        Me.Text = "Form5"
        Me.TabControl1.ResumeLayout(False)
        Me.TabPage1.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataGrid2, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.Outgoing1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region


    '响应关闭的事件
    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.Close()
    End Sub

    '进入Form的事件
    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '设置按钮
        dobutton(1, 1, 1, 0)
        '填充DataSet
        outgoing.Fill(Outgoing1)
        '设置Textbox和ComboBox
        TextBox1.Enabled = False
        TextBox2.Enabled = False
        TextBox3.Enabled = False
        ComboBox1.Enabled = False
    End Sub
    '设置dobutton函数
    'a,b,c,d分别代表了添加、删除、修改和取消几个按键的Enable值
    Private Function dobutton(ByVal a, ByVal b, ByVal c, ByVal d)
        '首先将四个按钮均置为False
        Button1.Enabled = False
        Button2.Enabled = False
        Button3.Enabled = False
        Button4.Enabled = False
        '判断a,b,c,d的值，1则为按钮有效，0为按钮无效
        If a = 1 Then Button1.Enabled = True
        If b = 1 Then Button2.Enabled = True
        If c = 1 Then Button3.Enabled = True
        If d = 1 Then Button4.Enabled = True

    End Function
    'TabControl选择变化时候的事件
    Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
        '定义con是TabControl选择的内容
        Dim con As Integer = TabControl1.SelectedIndex
        '生成选择的SQL语句
        nsql = "Select * from outgoing where 类型=" + Chr(39) + TabControl1.SelectedTab.Text + Chr(39)
        '生成选择“全部”的SQL语句
        If TabControl1.SelectedTab.Text = "全部" Then nsql = "Select * from outgoing"
        '调用显示函数
        doshow(nsql)
    End Sub
    '生成显示函数
    Private Function doshow(ByVal nsql)
        '定义连接的字符串，从窗体设计器生成的代码Region中拷贝过来
        Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=finance.mdb;" & _
                "Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLE" & _
                "DB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet O" & _
                "LEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Globa" & _
                "l Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System" & _
                " Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on " & _
                "Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" & _
                ""
        '利用SQL语句进行查询
        Me.outgoing.SelectCommand = Me.OleDbSelectCommand1
        OleDbSelectCommand1.CommandText = nsql
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
        '更新Outgoing1 DataSet数据集
        Outgoing1.Clear()
        outgoing.Fill(Outgoing1)
    End Function
    '当DataGrid当前单元格变化的时候响应事件
    Private Sub DataGrid2_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid2.CurrentCellChanged
        '设置4个功能按键
        dobutton(1, 1, 1, 0)
        '设置各个TextBox和ComboBox的内容
        TextBox3.Text = DataGrid2.Item(DataGrid2.CurrentCell.RowNumber, 0)
        ComboBox1.Text = DataGrid2.Item(DataGrid2.CurrentCell.RowNumber, 1)
        TextBox2.Text = DataGrid2.Item(DataGrid2.CurrentCell.RowNumber, 2)
        TextBox1.Text = DataGrid2.Item(DataGrid2.CurrentCell.RowNumber, 3)
        '设置Textbox和ComboBox
        TextBox1.Enabled = False
        TextBox2.Enabled = False
        TextBox3.Enabled = False
        ComboBox1.Enabled = False
    End Sub
    '响应添加的事件
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '状态加1,如果n=1，则表示现在是输入状态
        n = n + 1
        If n = 1 Then
            '打开Textbox和ComboBox接受用户数据
            TextBox1.Enabled = True
            TextBox2.Enabled = True
            TextBox3.Enabled = True
            ComboBox1.Enabled = True
            '清空Textbox和ComboBox中间的内容
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
            ComboBox1.Text = ""
            '设置按键
            dobutton(1, 0, 0, 1)
        End If
        '如果n=2，则表示现在是更新数据库的操作
        If n = 2 And TextBox1.Text <> "" And TextBox2.Text <> "" And ComboBox1.Text <> "" Then   '对输入的数据进行判断和校验
            '生成SQL语句
            nsql = "INSERT INTO outgoing VALUES(" + Chr(39) + CStr(TextBox1.Text) + Chr(39) + "," + Chr(39) + CStr(TextBox2.Text) + Chr(39) + "," + Chr(39) + CStr(ComboBox1.Text) + Chr(39) + "," + Chr(39) + CStr(TextBox3.Text) + Chr(39) + ")"
            '打开数据库
            OleDbConnection1.Open()
            '指定SQL语句对应的连接
            ncommand.Connection = OleDbConnection1
            '指定SQL语句
            ncommand.CommandText() = nsql
            '执行SQL语句，更新数据库
            ncommand.ExecuteNonQuery()
            '更新Outgoing1 DataSet数据集
            Outgoing1.Clear()
            outgoing.Fill(Outgoing1)
            '关闭数据库连接
            '设置Textbox,ComboBox和按钮
            OleDbConnection1.Close()
            TextBox1.Enabled = False
            TextBox2.Enabled = False
            TextBox3.Enabled = False
            ComboBox1.Enabled = False
            dobutton(1, 1, 1, 0)
        End If
        '如果n=2表示状态标志结束，置0
        If n = 2 Then n = 0
    End Sub
    '响应删除的事件
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '生成SQL语句
        nsql = "DELETE FROM outgoing WHERE 项目名称=" + Chr(39) + CStr(TextBox1.Text) + Chr(39) + " and 匹配码=" + Chr(39) + CStr(TextBox2.Text) + Chr(39) + " and 类型=" + Chr(39) + CStr(ComboBox1.Text) + Chr(39) + " and 单位=" + Chr(39) + CStr(TextBox3.Text) + Chr(39)
        '打开数据库
        OleDbConnection1.Open()
        '指定SQL语句对应的连接
        ncommand.Connection = OleDbConnection1
        '指定SQL语句
        ncommand.CommandText() = nsql
        '执行SQL语句，更新数据库
        ncommand.ExecuteNonQuery()
        '更新Outgoing1 DataSet数据集
        Outgoing1.Clear()
        outgoing.Fill(Outgoing1)
        '关闭数据库连接
        OleDbConnection1.Close()
    End Sub
    '响应取消的事件
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '设置按钮，恢复原来设定
        dobutton(1, 1, 1, 0)
        '置状态为0
        n = 0
        '设置Textbox,ComboBox
        TextBox1.Enabled = False
        TextBox2.Enabled = False
        TextBox3.Enabled = False
        ComboBox1.Enabled = False
    End Sub
    '响应更新的事件
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        '状态加1,如果n=1，则表示现在是输入状态
        n = n + 1
        If n = 1 Then
            '备份原来数据库中的数据
            bak1 = TextBox1.Text
            bak2 = TextBox2.Text
            bak3 = TextBox3.Text
            bak4 = ComboBox1.Text
            '设置Textbox,ComboBox和按钮
            TextBox1.Enabled = True
            TextBox2.Enabled = True
            TextBox3.Enabled = True
            ComboBox1.Enabled = True
            dobutton(0, 0, 1, 1)
        End If
        '如果n=2，则表示现在是更新数据库的操作
        If n = 2 And TextBox1.Text <> "" And TextBox2.Text <> "" And ComboBox1.Text <> "" Then
            '生成SQL语句
            nsql = "UPDATE outgoing SET 项目名称=" + Chr(39) + CStr(TextBox1.Text) + Chr(39) + " , 匹配码=" + Chr(39) + CStr(TextBox2.Text) + Chr(39) + " , 类型=" + Chr(39) + CStr(ComboBox1.Text) + Chr(39) + " , 单位=" + Chr(39) + CStr(TextBox3.Text) + Chr(39) + " WHERE 项目名称=" + Chr(39) + bak1 + Chr(39) + " and 匹配码=" + Chr(39) + bak2 + Chr(39) + " and 类型=" + Chr(39) + bak4 + Chr(39) + " and 单位=" + Chr(39) + bak3 + Chr(39)
            '打开数据库
            OleDbConnection1.Open()
            '指定SQL语句对应的连接，指定SQL语句
            ncommand.Connection = OleDbConnection1
            ncommand.CommandText() = nsql
            '执行SQL语句，更新数据库
            ncommand.ExecuteNonQuery()
            '更新Outgoing1 DataSet数据集
            Outgoing1.Clear()
            outgoing.Fill(Outgoing1)
            '关闭数据库连接
            OleDbConnection1.Close()
            '设置Textbox,ComboBox和按钮
            TextBox1.Enabled = False
            TextBox2.Enabled = False
            TextBox3.Enabled = False
            ComboBox1.Enabled = False
            dobutton(1, 1, 1, 0)
        End If
        '如果n=2表示状态标志结束，置0
        If n = 2 Then n = 0
    End Sub
End Class