Thursday, 8 December 2016

Microsoft Access Database Tutorial

Microsoft Access Database Tutorial

Microsoft Access is database package which is suitable for small scale project for single PC Application. Most Fundamental of any project which you should learn before start your project is database operation (insert ,update,Delete and show). This tutorial help you to learn database operation with full source code . So enjoy programming with Access.

Microsoft Access Database Insert Update Delete



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            com.Connection = con
            con.Open()
            fill_grid()
            lblid.Text = ""
        Catch ex As Exception
            MsgBox("Loading Error" & vbCrLf & ex.Message)
        End Try
    End Sub

Save Button Code

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If TextBox2.Text = "" Or TextBox3.Text = "" Then
            MsgBox("Please fill all field properly")
        Else
            com.CommandText = "insert into stu (sname,city) values('" & TextBox2.Text & "','" & TextBox3.Text & "')"
            com.ExecuteNonQuery()
            MsgBox("Saved")
            clearform()
            fill_grid()
        End If

    End Sub

Update Button Code

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        If lblid.Text = "" Then
            MsgBox("Please select a row from gridview then click update button")
        Else
            com.CommandText = "update stu set sname='" & TextBox2.Text & "',city='" & TextBox3.Text & "' where roll=" & Val(lblid.Text)
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
            fill_grid()
            lblid.Text = ""
        End If
    End Sub

Delete Button Code

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If lblid.Text = "" Then
            MsgBox("Please select a row from Gridview then try to Delete")
        Else
            If (MsgBox("Are you sure to delete this record", MsgBoxStyle.YesNo) = MsgBoxResult.Yes) Then
                Try
                    com.CommandText = "delete from stu where roll=" & Val(lblid.Text)
                    com.ExecuteNonQuery()
                    MsgBox("Record Deleted")
                    fill_grid()
                    lblid.Text = ""
                Catch ex As Exception
                    MsgBox("Unable to delete try after some time " & vbCrLf & ex.Message)
                End Try

            End If
          

        End If
    End Sub
Private Sub DataGridView1_RowHeaderMouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseClick
        Dim dr As DataGridViewSelectedRowCollection
        dr = DataGridView1.SelectedRows()
        lblid.Text = dr.Item(0).Cells(0).Value.ToString()
        TextBox2.Text = dr.Item(0).Cells(1).Value.ToString()
        TextBox3.Text = dr.Item(0).Cells(2).Value.ToString()
    End Sub

Functions

Sub clearform()
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox2.Focus()
    End Sub
    Sub fill_grid()
        Dim da As New OleDbDataAdapter("select * from stu", con)
        Dim ds As New DataSet
        da.Fill(ds, "My_table_temp_name")
        DataGridView1.DataSource = ds.Tables("My_table_temp_name")
        lblRecord_Count.Text = DataGridView1.RowCount
    End Sub