FrmEmployee人员管理

Imports System.Data
Imports System.Data.SqlClient
Public Class FrmEmployee
    Dim allow As Integer
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim conn As New SqlConnection
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
            Dim sql As String
            sql = "select employeesid as 员工ID,turename as 姓名,password as 密码,qxbs as 权限,address as 地址,phone as 电话 ," _
                & " age as 年龄,sex as 性别 " _
                & "from employees  order by employeesid"
            Dim adp As New SqlDataAdapter(sql, conn)
            Dim ds As New DataSet
            adp.Fill(ds, "a")
            Me.DataGrid1.DataSource = ds.Tables("a").DefaultView
        Catch ex As Exception
            ' MsgBox(ex.ToString)
            Exit Sub
        End Try
    End Sub
    Private Sub ButAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButAdd.Click
        Try
            Dim str As Integer
            If Me.TxtID.Text = "" Or Me.TxtName.Text = "" Or Me.TxtPassword.Text = "" Or Me.ComQX.Text = "" Then
                MsgBox("不能为空")
            Else
                Dim yn As Integer
                yn = MsgBox("是否保存新增加的记录?", MsgBoxStyle.YesNo, "增加记录")
                If yn = 6 Then
                    Dim conn As New SqlConnection
                    conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
                    Dim ds As New DataSet
                    Dim mytable As New DataTable
                    Dim sql As String
                    If Me.ComQX.Text = "管理" Then
                        str = 1
                    ElseIf Me.ComQX.Text = "员工" Then
                        str = 0
                    Else
                        MsgBox("请选择")
                    End If
                    sql = "insert into employees(employeesid,turename,password,qxbs,address,phone,age,sex)values("
                    sql &= "'" & Me.TxtID.Text & "','" & Me.TxtName.Text & "','" & Me.TxtPassword.Text & "','" & str & "','" & Me.TxtAddress.Text & "'"
                    sql &= ",'" & Me.TxtPhone.Text & "','" & Me.TxtAge.Text & "','" & Me.CSex.Text & "')"

                    Dim adp As New SqlDataAdapter(sql, conn)
                    adp.Fill(ds, "a")

                    Me.DataGrid1.DataSource = ds.Tables("a")
                    Form1_Load(sender, e)
                ElseIf yn = 7 Then
                    Form1_Load(sender, e)
                End If
            End If
        Catch ex As Exception
            ' MsgBox(ex.ToString)
            Exit Sub
        End Try
    End Sub

    Private Sub ButEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButEdit.Click
        Try
            Dim str As Integer
            Dim yn As Integer
            yn = MsgBox("是否修改记录？", MsgBoxStyle.YesNo, "修改记录")
            If yn = 6 Then
                Dim conn As New SqlConnection
                conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"

                Dim myset As New DataSet
                Dim mytable As New DataTable
                Dim sql As String
                If Me.ComQX.Text = "管理" Then
                    str = 1
                ElseIf Me.ComQX.Text = "员工" Then
                    str = 0
                Else
                End If
                sql = "update employees set turename='" & Me.TxtName.Text & "',password='" & Me.TxtPassword.Text & "',qxbs='" & str & "',"
                sql &= "address='" & Me.TxtAddress.Text & "',phone='" & Me.TxtPhone.Text & "',sex='" & Me.CSex.Text & " ',age='" & Me.TxtAge.Text & "'  where employeesid='" & Me.TxtID.Text & "'"
                Dim mydata As New SqlDataAdapter(sql, conn)
                mydata.Fill(myset, "a")
                DataGrid1.DataSource = myset.Tables("a")
                Form1_Load(sender, e)
            Else
                Form1_Load(sender, e)
            End If
        Catch ex As Exception
            'MsgBox(ex.ToString)
            Exit Sub
        End Try
    End Sub

    Private Sub ButDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButDel.Click
        Try
            Dim yn As Integer
            yn = MsgBox("是否删除记录？", MsgBoxStyle.YesNo, "删除记录")
            If yn = 6 Then
                Dim conn As New SqlConnection
                conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"

                Dim myset As New DataSet
                Dim mytable As New DataTable
                Dim sql As String
                sql = "delete from employees where employeesid='" & Me.TxtID.Text & "'"
                Dim mydata As New SqlDataAdapter(sql, conn)
                mydata.Fill(myset, "a")
                MsgBox("删除成功")
                clear()
                Form1_Load(sender, e)
            ElseIf yn = 7 Then
                Form1_Load(sender, e)
            End If
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Private Sub ButExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButExit.Click
        Try
            Dim mytable As New DataTable
            Dim conn As New SqlConnection
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
            Dim sql As String
            If Me.TxtID.Text = "" Then
                clear()
                Form1_Load(sender, e)
                Exit Sub
            Else
                sql = "select employeesid as 员工ID,turename as 姓名,password as 密码,qxbs as 权限,address as 地址,phone as 电话 ," _
                & " age as 年龄,sex as 性别 " _
                & "from employees   where employeesid like" & "'" & Me.TxtID.Text & "'"
                Dim adp As New SqlDataAdapter(sql, conn)
                Dim ds As New DataSet
                adp.Fill(ds, "a")
                Me.DataGrid1.DataSource = ds.Tables("a")
                If allow = 1 Then
                    Me.ComQX.Text = "管理"
                ElseIf allow = 0 Then
                    Me.ComQX.Text = "员工"
                Else
                    Me.ComQX.Text = ""
                End If
            End If
        Catch ex As Exception
            'MsgBox(ex.ToString)
            'yn = MsgBox("没有这条记录，点确定添加", MsgBoxStyle.YesNo)
            'If yn = 6 Then
            'Else
            '    Me.TxtID.Text = ""
            'End If
            Exit Sub
        Finally
        End Try
    End Sub
    Private Sub DataGrid1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.Click
        Try
            TxtID.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 0).ToString
            TxtName.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 1).ToString
            TxtPassword.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 2).ToString
            allow = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 3).ToString
            TxtAddress.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 4) & ""
            TxtPhone.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 5) & ""
            TxtAge.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 6) & ""
            CSex.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 7) & ""
            If allow = 1 Then
                Me.ComQX.Text = "管理"
            Else
                Me.ComQX.Text = "员工"
            End If
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Private Sub clear()
        Me.ComQX.Text = ""
        Me.TxtAddress.Text = ""
        Me.TxtAge.Text = ""
        Me.TxtID.Text = ""
        Me.TxtName.Text = ""
        Me.TxtPassword.Text = ""
        Me.TxtPassword.Text = ""
        Me.CSex.Text = ""
        Me.TxtPhone.Text = ""
    End Sub
End Class