How to Connect to a Database and Add/Update/Delete Record

In this tutorial I will explain to you on how to connect to an Access database and allow you to Add/Update/Delete a record.

To fully understand these tutorials please download the source code How to Add/Update/Delete Record using MS Access Database.

This source code is part of the Hotel Reservation System that I am currently working.

At the end of this tutorial you will learn the basic of database programming. I would like, however, to emphasize especially for beginners that one way to learn programming is to know how to debug a program and devote some of your time to reading. Don't be frightened on how short or long an article should be. The important is at the end of the tutorial you will learn something NEW! If you already know the topic, then don’t bother to study this again.

Table of Contents

  1. Introduction
  2. Let’s get started
  3. Database Connection
  4. Add and Update a Record
  5. Delete a Record
  6. Final Thoughts

Introduction

Before I started learning VB.NET one of the topic that I search for in the internet is on how to connect to the database and make some changes to the table. Although there’s a lot of results, but I cannot find one that suit to my needs. Most of the tutorial is using drag and drop features of vb.net editor. Well, this is okay in most cases but what if you’d like to manipulate the data by code? So, I created this tutorial so that beginner programmer will learn from this.

Let’s get started

It is very important that you use your common sense to understand the logic of database programming. There’s a lot of features built-in to Visual Basic Editor that most programmer especially beginner who overlook it. One of the favorite tools I usually used is the DEBUGGER. If you only knew how important a debugger is, then you do not even need to study this tutorial. Why? Because you can jump right away to the source code and start firing the F8 command from your keyboard and analyze every line as you step through the code.

Anyway beginner is a beginner. You need to start from scratch. If you have already downloaded the source code, then open it in the visual basic .net editor by double clicking the “HowtoAddUpdateDeleteRecord.sln”. If you want to know what is the object that runs the first time you start the program (by pressing F5) then double click the “My Project” at the Solution Explorer. Look at the Startup Form.

You will see that the value is “frmCustomersList”. Now, click this object in the Solution Explorer and click the View Code at the toolbar. Look for the Load event similar below:

Private Sub frmCustomersList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sSql = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address FROM Customers ORDER BY CustomerID ASC"

        Call FillList()

        FillListView(lvList, GetData(sSql))
End Sub

frmCustomersList_Load is the second procedure that runs when you hit the F5 Key from your keyboard. If you’d like to know how this code is executed then press F8.

Believe it or not F8 is the answer to all your programming question. And I really mean it. When I started programming all I do is to search for free source code and start using the debugging tool. That’s why Visual Basic is being named as Rapid Application Development or RAD.

If you follow the debugger the first line it executes is the Private Sub frmCustomersList_Resize(ByVal sender As Object, ByVal e As System.EventArgs) then followed by frmCustomersList_Load which is actually the important procedure to note here.

Another important debugging tool is “Toggle Breakpoint”. You will be prompted to your code if one of the line is marked by toggle break point. This can be done by pressing the F9 key or clicking the Debug menu then Toggle Breakpoint. This tool is important if the form is already loaded and you want to tract the execution of a code say within a command button.

For example. Open the form frmCustomersList and double click the add button and move the up arrow key once and press F9. You willl have a picture as shown below:

[inline:Toggle Breakpoint.jpg]

Now, when you run the program and click the Add button you will be directed to the code editor window. This case you will see what is happening when you are executing the program. Isn’t it nice?

Database Connection

In order to connect to the database you need a connection string like this:

Public Const cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=../data/sample.mdb"

Then open it by using this command:

Dim cnHotel As OleDbConnection

cnHotel = New OleDbConnection

With cnHotel

If .State = ConnectionState.Open Then .Close()
.ConnectionString = cnString

.Open()
End With

You need this whether you use OleDbDataReader, ExecuteNonQuery or OleDbCommandBuilder to read or write into the database table. To know more about this class just click this command and press F1 key to open the help files. Be sure you installed the MSDN.

Since you have already open the connection to your database this is now the time to fill the ListView with data. This can be done by calling a function like:

FillListView(lvList, GetData(sSql))

