Here is my first time to create a complete CRUD application written in Microsoft Visual Basic NET and Microsoft Access I called this program Address Book application to store information and contact numbers of the persons. The code is very easy to understand and use. I use Microsoft Visual Studio 2012 Ultimate Edition and Microsoft Access 2010. I hope you will find my work useful. Thank you.
My email address are the following jakerpomperada@gmail.com and jakerpomperada@yahoo.com.
My mobile number here in the Philippines is 09173084360.
Sample Program Output
Program Listing
Imports System.Data.OleDb
Imports System.IO
Public Class Form1
Dim strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\vbnet_address\addressbook.accdb;Persist Security Info=False;"
Dim con As New OleDbConnection
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable
Dim save_tag As String
Sub Fill_Grid(grid As Windows.Forms.DataGridView)
Dim x As Integer
If grid.Rows.Count > 0 Then
While x < grid.Rows.Count
grid.Rows.RemoveAt(x)
End While
End If
With grid
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
.RowHeadersVisible = False
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
End With
con.ConnectionString = strConn
con.Open()
With cmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM addressbook"
End With
da.SelectCommand = cmd
grid.DataSource = dt
da.Fill(dt)
For i As Integer = 0 To 0
grid.Columns(i).Visible = False
Next
grid.Columns(0).Visible = False
con.Dispose()
con.Close()
End Sub
Sub save(ByVal tag As String)
Dim cmdtxt As String = Nothing
If tag = "new" Then
cmdtxt = "INSERT INTO addressbook ([personsname], [address], [telephone], [mobile], [email]) " & _
"Values('" & UCase(TextBox1.Text) & "','" & UCase(TextBox2.Text) & "','" & UCase(TextBox3.Text) & "','" & UCase(TextBox4.Text) & "','" & LCase(TextBox5.Text) & "' ) "
ElseIf tag = "edit" Then
cmdtxt = "UPDATE addressbook SET " & _
"[personsname] = '" & UCase(TextBox1.Text.ToString) & "',[address] = '" & UCase(TextBox2.Text) & "', [telephone] = '" & UCase(TextBox3.Text) & "' , [mobile] ='" & UCase(TextBox4.Text) & "' ,[email]='" & LCase(TextBox5.Text) & "' " & _
"WHERE ID = " & TextBox6.Text & ""
End If
Try
con.ConnectionString = strConn
con.Open()
With cmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = cmdtxt
End With
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, vbCritical)
Finally
con.Dispose()
con.Close()
End Try
End Sub
Sub delete(ByVal id As Integer)
Dim cmdtxt As String = Nothing
cmdtxt = "DELETE FROM addressbook WHERE ID = " & id & ""
Try
con.ConnectionString = strConn
con.Open()
With cmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = cmdtxt
End With
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, vbCritical)
Finally
con.Dispose()
con.Close()
End Try
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Label8.Text = DateTime.Now.ToString()
TextBox6.Visible = False
save_tag = "new"
Fill_Grid(DataGridView1)
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
TextBox1.Text = String.Empty
TextBox2.Text = String.Empty
TextBox3.Text = String.Empty
TextBox4.Text = String.Empty
TextBox5.Text = String.Empty
TextBox1.Focus()
save_tag = "new"
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
save(save_tag)
Button1.PerformClick()
Fill_Grid(DataGridView1)
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
Dim id As Integer
id = Val(TextBox6.Text)
delete(id)
Button1.PerformClick()
Fill_Grid(DataGridView1)
End Sub
Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
TextBox1.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value.ToString
TextBox2.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value.ToString
TextBox3.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value.ToString
TextBox4.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value.ToString
TextBox5.Text = DataGridView1.Rows(e.RowIndex).Cells(5).Value.ToString
TextBox6.Text = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString
save_tag = "edit"
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Button1.PerformClick()
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
If MsgBox("Are you sure you want to quit?", MsgBoxStyle.YesNo Or MsgBoxStyle.DefaultButton2, "Close application") = Windows.Forms.DialogResult.Yes Then
Me.Close()
End If
TextBox1.Focus()
End Sub
End Class
