在 VB.NET 2005 中使用 MS Access 2003 插入、更新、删除和搜索值

下载 WithAccess - 143.91 KB

Shahzad Mahmood
MughalShahzad@gmail.com
Netsol Technologies Limited
Safari Villas,
巴基斯坦，拉瓦尔品第

引言

本文和示例涵盖了“如何在 VB.NET 2005 中使用 MS Access 2003 数据库插入、更新、删除和搜索值”。虽然 VB.NET 2005 提供了更多的方法来完成这项工作，但我发现这种方法对于初学者来说更容易、更简单和易于理解。

背景

学习者需要具备 VB.NET 2005 和 MS Access 2003 的一些编程经验

Using the Code

按照以下步骤操作

1. 创建一个 VB.NET 2005 Windows 项目
2. 在窗体上放置 3 个标签
3. 放置 3 个文本框，重命名它们为 txtAu_ID、txtAuthor 和 txtSearchResult
4. 放置 4 个按钮，重命名它们为 btnInsert、btnUpdate、btnDelet 和 btnSearch
5. 创建 MS Access 数据库 "TestDB.mdb"
6. 创建一个名为 "Authors" 的表，其中包含 2 个字段 "Au_ID" 和 "Author"

复制并粘贴以下代码

Imports System.Data

Imports System.Data.OleDb 

Public Class Form1 

    Dim cnnOLEDB As New OleDbConnection

    Dim cmdOLEDB As New OleDbCommand
 
    Dim cmdInsert As New OleDbCommand

    Dim cmdUpdate As New OleDbCommand

    Dim cmdDelete As New OleDbCommand

    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & 
        System.Environment.CurrentDirectory & "\TestDB.mdb" 

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

        cnnOLEDB.ConnectionString = strConnectionString

        cnnOLEDB.Open()

    End Sub 

    Private Sub btnSearch_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles btnSearch.Click 

    txtAearchResult.Text = "" 

    Dim vSearch As String = InputBox("Enter Integer number to search name:") 

    If vSearch <> "" Then 

        cmdOLEDB.CommandText = "SELECT Au_ID,
            Author FROM Authors WHERE Au_ID=" & CInt(vSearch) 

        cmdOLEDB.Connection = cnnOLEDB

        Dim rdrOLEDB As OleDbDataReader = cmdOLEDB.ExecuteReader

        If rdrOLEDB.Read = True Then
        
        txtAearchResult.Text &= rdrOLEDB.Item(0).ToString & " " & 
            rdrOLEDB.Item(1).ToString

        rdrOLEDB.Close()

        Exit Sub

        Else

            MsgBox(rdrOLEDB.Close()

            "Record not found")

            Exit Sub 

        End If

    Else

        MsgBox("Enter search value.") 

        Exit Sub 

    End If 

    End Sub

    Private Sub btnInsert_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles btnInsert.Click 

    If txtAu_ID.Text <> "" And txtAuthor.Text <> "" Then 

        cmdInsert.CommandText = "INSERT INTO Authors (Au_ID, Author) VALUES (" & 
            xtAu_ID.Text & ", '" & txtAuthor.Text & "');" 

        'MsgBox(cmdInsert.CommandText)

         cmdInsert.CommandType = CommandType.Text

         cmdInsert.Connection = cnnOLEDB

         cmdInsert.ExecuteNonQuery()

         MsgBox(txtAu_ID.Text = "Record inserted.")"" 

         txtAuthor.Text = "" 

    Else 

        MsgBox("Enter the required values:" & 
            vbNewLine & "1. Au_ID" & vbNewLine & "2.Author")

    End If 

    cmdInsert.Dispose()

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles btnUpdate.Click

    If txtAu_ID.Text <> "" And txtAuthor.Text <> "" Then

        cmdUpdate.CommandText = "UPDATE Authors SET Author = '" & txtAuthor.Text & "' 
        WHERE Au_ID = " & txtAu_ID.Text & ";" 

        'MsgBox(cmdUpdate.CommandText)

        cmdUpdate.CommandType = CommandType.Text

        cmdUpdate.Connection = cnnOLEDB

        cmdUpdate.ExecuteNonQuery()

        MsgBox(txtAu_ID.Text = "Record updated.")"" 

        txtAuthor.Text = "" 

    Else

        MsgBox("Enter the required values:" & vbNewLine & "1. Au_ID" & vbNewLine & 
            "2.Author")

    End If

    cmdUpdate.Dispose()

    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles btnDelete.Click 

    If txtAu_ID.Text <> "" Then 

        cmdDelete.CommandText = "DELETE FROM Authors WHERE Au_ID = " &
            txtAu_ID.Text  & ";" 

        'MsgBox(cmdDelete.CommandText)

        cmdDelete.CommandType = CommandType.Text

        cmdDelete.Connection = cnnOLEDB

        cmdDelete.ExecuteNonQuery()

        MsgBox(txtAu_ID.Text = "Record deleted.")"" 

        txtAuthor.Text = "" 

        cmdDelete.Dispose()

    Else

        MsgBox("Enter the required values:" & vbNewLine & "1. Au_ID") 

    End If

    cmdUpdate.Dispose()

    End Sub

End Class

有关更多详细信息，请在下面的留言板上给我留言