frmpeoplemanage.vb

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

        Me.SqlSelectCommand1.CommandText = "SELECT 内部编号, 职员编号, 姓名, 姓名简码, 性别, 出生日期, 年龄, 籍贯, 民族, 文化程度, 毕业学校, 健康状况, 婚姻状况, 身份证号码," & _
        " 家庭电话, 办公电话, 手机, 电子邮件地址, 职工账号, 单位编号, 备注 FROM 职员基本信息表"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE 职员基本信息表 SET 职员编号 = @职员编号, 姓名 = @姓名, 姓名简码 = @姓名简码, 性别 = @性别, 出生日期 = @出生日期, " & _
        "年龄 = @年龄, 籍贯 = @籍贯, 民族 = @民族, 文化程度 = @文化程度, 毕业学校 = @毕业学校, 健康状况 = @健康状况, 婚姻状况 = @" & _
        "婚姻状况, 身份证号码 = @身份证号码, 家庭电话 = @家庭电话, 办公电话 = @办公电话, 手机 = @手机, 电子邮件地址 = @电子邮件地址, 职工" & _
        "账号 = @职工账号, 单位编号 = @单位编号, 备注 = @备注 WHERE (职员编号 = @Original_职员编号) AND (健康状况 = @Or" & _
        "iginal_健康状况 OR @Original_健康状况 IS NULL AND 健康状况 IS NULL) AND (出生日期 = @Original_出生" & _
        "日期 OR @Original_出生日期 IS NULL AND 出生日期 IS NULL) AND (办公电话 = @Original_办公电话 OR @Or" & _
        "iginal_办公电话 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_姓名) 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) 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) AND (文化程度 = @Original_文化程度 OR @Original_文化" & _
        "程度 IS NULL AND 文化程度 IS NULL) AND (毕业学校 = @Original_毕业学校 OR @Original_毕业学校 IS NUL" & _
        "L AND 毕业学校 IS NULL) 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 (职工账号 = @Or" & _
        "iginal_职工账号 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, 30, "职员编号"))
        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.VarChar, 10, "姓名简码"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@性别", System.Data.SqlDbType.VarChar, 2, "性别"))
        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.Int, 4, "年龄"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@籍贯", System.Data.SqlDbType.VarChar, 50, "籍贯"))
        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.VarChar, 50, "文化程度"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@毕业学校", System.Data.SqlDbType.VarChar, 100, "毕业学校"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@健康状况", System.Data.SqlDbType.VarChar, 50, "健康状况"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@婚姻状况", System.Data.SqlDbType.VarChar, 10, "婚姻状况"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@身份证号码", System.Data.SqlDbType.VarChar, 18, "身份证号码"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@家庭电话", System.Data.SqlDbType.VarChar, 50, "家庭电话"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@办公电话", System.Data.SqlDbType.VarChar, 50, "办公电话"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@手机", System.Data.SqlDbType.VarChar, 50, "手机"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@电子邮件地址", System.Data.SqlDbType.VarChar, 50, "电子邮件地址"))
        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.VarChar, 20, "单位编号"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@备注", System.Data.SqlDbType.VarChar, 100, "备注"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_职员编号", System.Data.SqlDbType.VarChar, 30, 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, 50, 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.VarChar, 50, 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, 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.VarChar, 100, 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, 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.VarChar, 10, 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, 10, 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, 50, 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.VarChar, 2, 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, 50, 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, 50, 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, 100, 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, 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.VarChar, 50, 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, 50, 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, 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.VarChar, 18, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "身份证号码", System.Data.DataRowVersion.Original, Nothing))
        '
        'frmPeopleManage
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(608, 325)
        Me.Controls.Add(Me.dgdPeopleInfo)
        Me.Controls.Add(Me.grbPeople)
        Me.Controls.Add(Me.ToolBar1)
        Me.Name = "frmPeopleManage"
        Me.Text = "职员信息维护"
        Me.grbPeople.ResumeLayout(False)
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.dgdPeopleInfo, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub SetDataGrid()
        SqlConnection1.ConnectionString = DataBase.sConn
        SqlDataAdapter1.Fill(DataSet11)
        Dim dt As DataTable
        dt = DataSet11.Tables("职员基本信息表")
        '定义一个DataGrid表样式
        Dim ts As New DataGridTableStyle
        Dim aColumnTextColumn As DataGridTextBoxColumn
        Dim numCols As Integer = dt.Columns.Count
        Dim i As Integer
        For i = 1 To numCols - 1
            aColumnTextColumn = New DataGridTextBoxColumn
            aColumnTextColumn.MappingName = _
            dt.Columns(i).ColumnName()
            aColumnTextColumn.HeaderText = _
            dt.Columns(i).ColumnName()
            aColumnTextColumn.NullText = ""
            If i = 4 Or i = 6 Then
                aColumnTextColumn.Width = 40
            End If
            ts.GridColumnStyles.Add(aColumnTextColumn)
        Next
        ts.AlternatingBackColor = Color.LightGray
        ts.AllowSorting = False
        ts.MappingName = dt.TableName
        dgdPeopleInfo.TableStyles.Add(ts)
        Dim dv As DataView = dt.DefaultView
        'dv.AllowNew = False
        dgdPeopleInfo.DataSource = dt.DefaultView
    End Sub
    Private Sub frmPeopleManage_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
        Dim db As DataBase = New DataBase
        '填充cbbDepCode
        Dim strSQL As String = "select 单位编号 from"
        strSQL += " 组织机构编码表 order by 单位编号"
        Dim dv As DataView = db.RunSelectSQL(strSQL)
        For Each drv As DataRowView In dv
            Me.cbbDepCode.Items.Add(drv("单位编号"))
        Next
        db.Dispose()
        SetDataGrid()
    End Sub

    Private Sub dgdPeopleInfo_CurrentCellChanged( _
        ByVal sender As Object, ByVal e As EventArgs) _
        Handles dgdPeopleInfo.CurrentCellChanged
        BindingContext(DataSet11, "职员基本信息表").ResumeBinding()
        '判断当前行是否改变
        If iCurrentLine = -1 Or iCurrentLine _
        <> dgdPeopleInfo.CurrentCell.RowNumber Then
            '记住当前行的行号
            iCurrentLine = dgdPeopleInfo.CurrentCell.RowNumber
            '根据dgdPeopleInfo前行的行号设置在TextBox显示的数据行
            BindingContext(DataSet11, "职员基本信息表").Position = _
            dgdPeopleInfo.CurrentCell.RowNumber
        End If
        If dgdPeopleInfo.CurrentCell.RowNumber = _
        DataSet11.Tables("职员基本信息表").Rows.Count Then
            BindingContext(DataSet11, "职员基本信息表").SuspendBinding()
        End If
    End Sub

    Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, _
        ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) _
        Handles ToolBar1.ButtonClick
        If e.Button Is tbbSave Then
            Dim row As Integer
            row = dgdPeopleInfo.CurrentCell.RowNumber
            dgdPeopleInfo.CurrentCell = New DataGridCell(row + 1, 0)
            Try
                If DataSet11.HasChanges Then
                    '通过SqlDataAdapter1更新数据库数据
                    SqlDataAdapter1.Update(DataSet11)
                    DataSet11.Tables("职员基本信息表"). _
                    DefaultView.RowFilter = ""
                    dgdPeopleInfo.CurrentCell = New DataGridCell(row, 0)
                    MessageBox.Show("数据修改成功!")
                Else
                    dgdPeopleInfo.CurrentCell = New DataGridCell(row, 0)
                    MessageBox.Show("无修改的数据!")
                End If
            Catch ex As Exception
                MessageBox.Show("修改的数据出错")
            End Try
        ElseIf e.Button Is tbbDel Then
            Try
                '获得DataGrid当前被选中的行号
                Dim iRow = dgdPeopleInfo.CurrentRowIndex

                If MessageBox.Show("你确认要删除此条记录吗？", "Confirm", _
                MessageBoxButtons.YesNo) = DialogResult.Yes Then
                    '从职员基本信息表表删除当前被选中记录()
                    DataSet11.Tables("职员基本信息表"). _
                    Rows(iRow).Delete()
                Else
                    Exit Sub
                End If
                Try
                    If DataSet11.HasChanges Then
                        '用SqlDataAdapter1更新数据库
                        SqlDataAdapter1.Update(DataSet11)
                        MessageBox.Show("数据删除成功!")
                    Else
                        MessageBox.Show("无修改的数据!")
                    End If
                Catch ex As Exception
                    MessageBox.Show("删除数据出错")
                    'MessageBox.Show(ex.ToString)
                End Try
            Catch ex As Exception
                MessageBox.Show("删除数据出错")
            End Try
        End If
    End Sub

    Private Sub cbbDepCode_TextChanged(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles cbbDepCode.TextChanged
        Dim db As DataBase = New DataBase
        '根据cbbDepCode.Text查询单位名称
        Dim strSQL As String = "select 单位名称 from"
        strSQL += " 组织机构编码表 where 单位编号 = '"
        strSQL += Me.cbbDepCode.Text + "'"
        Dim dv As DataView = db.RunSelectSQL(strSQL)
        If dv.Table.Rows.Count <> 0 Then
            '查询结果不为空进行赋值
            Me.TextBox16.Text = dv.Table.Rows(0)(0)
        Else
            '否则清空TextBox
            Me.TextBox16.Text = ""
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    End Sub
End Class