数据库（vb.net+OleDB+Access）简易学生信息管理系统

在我们日常生活当中，数据库一词往往离不开我们的编程界，在学校、仓库等方面起着存储数据及数据关系作用的文件。相较于Excel，Access可以存储无限多的记录，内容也十分丰富，例如文本、数字、日期、T&F等。而且不需要额外的转换数据即可被.net读取使用（难道不是已经封装好了吗doge），好了开始我们今天的教学。

如题，我们需要完成几项工作

一、安装Office2016，也可以单独安装Access2016，新建数据库

二、如果安装顺利那么就不需要安装“引擎”，因为本人安装完之后VS闪退后来卸载，然后重装Office解决。主要是在这一步栽的人比较多，不懂的可以评论或私信解决哦

 三、打开VS，新建项目，设计窗体

 1、学生表

 我们做数据库开发会使用到DataGridView控件，用来显示查询结果和预览。

2、课程表

3、父窗体MDI容器

四、开始编写代码

1、主窗体：（窗口管理器）

Public Class 窗口管理器
    Private Sub 学生表ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 学生表ToolStripMenuItem.Click
        学生表.MdiParent = Me
        学生表.Show()
    End Sub

    Private Sub 课程表ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 课程表ToolStripMenuItem.Click
        课程表.MdiParent = Me
        课程表.Show()
    End Sub
End Class

尽量使用英文做变量名，此处为了直观。

2、学生表

全局变量声明

Dim objDa As New OleDb.OleDbDataAdapter
Dim objDs As New DataSet
Dim objXSTable As DataTable

全局过程声明：

    Sub Reload()
        objDa.Update(objDs, "xsb1")
        objXSTable.Clear()
        Bind()

    End Sub

    Sub Bind()
        Dim objConn As New OleDb.OleDbConnection
        Dim objComm As New OleDb.OleDbCommand
        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"

        objComm.CommandText = "SELECT * from 学生表 "

        objComm.Connection = objConn
        objDa.SelectCommand = objComm
        Dim builder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(objDa)
        objConn.Open()
        objDa.Fill(objDs, "xsb1")
        objXSTable = objDs.Tables("xsb1")
        objConn.Close()
        DataGridView1.DataSource = objDs.Tables("xsb1")

    End Sub

窗体加载时，我们要先填充表格（初始化）

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim objConn As New OleDb.OleDbConnection
        Dim objComm As New OleDb.OleDbCommand
        Dim objDa As New OleDb.OleDbDataAdapter
        Dim objDs As New DataSet

        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"
        objComm.CommandText = "SELECT * from 学生表"
        objComm.Connection = objConn
        objDa.SelectCommand = objComm
        objConn.Open()
        objDa.Fill(objDs, "xsb")
        objConn.Close()
        DataGridView1.DataSource = objDs.Tables("xsb")
        Bind()
    End Sub

录入一条记录：（注意，不管时录入还是修改，主键的内容都不可以为空！）

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
     '        Dim objConn As New OleDb.OleDbConnection
     '        Dim objComm As New OleDb.OleDbCommand

     '        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
     'Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"

     Dim myRow As DataRow = objXSTable.NewRow()
     myRow("学号") = TextID.Text
     myRow("姓名") = TextName.Text
     myRow("性别") = Combo1.SelectedItem
     myRow("出生日期") = DatePicker.Value
     myRow("选课（专业）") = TextOptionClass.Text
     myRow("是否为艺考生") = CheckBox1.Checked
     myRow("学分") = TextScore.Text
     myRow("备注") = TextTip.Text
     objXSTable.Rows.Add(myRow)
     Reload()
 End Sub

修改

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim index As Integer = DataGridView1.CurrentRow.Index
    objXSTable.Rows.Item(index).Item(0) = TextID.Text
    objXSTable.Rows.Item(index).Item(1) = TextName.Text
    objXSTable.Rows.Item(index).Item(2) = Combo1.SelectedItem
    objXSTable.Rows.Item(index).Item(3) = DatePicker.Value
    objXSTable.Rows.Item(index).Item(4) = TextOptionClass.Text
    objXSTable.Rows.Item(index).Item(5) = CheckBox1.Checked
    objXSTable.Rows.Item(index).Item(6) = TextScore.Text
    objXSTable.Rows.Item(index).Item(7) = TextTip.Text
    Reload()
End Sub

删除一条记录

 Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
     Dim index As Integer = DataGridView1.CurrentRow.Index
     objXSTable.Rows.Item(index).Delete()
     Reload()
 End Sub

清理文本框内的数据（不影响数据库）

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    TextID.Text = ""
    TextName.Text = ""
    Combo1.SelectedIndex = -1
    DatePicker.Value = #2000/01/01#
    TextOptionClass.Text = ""
    CheckBox1.Checked = False
    TextScore.Text = ""
    TextTip.Text = ""