The line of code will then execute a function:

    'Fill ListView control with data
    Public Sub FillListView(ByRef lvList As ListView, ByRef myData As OleDbDataReader)
        Dim itmListItem As ListViewItem
        Dim strValue As String

        Do While myData.Read

            itmListItem = New ListViewItem()

            strValue = IIf(myData.IsDBNull(0), "", myData.GetValue(0))

            itmListItem.Text = strValue

            For shtCntr = 1 To myData.FieldCount() - 1
                If myData.IsDBNull(shtCntr) Then
                    itmListItem.SubItems.Add("")
                Else
                    itmListItem.SubItems.Add(myData.GetString(shtCntr))
                End If
            Next shtCntr

            lvList.Items.Add(itmListItem)
        Loop

    End Sub

Again in order to see how this code is being executed just run the program using the debugging tool (either F8 or F9).

The rest of the procedure is executed only when they are called. For example, the code below is executed only when you click the Add button.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim CustomerID As String

        frmCustomers.State = gModule.FormState.adStateAddMode

        For Each sItem As ListViewItem In lvList.SelectedItems
            CustomerID = sItem.Text
        Next

        frmCustomers.CustomerID = CustomerID
        frmCustomers.ShowDialog()

        Call FillList()
End Sub

This code will open the form frmCustomers in add mode and will execute also its own Load Event. If you want to open the form frmCustomers in edit mode, then just double click the item in a ListView.

The code being executed are:

Private Sub lvList_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvList.DoubleClick
        Dim CustomerID As String

        For Each sItem As ListViewItem In lvList.SelectedItems
            CustomerID = sItem.Text
        Next

        With frmCustomers
            .State = gModule.FormState.adStateEditMode

            .CustomerID = CustomerID

            .ShowDialog()

            Call FillList()
        End With

        frmCustomers = Nothing

End Sub

The two procedure seems carry the same concept, by opening a form, except they vary on the button invoke for execution.

The line

frmCustomers.State = gModule.FormState.adStateAddMode

will tell the target form to open the connection to the database in add mode and

frmCustomers.State = gModule.FormState.adStateEditMode

will open the database in edit mode.

Add and Update a Record

Now, how to save the data in textboxes within the form? This can be done by calling a procedure called btnSave_Click. This procedure is fired when the Save button is clicked.

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim dt As DataTable = dsCustomers.Tables("Customers")

        If txtCustomerID.Text = "" Or txtCompanyName.Text = "" Then
            MsgBox("Please fill up Customer ID or Company Name information.", MsgBoxStyle.Critical)

            Exit Sub
        End If

        Try
            If State = gModule.FormState.adStateAddMode Then
                ' add a row
                Dim newRow As DataRow

                newRow = dt.NewRow()

                newRow("CustomerID") = txtCustomerID.Text

                dt.Rows.Add(newRow)
            End If

            With dt
                .Rows(0)("CustomerID") = txtCustomerID.Text
                .Rows(0)("CompanyName") = txtCompanyName.Text
                .Rows(0)("ContactName") = IIf(txtContactName.Text = "", System.DBNull.Value, txtContactName.Text)
                .Rows(0)("ContactTitle") = IIf(txtContactTitle.Text = "", System.DBNull.Value, txtContactTitle.Text)
                .Rows(0)("Address") = IIf(txtAddress.Text = "", System.DBNull.Value, txtAddress.Text)
                .Rows(0)("City") = IIf(txtCity.Text = "", System.DBNull.Value, txtCity.Text)
                .Rows(0)("Region") = IIf(txtRegion.Text = "", System.DBNull.Value, txtRegion.Text)
                .Rows(0)("PostalCode") = IIf(txtPostalCode.Text = "", System.DBNull.Value, txtPostalCode.Text)
                .Rows(0)("Country") = IIf(txtCountry.Text = "", System.DBNull.Value, txtCountry.Text)
                .Rows(0)("Phone") = IIf(txtPhone.Text = "", System.DBNull.Value, txtPhone.Text)
                .Rows(0)("Fax") = IIf(txtFax.Text = "", System.DBNull.Value, txtFax.Text)

                daCustomers.Update(dsCustomers, "Customers")

                MsgBox("Record successfully saved.", MsgBoxStyle.Information)
            End With

        Catch ex As OleDbException
            MsgBox(ex.ToString)
        End Try
    End Sub

