Database Operations (CRUD) Using OleDB in vb.net

In this post, you will learn how to use OleDb to:

• Connect to data source
• Add records to database
• Modify an existing record
• Read the desired record
• Read all records
• Delete a particular record
• Delete all records from database

Imports System.Data.OleDb

Dim conString As String

Dim con As OleDbConnection

Dim dr As OleDbDataReader

//CONNECTION

conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=w:\newdata.mdb"

con = New OleDbConnection(conString)

con.Open()

//ADD

Dim strSql As String

Try

strSql = "insert into books values('" & tbBook.Text & "','" & tbAuthor.Text & "')"

Dim cmd As OleDbCommand = New OleDbCommand(strSql, con)

Dim icount As Integer = cmd.ExecuteNonQuery

If icount > 0 Then

MsgBox(tbBook.Text & " book added to your library")

DB2List(lstBooks)

Else

MsgBox(tbBook.Text & " cannot be added to your library")

End If

Catch ex1 As Exception

MsgBox(ex1.Message)

End Try

//MODIFY

Dim strSql As String

Try

strSql = "update books set author = '" & tbAuthor.Text & "' where book = '" & tbBook.Text & "'"

Dim cmd As OleDbCommand = New OleDbCommand(strSql, con)

Dim icount As Integer = cmd.ExecuteNonQuery

If icount > 0 Then

MsgBox(tbBook.Text & " author updated")

DB2List(lstBooks)

Else

MsgBox(tbBook.Text & " author cannot be updated")

End If

Catch ex1 As Exception

MsgBox(ex1.Message)

End Try

//DELETE

Dim selItem As String

Dim strSql As String

For i = 0 To lstBooks.Items.Count - 1

If lstBooks.GetSelected(i) Then

selItem = lstBooks.GetItemText(lstBooks.Items(i))

End If

Next

strSql = "delete from books where book='" & selItem & "'"

Dim cmd As OleDbCommand = New OleDbCommand(strSql, con)

Dim icount As Integer = cmd.ExecuteNonQuery

If icount > 0 Then

MsgBox(selItem & " deleted sucessfully")

DB2List(lstBooks)

Else

MsgBox(selItem & " cannot be deleted")

End If

//DELETE ALL

Dim strSql As String

strSql = "delete * from books"

Dim cmd As OleDbCommand = New OleDbCommand(strSql, con)

Dim icount As Integer = cmd.ExecuteNonQuery

If icount > 0 Then

MsgBox("All books deleted successfully")

DB2List(lstBooks)

Else

MsgBox("All books cannot be deleted")

End If

//READ ALL

Private Sub DB2List(ByRef lb As ListBox)

Dim strSql As String = "select * from books"

Dim dadapter As OleDbDataAdapter

dadapter = New OleDbDataAdapter()

dadapter.SelectCommand = New OleDbCommand(strSql, con)

Dim dset As DataSet = New DataSet()

dadapter.Fill(dset, "Books")

lb.DataSource = dset

lb.DisplayMember = "Books.Book"

End Sub

//READ SELECTED

Private Sub RetrievConditional(ByVal bk As String)

Dim strSql As String = "select * from books where book = '" & bk & "'"

Dim cmd As OleDbCommand = New OleDbCommand(strSql, con)

dr = cmd.ExecuteReader

While dr.Read()

tbBook.Text = dr(0)

tbAuthor.Text = dr(1)

End While

End Sub

Code is self explanatory...