delete record from access database

To delete Record from Access database we have used Dataset and Oledbdataadapters 

DataAdapter :- DataAdapter  is like a pipeline or path or bridge that is used to execute the given query on database table . DataAdapter that we have used is OledbdataAdapter as we are using MS-Access as database . Data Adapter first takes the command to be executed on table data from Oledbcommand object and this object contains the query that is to be executed on database . Then it execute query on Database table data and store resultant data in form of rows and columns or Tabulated form in Dataset

Dataset :- Dataset is generally a memory area where the result of the query that we have executed on database is placed . When a query is passed by using Oledbdataadapter to execute that query on database specified table . Then the result of the query has to be placed some to use it . Like to show it in Datagridview or to show that result data in Textboxes or to manupulate the data and then save that manipulated data again to Table in database

DOWNLOAD this Demo Project from my gmail upload link :-

https://drive.google.com/?tab=mo&authuser=0#folders/0ByR2IblIaEGyZ1ZxLXFiNFNlOUk

Steps to Make Microsoft Access Database:

1. Go to desired location you want to make your Database

2. Right click then click on new and 

3. Then click Microsoft Access 2007 Database

4. Create Desired Table with required Fields 

5. Save it as Microsoft Access database 2003 format 

6. click ok 

DOWNLOAD THIS Demo Database from my gmail upload link :-

https://docs.google.com/file/d/0ByR2IblIaEGyaC0tamUyc1piRkE/edit

Steps To Make This Demo Project :-

1. Start your Visual Studio 2010 

2. At start page click on Create New Project 

3. In Dialog Box provide Project name and path where to save this project and click ok

4. Now from Toolbox Drag one ComboBox , one Label and One Button

5. Now Change Label 'Text' Property to "ID_num"

6. Now Change Button 'Text' Property to "Delete"

7. Make Design as shown below

Design of Form 1 

8.  In Below image you can see there are two records in Data base Table 'info_table ' .

9. Now Run the Application 

10. Click on Down arrow of combobox Your ID-num will be shown as image below

11. Now select an id_num from combobox items and then click on Delete

12. Now Record will be deleted from database table as shown below

Code for Application 

Imports System.Data.OleDb

'http://www.geeksprogrammings.blogspot.in

Public Class Form1

    Dim connection As New OleDbConnection

    Dim Del_command As New OleDbCommand

    Dim select_command As New OleDbCommand

    Dim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

        "C:\test.mdb"

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

        connection.ConnectionString = ConnectionString

        connection.Open()

        select_command.CommandText = "select id_num from info_table"

        select_command.CommandType = CommandType.Text

        Dim adapter As New OleDbDataAdapter("select id_num from info_table", connection)

        'adapter.SelectCommand = select_command

        Dim dt As New DataTable

        adapter.Fill(dt)

        select_command.Connection = connection

        select_command.ExecuteNonQuery()

        Dim i As New Integer

        For i = 0 To dt.Rows.Count - 1

            ComboBox1.Items.Add(dt.Rows(i).Item(0))

        Next

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Del_command.CommandText = "delete from info_table where id_num=@id_num"

        Del_command.Parameters.AddWithValue("@id_num", ComboBox1.SelectedItem)

        Del_command.CommandType = CommandType.Text

        Del_command.Connection = connection

        Del_command.ExecuteNonQuery()

        MsgBox("Record Deleted ")

    End Sub

Explanation of Code

Imports System.Data.OleDb

This line of code tells we are importing Oledb Namespace. Oledb stands for Object Linking and Embedding . For using Access database with .net applications we have to inherit classes from this oledb Namespace . Oledb Namespace contains all classes that are required to connect vb.net/c# application to Microsoft Access
Database

Public Class Form1
    Dim connection As New OleDbConnection
    Dim command As New OleDbCommand
    Dim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\test.mdb

End Class

Now In These lines of code we declared 3 things

1. OledbConnection :- OledbConnection is a class contained in System.Data.Oledb Namespace and it is used to make connectivity to Access Database . It receives the connection string that tells the path of Microsoft Access Database to connect to it. In this line of code we have created instance of Oledbconnection class

2. OledbCommand :- OledbCommand is a class contained in System.Data.Oledb Namespace and it is used to define or specify the command that we are going to execute on Microsoft Access Database. In this line of code we are creating instance of OledbCommand to use this instance in further code

3. Connection string :- Now we are creating a variable named connectionString that will receive the string or path that tell how we connect to our Access database. It receives two parameters :-

Provider=Microsoft.Jet.OLEDB.4.0 -- 

Data Source=" & "C:\test.mdb

Now Provider is main part here in connection string . Provider is different for different approaches used for connecting data to database . The Connection string contains information that the Provider needs to know to connect to database Once connected then rest of job is done by provider


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connection.ConnectionString = ConnectionString
        connection.Open()
    End Sub

Now in this block of code we have initialized our OledbConnection instance with connection string variable that we have initialized in previous step. So in this code we have given the Oledbconnectin instance means connection some information about Provider to use and location of database file on computer

Then we have used connection.open() method opens port for Enter into Access Database. this opens a pipe to execute query in database


How To Use Access database with vb.net

Share this

heemanshu bhalla