如何使用 vb.net 形式删除 MS access 数据库的记录以及如何使用 vb.net 形式进入受密码保护的数据库

How to delete records of MS access database using vb.net form and how to enter a password protected database using vb.net form

提问by PasinduM

I'm a new for a vb.net and i'm still a student. I created a form to enter students enrollment details in vb.net just for educational needs. I created a database using MS Access 2010 and linked it to my vb form. It works fine and I can enter details via vb.net application to my access database, but I'm unable to handle following tasks.

我是 vb.net 的新手，我还是一名学生。我创建了一个表格，用于在 vb.net 中输入学生注册详细信息，仅用于教育需要。我使用 MS Access 2010 创建了一个数据库并将其链接到我的 vb 表单。它工作正常，我可以通过 vb.net 应用程序将详细信息输入到我的访问数据库，但我无法处理以下任务。

1) Delete record of the database using a Primary key( primary key of the my database " student number" here's my code; please correct me to do this task:

1）使用主键删除数据库的记录（我的数据库的主键“学号”这是我的代码；请纠正我来完成这个任务：

Imports System.Data.OleDb
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As OleDbConnection
        Dim com As OleDbCommand
        con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Pasindu\Documents\database9.accdb")
        com = New OleDbCommand("delete from Table1 where Student number =@sno", con)
        con.Open()
        com.Parameters.AddWithValue("@sno", TextBox1.Text)
        com.ExecuteNonQuery()
        MsgBox("Record Deleted")
        con.Close()
    End Sub
End Class

2) I want to protect my database using a password and I encrypt my MS Access database using a password. So I just need to know the modification to my code when I protect my database, because I'm unable to update my database using my previous code.. here's my previous code to enter data to the access database.. code works fine if there wasn't a password protected database.

2) 我想使用密码保护我的数据库，并使用密码加密我的 MS Access 数据库。所以我只需要在保护我的数据库时知道对我的代码的修改，因为我无法使用我以前的代码更新我的数据库..这是我以前将数据输入到访问数据库的代码..不是受密码保护的数据库。

Imports System.Data.OleDb
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As OleDbConnection
        Dim com As OleDbCommand
        con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Pasindu\Documents\database9.accdb;")
        com = New OleDbCommand("insert into table1 values(@sno,@sname,@nic)", con)
        con.Open()
        com.Parameters.AddWithValue("@sno", TextBox1.Text)
        com.Parameters.AddWithValue("@sname", TextBox2.Text)
        com.Parameters.AddWithValue("@nic", TextBox3.Text)
        com.ExecuteNonQuery()
        MsgBox("record added")
        con.Close()
    End Sub

采纳答案by Steve

To open an MS Access database protect with password you need to change your connection string

要使用密码打开 MS Access 数据库保护，您需要更改连接字符串

"Provider=Microsoft.ACE.OLEDB.12.0;" + 
"Data Source=C:\Users\Pasindu\Documents\database9.accdb;" + 
"Jet OLEDB:Database Password=MyDbPassword;"

where MyDbPassword should be replaced with the correct string

其中 MyDbPassword 应替换为正确的字符串

For the delete part of your question, I will try to change the command in

对于您问题的删除部分，我将尝试更改命令

delete * from Table1 where [Student number]=@sno

If your field name contains spaces then encapsulate the name with square brackets

如果您的字段名称包含空格，则用方括号封装名称

回答by Siyanda

Double click on the form and add the following code

双击表单并添加以下代码

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Administrator\Documents\Project.mdb")
Dim sql = "select ShopperID from Shopper"
Dim command As New OleDbCommand(sql, con)
Dim da As New OleDbDataAdapter(command)
Dim dt As New DataTable
Try
  con.Open()
  da.Fill(dt)
  Dim x As Integer
  For x = 0 To dt.Rows().Count
    ComboBox1.Items.Add(dt.Rows(x).Item("ShopperID").ToString)
  Next
Catch ex As Exception
End Try

Double click on the button and add the code below

双击按钮并添加下面的代码

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Administrator\Documents\Project.mdb")
Dim sql = "delete from Shopper where ShopperID='" & ComboBox1.SelectedValue & "'"
Dim command As New OleDbCommand(sql, con)
Dim da As New OleDbDataAdapter(command)
Dim dt As New DataTable
Try
  con.Open()
  da.Fill(dt)
  command.ExecuteNonQuery()
  con.Close()
  MessageBox.Show("Reocrd Deleted")
Catch ex As Exception
End Try