The code for adding and updating a table is the same except that if you are in add mode you just simply add this command:

If State = gModule.FormState.adStateAddMode Then

' add a row
Dim newRow As DataRow

newRow = dt.NewRow()
newRow("CustomerID") = txtCustomerID.Text

dt.Rows.Add(newRow)
End If

This way you do not need to create a separate command to insert and update a table.

Delete a Record

Let us go back to frmCustomersList form and delete a record. The procedure before will be fired after clicking a Delete button:

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

        For Each sItem As ListViewItem In lvList.SelectedItems
            CustomerID = sItem.Text
        Next

        If CustomerID <> "" Then
            'Delete the selected record
            Dim strDeleted As Boolean

            strDeleted = ExecNonQuery("DELETE Customers.CustomerID FROM Customers WHERE CustomerID= '" & CustomerID & "'")

            If strDeleted = "True" Then
                MsgBox("Record's deleted.", MsgBoxStyle.Information)

                Call FillList()
            Else
                MsgBox(strDeleted)
            End If
        Else
            MsgBox("Please select record to delete.", MsgBoxStyle.Critical)
        End If
End Sub

The important line here is the

strDeleted = ExecNonQuery("DELETE Customers.CustomerID FROM Customers WHERE CustomerID= '" & CustomerID & "'")

which call the function ExecNonQuery and deletes a record based on the SQL Statement.

Final Thoughts

The above tutorial will simply teach you on how to connect to a database and make some changes to the database table.

It is very important that you read first some tutorials about programming before you dive into the source code if you’re just starting out.

If you really wanted to learn faster, then I recommend a book which is my reference also with this article. This book is called Beginning VB 2008 Databases: From Novice to Professional (Beginning: from Novice to Professional). The advantage of this book because it teaches your more specific to database programming.

Tags

Comments

i need more info about it

In reply to by Anonymous (not verified)

What kind of info do you need?

In reply to by admin

what do we need if we like to insert a picture in student information?

please i need an enrollemnt system created using visual basic 6.0 Pls help me

hi...just want to ask for a help! i'm a student and using vb 6.0 in creating a program... just want to ask how to delete a particular record from datagrid? thanks and regards!

hi..

i've some problem to view data from flexgrid..

that mean..my system is reseller sales system

first my system browse data in xls (excel) from document

then from the data that show in flexgrid i want to view in other GUI

can u help me?

hi..

how to make when click check box, the option button will enable

Hello your codes is so nice we learn a lot tank you

I;m having problem with INSERT into statement according to my vb and database?

In reply to by Anonymous (not verified)

What is really the problem?

Don't forget to see the error I descbribed. Thanks :)

In reply to by Anonymous (not verified)

Yes I'm working on it yesterday and I was not able to fix it. But don't worry I will fix it today if I have time.

how can i connect on an ms access database using javascript? tnx..

hi i downloaded your hotel reservation system and i noticed that you used access. so u used oledb for the connection and etc.. are there some difference if i change the connection to sqL? im using vb express 2008. i've learned many techncs from ur system. tnx a lot :D

In reply to by Anonymous (not verified)

For some reason yes. Please follow the database programming made easy tutorial. I explained the difference between oledb and sql connection.

With cnHotel If .State = ConnectionState.Open Then .Close() .ConnectionString = cnString .Open() End With Hi, Why do you close the connection before attempting to reopen? Why you do not keep the active connection?

For shtCntr = 1 To myData.FieldCount() - 1 If myData.IsDBNull(shtCntr) Then itmListItem.SubItems.Add("") Else itmListItem.SubItems.Add(myData.GetString(shtCntr)) End If Next shtCntr lvList.Items.Add(itmListItem)