End Sub

查询（模糊查询，简单的SQL语句）

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim objConn As New OleDb.OleDbConnection
        Dim objComm As New OleDb.OleDbCommand
        Dim objDa As New OleDb.OleDbDataAdapter
        Dim objDs As New DataSet

        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"

        If FRid.Checked = True Then
            objComm.CommandText = "SELECT * from 学生表 where 学号 like '%" & FTid.Text & "%'"
        ElseIf FRName.Checked = True Then
            objComm.CommandText = "SELECT * from 学生表 where 姓名 like '%" & FTName.Text & "%'"
        End If
        objComm.Connection = objConn
        objDa.SelectCommand = objComm
        objConn.Open()
        objDa.Fill(objDs, "xsb")
        objConn.Close()
        DataGridView1.DataSource = objDs.Tables("xsb")
    End Sub

点击表格任意一个单元格（立马填充到左下角的编辑区域，但注意主键不能为空，本人没有做错误处理）

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
    TextID.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString
    TextName.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString
    Combo1.SelectedItem = DataGridView1.CurrentRow.Cells(2).Value.ToString
    Combo1.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString
    DatePicker.Value = DataGridView1.CurrentRow.Cells(3).Value
    TextOptionClass.Text = DataGridView1.CurrentRow.Cells(4).Value.ToString
    CheckBox1.Checked = DataGridView1.CurrentRow.Cells(5).Value
    TextScore.Text = DataGridView1.CurrentRow.Cells(6).Value.ToString
    TextTip.Text = DataGridView1.CurrentRow.Cells(7).Value.ToString
End Sub

3、课程表，类似于学生表。

Imports System.Windows.Forms.VisualStyles.VisualStyleElement.Button

Public Class 课程表

    Dim objDa As New OleDb.OleDbDataAdapter
    Dim objDs As New DataSet
    Dim objXSTable As DataTable
    Private Sub 课程表_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim objConn As New OleDb.OleDbConnection
        Dim objComm As New OleDb.OleDbCommand
        Dim objDa As New OleDb.OleDbDataAdapter
        Dim objDs As New DataSet

        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"
        objComm.CommandText = "SELECT * from 课程表"
        objComm.Connection = objConn
        objDa.SelectCommand = objComm
        objConn.Open()
        objDa.Fill(objDs, "kcb")
        objConn.Close()
        DataGridView1.DataSource = objDs.Tables("kcb")
        Bind()
    End Sub
    Sub Reload()
        objDa.Update(objDs, "kcb1")
        objXSTable.Clear()
        Bind()

    End Sub
    Sub Bind()
        Dim objConn As New OleDb.OleDbConnection
        Dim objComm As New OleDb.OleDbCommand
        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"

        objComm.CommandText = "SELECT * from 课程表 "

        objComm.Connection = objConn
        objDa.SelectCommand = objComm
        Dim builder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(objDa)
        objConn.Open()
        objDa.Fill(objDs, "kcb1")
        objXSTable = objDs.Tables("kcb1")
        objConn.Close()
        DataGridView1.DataSource = objDs.Tables("kcb1")

    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        TextID.Text = ""
        TextName.Text = ""
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim index As Integer = DataGridView1.CurrentRow.Index
        objXSTable.Rows.Item(index).Delete()
        Reload()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim index As Integer = DataGridView1.CurrentRow.Index
        objXSTable.Rows.Item(index).Item(0) = TextID.Text
        objXSTable.Rows.Item(index).Item(1) = TextName.Text
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim myRow As DataRow = objXSTable.NewRow()
        myRow("课程编号") = TextID.Text
        myRow("课程名称") = TextName.Text
        objXSTable.Rows.Add(myRow)
        Reload()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim objConn As New OleDb.OleDbConnection
        Dim objComm As New OleDb.OleDbCommand
        Dim objDa As New OleDb.OleDbDataAdapter
        Dim objDs As New DataSet

        objConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"

        If FRid.Checked = True Then
            objComm.CommandText = "SELECT * from 课程表 where 课程编号 like '%" & FTid.Text & "%'"
        ElseIf FRName.Checked = True Then
            objComm.CommandText = "SELECT * from 课程表 where 课程名称 like '%" & FTName.Text & "%'"
        End If
        objComm.Connection = objConn
        objDa.SelectCommand = objComm
        objConn.Open()
        objDa.Fill(objDs, "kcb")
        objConn.Close()
        DataGridView1.DataSource = objDs.Tables("kcb")
    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        TextID.Text = DataGridView1.CurrentRow.Cells(0).Value.ToString
        TextName.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString

    End Sub
End Class

本系统由于是初级阶段，没有表与表之间的关系，目前属于孤立阶段，但是简单的录入、修改和查询已经可以实现。

等待下一次更新！