Click here to Skip to main content
11,717,570 members (80,798 online)
Click here to Skip to main content

Insert, Update, Delete & Search Values in MS Access 2003 with VB.NET 2005

, 9 Jun 2008 CPOL 237.7K 8.7K 32
Rate this:
Please Sign up or sign in to vote.
How to Insert, Update, Delete & Search Values in MS Access 2003 with VB.NET 2005

Download WithAccess - 143.91 KB

Shahzad Mahmood
MughalShahzad@gmail.com
Netsol Technologies Limited
Safari Villas,
Rawalpindi, Pakistan

Introduction

This article and sample covers that "How to Insert, Update, Delete and Search Values to/from MS Access database 2003 with VB.NET 2005", Although VB.NET 2005 gives lot more methodologies to do this same job but I find this more easier, simplest and understandable for a beginner.

Background

Learner must have little bit programming experience of VB.NET 2005 and MS Access 2003

Using the Code

Follow the steps:

  1. Create a VB.NET 2005 Windows Project,
  2. Place 3 Labels on form.
  3. Place 3 TextBoxes, Rename them txtAu_ID, txtAuthor, txtSearchResult
  4. Place 4 Buttons, Rename them btnInsert, btnUpdate, btnDelet, btnSearch
  5. Create MS Access Database "TestDB.mdb"
  6. Create a Table "Authors" with 2 fields "Au_ID" and "Author"

Copy and Pate the following code:

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