hello sir, m a stud of TYBSc IT.....m wish 2 make my prjt in vb.net......since i dnt hv ny client 4 my hotel projt m unable 2 start of with my proj......wht 2 do......even i dwnloaded ur hotel prjt bt sum of d thngs i find was tough.......so plz plz help me sir.........as want 2 complete my proj on tm n there is no 1 2 hellp me out.....waitin 4 ur rply......

In reply to by Anonymous (not verified)

hey if u have any type of information abt payroll system plz plz plz help cuz this my first project and i dont knw wht to do and hw to strt so plz give me fast rply and im waiting for it also my email account is available:[email protected]

Hi With frmCustomers .State = gModule.FormState.adStateEditMode .CustomerID = CustomerID I have a problem here. it says that .STATE is not a member of frmCustomers .CUSTOMERID is not a member of frmCustomers Please Help me

Its really useful for me as a beginner

strValue = IIf(rdr.IsDBNull(0), "", rdr.GetValue(0)) item.Text = strValue Hi i need your explaination on the code above, wat use of the strValue variable and wat data it will get. Lastly why at last it need to put on "item.Text" Please I need a more details understanding explaination. It make me stuck here a time. Thank you

Gud day sir I'am a beginner, and I have a project in my subject. if I'am running my program theres an error it says that UNABLE TO CAST OBJECT OF TYPE 'SYSTEM.DATA.OLEBD.OLEDB.EXCEPTION' TO TYPE 'SYSTEM.DATA.OLEDBREADER'. I dont know how to fix it plz help..I'am using VB.NET I need ur reply plz....

This code will open the form frmCustomers in add mode and will execute also its own Load Event. If you want to open the form frmCustomers in edit mode, then just double click the item in a ListView. hello sir, im confused, where to "double click the item in a ListView" thanks. cavern

ublic Class Form1 Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\dotnet010\My Documents\my db.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True") Dim da As SqlDataAdapter Dim ds As DataSet Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim com As New SqlCommand com.Connection = con com.CommandType = CommandType.Text com.CommandText = "Select * from empdatatable" da = New SqlDataAdapter(com) ds = New DataSet da.Fill(ds, "empdatatable") End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim row1 As DataRow row1 = ds.Tables("empdatatable").NewRow() row1(0) = txtbxId.Text row1(1) = txtbxName.Text row1(2) = txtbxAddress.Text row1(3) = txtbxMobile.Text ds.Tables("empdatatable").Rows.Add(row1) Dim comins As New SqlCommand comins.Connection = con comins.CommandType = CommandType.Text comins.CommandText = "Insert into empdatatable values(" & txtbxId.Text & ",'" & txtbxName.Text & "','" & txtbxAddress.Text & "'," & txtbxMobile.Text & ")" da.InsertCommand = comins da.Update(ds, "empdatatable") End Sub i m using this type of code for insertion still der is error and record is not updated what should i do sir?

Hi i want to retreive image from sql and show in picture box in c# i saved image to database in the form of binary data kindly help me thanx

Hi! I studied the codings you did and fortunately i'm going through fine, Thanks anyway, but i've kinda stuck on an error while deleting data. it says 'data type mismatch in criteria exception' what cud possibly be wrong? I've used your coding, if you dont mind, as reference but I couldn't find what was missing. Please help me on this. Thanks

hi sir any one know what this error means " daAcntmngr.Update(dsAcntmngr, "AccountManager")-"Syntax error in INSERT INTO statement ..why i get this error im sure all my field name is right .. thank you for the help

what's the meaning of .state? because, when i use your code, error.. With frmCustomers .State = gModule.FormState.adStateEditMode .CustomerID = CustomerID .ShowDialog() Call FillList()

Dear : if Current Cell is empty in Access and we want to drage automatically Last Value in the next Row ? Plz share with me on my id . [email protected]

i want to ask you. on how to connect from one page to another page using php. thanks

hi i am new in php, i need help on my project; I want if i select a picture from a list and show detailes of that picture and for the following person viewing the list not to be able to select the selected picture again. Please help!

Add new comment