数据库（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 SubPrivate 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 SubSub Bind()Dim objConn As New OleDb.OleDbConnectionDim objComm As New OleDb.OleDbCommandobjConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"objComm.CommandText = "SELECT * from 学生表 "objComm.Connection = objConnobjDa.SelectCommand = objCommDim 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.LoadDim objConn As New OleDb.OleDbConnectionDim objComm As New OleDb.OleDbCommandDim objDa As New OleDb.OleDbDataAdapterDim objDs As New DataSetobjConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"objComm.CommandText = "SELECT * from 学生表"objComm.Connection = objConnobjDa.SelectCommand = objCommobjConn.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.TextmyRow("姓名") = TextName.TextmyRow("性别") = Combo1.SelectedItemmyRow("出生日期") = DatePicker.ValuemyRow("选课（专业）") = TextOptionClass.TextmyRow("是否为艺考生") = CheckBox1.CheckedmyRow("学分") = TextScore.TextmyRow("备注") = TextTip.TextobjXSTable.Rows.Add(myRow)Reload()End Sub

修改

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.ClickDim index As Integer = DataGridView1.CurrentRow.IndexobjXSTable.Rows.Item(index).Item(0) = TextID.TextobjXSTable.Rows.Item(index).Item(1) = TextName.TextobjXSTable.Rows.Item(index).Item(2) = Combo1.SelectedItemobjXSTable.Rows.Item(index).Item(3) = DatePicker.ValueobjXSTable.Rows.Item(index).Item(4) = TextOptionClass.TextobjXSTable.Rows.Item(index).Item(5) = CheckBox1.CheckedobjXSTable.Rows.Item(index).Item(6) = TextScore.TextobjXSTable.Rows.Item(index).Item(7) = TextTip.TextReload()
End Sub

删除一条记录

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

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

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

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

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.ClickDim objConn As New OleDb.OleDbConnectionDim objComm As New OleDb.OleDbCommandDim objDa As New OleDb.OleDbDataAdapterDim objDs As New DataSetobjConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"If FRid.Checked = True ThenobjComm.CommandText = "SELECT * from 学生表 where 学号 like '%" & FTid.Text & "%'"ElseIf FRName.Checked = True ThenobjComm.CommandText = "SELECT * from 学生表 where 姓名 like '%" & FTName.Text & "%'"End IfobjComm.Connection = objConnobjDa.SelectCommand = objCommobjConn.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.CellClickTextID.Text = DataGridView1.CurrentRow.Cells(0).Value.ToStringTextName.Text = DataGridView1.CurrentRow.Cells(1).Value.ToStringCombo1.SelectedItem = DataGridView1.CurrentRow.Cells(2).Value.ToStringCombo1.Text = DataGridView1.CurrentRow.Cells(2).Value.ToStringDatePicker.Value = DataGridView1.CurrentRow.Cells(3).ValueTextOptionClass.Text = DataGridView1.CurrentRow.Cells(4).Value.ToStringCheckBox1.Checked = DataGridView1.CurrentRow.Cells(5).ValueTextScore.Text = DataGridView1.CurrentRow.Cells(6).Value.ToStringTextTip.Text = DataGridView1.CurrentRow.Cells(7).Value.ToString
End Sub

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

Imports System.Windows.Forms.VisualStyles.VisualStyleElement.ButtonPublic Class 课程表Dim objDa As New OleDb.OleDbDataAdapterDim objDs As New DataSetDim objXSTable As DataTablePrivate Sub 课程表_Load(sender As Object, e As EventArgs) Handles MyBase.LoadDim objConn As New OleDb.OleDbConnectionDim objComm As New OleDb.OleDbCommandDim objDa As New OleDb.OleDbDataAdapterDim objDs As New DataSetobjConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"objComm.CommandText = "SELECT * from 课程表"objComm.Connection = objConnobjDa.SelectCommand = objCommobjConn.Open()objDa.Fill(objDs, "kcb")objConn.Close()DataGridView1.DataSource = objDs.Tables("kcb")Bind()End SubSub Reload()objDa.Update(objDs, "kcb1")objXSTable.Clear()Bind()End SubSub Bind()Dim objConn As New OleDb.OleDbConnectionDim objComm As New OleDb.OleDbCommandobjConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"objComm.CommandText = "SELECT * from 课程表 "objComm.Connection = objConnobjDa.SelectCommand = objCommDim 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 SubPrivate Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.ClickTextID.Text = ""TextName.Text = ""End SubPrivate Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.ClickDim index As Integer = DataGridView1.CurrentRow.IndexobjXSTable.Rows.Item(index).Delete()Reload()End SubPrivate Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.ClickDim index As Integer = DataGridView1.CurrentRow.IndexobjXSTable.Rows.Item(index).Item(0) = TextID.TextobjXSTable.Rows.Item(index).Item(1) = TextName.TextEnd SubPrivate Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.ClickDim myRow As DataRow = objXSTable.NewRow()myRow("课程编号") = TextID.TextmyRow("课程名称") = TextName.TextobjXSTable.Rows.Add(myRow)Reload()End SubPrivate Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.ClickDim objConn As New OleDb.OleDbConnectionDim objComm As New OleDb.OleDbCommandDim objDa As New OleDb.OleDbDataAdapterDim objDs As New DataSetobjConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='G:\桌面\学生信息管理系统\学生信息.accdb'"If FRid.Checked = True ThenobjComm.CommandText = "SELECT * from 课程表 where 课程编号 like '%" & FTid.Text & "%'"ElseIf FRName.Checked = True ThenobjComm.CommandText = "SELECT * from 课程表 where 课程名称 like '%" & FTName.Text & "%'"End IfobjComm.Connection = objConnobjDa.SelectCommand = objCommobjConn.Open()objDa.Fill(objDs, "kcb")objConn.Close()DataGridView1.DataSource = objDs.Tables("kcb")End SubPrivate Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClickTextID.Text = DataGridView1.CurrentRow.Cells(0).Value.ToStringTextName.Text = DataGridView1.CurrentRow.Cells(1).Value.ToStringEnd Sub
End Class

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

等待下一次更新！