For further details send leave me a message on the messaged board below

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Member 3931606
Pakistan Pakistan
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionError in UPDATE Syntax query Pin
Gautam_Gstar25-Aug-15 21:09
memberGautam_Gstar25-Aug-15 21:09 
QuestionConnection Error Pin
Member 111344556-Oct-14 21:56
memberMember 111344556-Oct-14 21:56 
QuestioncnnOLEDB.Open error Pin
Member 1081656513-May-14 15:15
memberMember 1081656513-May-14 15:15 
GeneralMy vote of 5 Pin
Vast is best21-Dec-12 4:14
memberVast is best21-Dec-12 4:14 
Questioncopy this code place of above coding its working...!!!! Pin
Haresh Prajapati4-Dec-12 21:24
memberHaresh Prajapati4-Dec-12 21:24 
QuestionAbout Search Button Pin
Haresh Prajapati3-Dec-12 2:51
memberHaresh Prajapati3-Dec-12 2:51 
QuestionI want to disable the message INSERT INTO Authors (Au_ID, Author,Gender)..... when i insert data in the form Pin
Sachinwei23-May-12 18:39
memberSachinwei23-May-12 18:39 
QuestionHelp me how to have a perfect location of database on the project i publish Pin
Sachinwei22-May-12 19:30
memberSachinwei22-May-12 19:30 
QuestionHelp Pin
AmeeR-8922-May-12 0:31
memberAmeeR-8922-May-12 0:31 
Questionupdate database Pin
bill clift23-Mar-12 7:10
memberbill clift23-Mar-12 7:10 
Questionneed help Pin
alom_9320-Feb-12 12:32
memberalom_9320-Feb-12 12:32 
GeneralMy vote of 5 Pin
manish100028-Sep-10 4:24
membermanish100028-Sep-10 4:24 
Generalprint Pin
Mohamed Safi Samsudeen6-Jan-09 22:24
memberMohamed Safi Samsudeen6-Jan-09 22:24 
how to print the form in printer when i click the button.
GeneralRe: print Pin
Mohamed Safi Samsudeen11-Jan-09 22:47
memberMohamed Safi Samsudeen11-Jan-09 22:47 
GeneralRe: print Pin
Member 393160612-Jan-09 16:22
memberMember 393160612-Jan-09 16:22 
GeneralRe: print Pin
Mohamed Safi Samsudeen14-Jan-09 20:54
memberMohamed Safi Samsudeen14-Jan-09 20:54 
Generalfocus false Pin
Mohamed Safi Samsudeen12-Dec-08 3:20
memberMohamed Safi Samsudeen12-Dec-08 3:20 
Generalexe file Pin
Mohamed Safi Samsudeen28-Nov-08 19:53
memberMohamed Safi Samsudeen28-Nov-08 19:53 
GeneralRe: exe file Pin
Member 393160630-Nov-08 19:38
memberMember 393160630-Nov-08 19:38 
GeneralRe: exe file Pin
Mohamed Safi Samsudeen2-Dec-08 22:31
memberMohamed Safi Samsudeen2-Dec-08 22:31 
GeneralRe: exe file Pin
Member 39316062-Dec-08 23:53
memberMember 39316062-Dec-08 23:53 
Generalhi Pin
Mohamed Safi Samsudeen10-Nov-08 22:47
memberMohamed Safi Samsudeen10-Nov-08 22:47 
GeneralRe: hi Pin
Member 393160611-Nov-08 0:12
memberMember 393160611-Nov-08 0:12 
GeneralHi Pin
Mohamed Safi Samsudeen4-Nov-08 1:29
memberMohamed Safi Samsudeen4-Nov-08 1:29 
GeneralRe: Hi Pin
Member 39316064-Nov-08 17:01
memberMember 39316064-Nov-08 17:01 
Generalhelp me Pin
vprakashit22-Feb-13 13:35
membervprakashit22-Feb-13 13:35 
Generalhelp me Pin
vprakashit22-Feb-13 13:35
membervprakashit22-Feb-13 13:35 
Generalhelp me Pin
vprakashit22-Feb-13 13:36
membervprakashit22-Feb-13 13:36 
GeneralRe: Hi Pin
Mohamed Safi Samsudeen4-Nov-08 17:56
memberMohamed Safi Samsudeen4-Nov-08 17:56 
GeneralRe: Hi Pin
Member 39316064-Nov-08 19:21
memberMember 39316064-Nov-08 19:21 
GeneralRe: Hi Pin
Mohamed Safi Samsudeen4-Nov-08 20:40
memberMohamed Safi Samsudeen4-Nov-08 20:40 
GeneralRe: Hi Pin
Member 39316064-Nov-08 21:52
memberMember 39316064-Nov-08 21:52 
GeneralRe: Hi Pin
Mohamed Safi Samsudeen4-Nov-08 22:17
memberMohamed Safi Samsudeen4-Nov-08 22:17 
GeneralRe: Hi Pin
Member 39316064-Nov-08 22:48
memberMember 39316064-Nov-08 22:48 
GeneralRe: Hi Pin
Mohamed Safi Samsudeen4-Nov-08 23:01
memberMohamed Safi Samsudeen4-Nov-08 23:01 
GeneralRe: Hi Pin
Member 39316064-Nov-08 23:27
memberMember 39316064-Nov-08 23:27 
GeneralRe: Hi Pin
Mohamed Safi Samsudeen4-Nov-08 23:46
memberMohamed Safi Samsudeen4-Nov-08 23:46 
GeneralRe: Hi Pin
Member 39316065-Nov-08 0:17
memberMember 39316065-Nov-08 0:17 
GeneralHello Pin
Mohamed Safi Samsudeen30-Oct-08 4:53
memberMohamed Safi Samsudeen30-Oct-08 4:53 
GeneralRe: Hello Pin
Member 393160630-Oct-08 17:30
memberMember 393160630-Oct-08 17:30 
GeneralRe: Hello Pin
Mohamed Safi Samsudeen30-Oct-08 19:30
memberMohamed Safi Samsudeen30-Oct-08 19:30 
GeneralRe: Hello Pin
Member 393160630-Oct-08 22:04
memberMember 393160630-Oct-08 22:04 
GeneralRe: Hello Pin
Mohamed Safi Samsudeen30-Oct-08 23:52
memberMohamed Safi Samsudeen30-Oct-08 23:52 
GeneralHello sir, Pin
Mohamed Safi Samsudeen27-Oct-08 20:33
memberMohamed Safi Samsudeen27-Oct-08 20:33 
GeneralRe: Hello sir, Pin
Member 393160627-Oct-08 20:56
memberMember 393160627-Oct-08 20:56 
GeneralHi Pin
Mohamed Safi Samsudeen23-Oct-08 2:25
memberMohamed Safi Samsudeen23-Oct-08 2:25 
GeneralRe: Hi Pin
Member 393160623-Oct-08 19:07
memberMember 393160623-Oct-08 19:07 
GeneralRe: Hi Pin
Mohamed Safi Samsudeen25-Oct-08 3:39
memberMohamed Safi Samsudeen25-Oct-08 3:39 
Generalsql Pin
Mohamed Safi Samsudeen21-Oct-08 8:55
memberMohamed Safi Samsudeen21-Oct-08 8:55 
GeneralRe: sql Pin
Member 393160622-Oct-08 1:06
memberMember 393160622-Oct-08 1:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150901.1 | Last Updated 9 Jun 2008
Article Copyright 2008 by Member 3931606
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid