Promote Your Blog
Google

Friday, March 2, 2012

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...

7 comments:

  1. Dear kindley tell mein that whether i will put the coulumn name and cell name where i want to insert the data ... in insertion section of this code... plz rply me sooon...

    ReplyDelete
  2. For inserting into certain columns of a table only, construct a string with following syntax:

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

    Hope this is what you were asking for & the answer resolves your query ..

    ReplyDelete
  3. This database is compatible with .NET, Silverlight, Windows Phone, Mono, Monodroid, and Monotouch:
    http://www.kellermansoftware.com/p-43-ninja-net-database-pro.aspx

    ReplyDelete
  4. why i cannot do insert in database
    they show syntax error insert into

    ReplyDelete
  5. Download Demo Application to Delete data from Access Database with code

    http://geeksprogrammings.blogspot.in/2013/09/delete-record-from-access-database.html

    ReplyDelete
  6. This is simple and straight forward example for developers, it worked for me

    ReplyDelete