Showing posts with label crud in visual basic net and ms access. Show all posts
Showing posts with label crud in visual basic net and ms access. Show all posts

Sunday, June 18, 2017

Address Book in Visual Basic NET and MS Access

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