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

Question

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.

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:

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.

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

Solution

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

"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

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

Comments

[PasinduM]

Thank you very much for the answer. Now i can access to my database via my vb.net application. but still i'm unable to delete the record. that said me OleDbException was unhandled and no value given for one or more required parameters. Thank you for your help. I appreciate that very much.!! Pasindu Mallikaarachchi

[Steve]

Well, check your table named table1. It should contains the field Student number (just one space)

[PasinduM]

Thank you very much my problem was solved. my Field name was little bit different I used Student_Name in the access. Now i correct that. Thank you for your time sir. Really appreciate your help.

[LarsTech]

Never use an empty try-catch.