对数据库（Access）的一些操作--对数据的操作

来源：互联网发布：日韩东亚危机知乎编辑：程序博客网时间：2024/06/15 19:17

1、读取数据

首先，需要添加引用。[项目]--[引用的添加]--[COM选项]--[选择Microsoft ActiveX Data Object 2.7 Library]--[OK]

其次，添加DataGrid控件。

    Private Sub frmList_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cnn As ADODB.Connection
        Dim Myrec As ADODB.Recordset
        Dim tbl As DataTable
       cnn = New ADODB.Connection      '创建数据库句柄
        Myrec = New ADODB.Recordset    '创建纪录句柄
        frmMain = New frmMain
        ChDir(".")                                             '获得当前目录路径
        cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source = db1.mdb")           '打开数据库

        Myrec.Open("Table1", cnn, 1, , )   '打开记录集
        'table的作成
        tbl = New DataTable("Table1")
        tbl.Columns.Add("No")
        tbl.Columns.Add("Name")
        tbl.Columns.Add("Age")

        'Display the Record
        Dim i As Integer
        Dim nCount As Integer
        nCount = Myrec.RecordCount
        For i = 1 To nCount
            tbl.Rows.Add(New Object() {Myrec.Fields(0).Value.ToString, Myrec.Fields(1).Value.ToString, Myrec.Fields(2).Value.ToString})
            Myrec.MoveNext()
        Next
End Sub

2、插入数据

            Myrec.AddNew()
            Myrec.Fields(0).Value = TextBox1.Text.ToString
            Myrec.Fields(1).Value = TextBox2.Text.ToString
            Myrec.Fields(2).Value = TextBox3.Text.ToString
            Myrec.Update()

3、删除数据

      '----------------Method 1-----------------
        'Dim oConn As ADODB.Connection
        'Dim oRec As ADODB.Recordset

        'oConn = New ADODB.Connection
        'ChDir(".")
        'oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb")
        'oRec = oConn.Execute("DELETE FROM Dic1 WHERE No= '" + TextBox1.Text + "'")

'----------------Method 2-----------------

        'Dim strCon As String = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = db1.mdb "
        'Dim myConn As OleDbConnection = New OleDbConnection(strCon)
        'myConn.Open()
        'Dim strDele As String = "DELETE FROM Dic1 WHERE No= '" + TextBox1.Text+"'"
        'Dim myCommand As OleDbCommand = New OleDbCommand(strDele, myConn)
        'myCommand.ExecuteNonQuery()

        '----------------Method 3-----------------
        Myrec.MoveFirst()
        Myrec.Find("No = '" + TextBox1.Text + "'")
        If Myrec.EOF Then
            Beep()
            MsgBox("没有 No 「" & TextBox1.Text & "」 .", MsgBoxStyle.OKOnly, "Notice")
        Else
            Myrec.Delete()
            Myrec.Update()
            MsgBox("删除成功！", MsgBoxStyle.OKOnly, "Notice")
        End If

4、检索

                Myrec.MoveFirst()
                Myrec.Find("Name = '" + searchtxt.Text + "'")
                If Myrec.EOF Then
                    MsgBox("没有 Name 「" & searchtxt.Text & "」 ", MsgBoxStyle.OKOnly, "Notice")
                Else
                    Call ShowRecord()
                End If

5、更新

        Myrec.Fields(0).Value = TextBox1.Text
        Myrec.Fields(1).Value = TextBox2.Text
        Myrec.Fields(2).Value = TextBox3.Text
        Myrec.Update()
        MsgBox("更新成功！", MsgBoxStyle.OKOnly, "Notice")