Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class Frmmain
    Dim constr As New OleDbConnection
    Dim da As OleDbDataAdapter
    Dim dS As New DataSet
    Dim a As Integer = 0
    Dim dBind As New BindingSource
    Private Sub Frmmain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call md_settings()
        Call md_fillevents()
    End Sub
    Private Sub md_settings()

        Try
            Dim ConnString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
            Dim myConnection As OleDbConnection = New OleDbConnection
            myConnection.ConnectionString = ConnString
            myConnection.Open()

            Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select * from emp_mstr", ConnString)
            dS = New DataSet
            da.Fill(dS, "emp")
            dBind.DataSource = dS
            DataGridView1.DataSource = dS
            dBind.DataMember = dS.Tables(0).ToString()
            DataGridView1.DataSource = dBind
        Catch ex As Exception

        End Try
    End Sub
    Private Sub cmdadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdadd.Click
        Try
            Dim ConnString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
            Dim myConnection As OleDbConnection = New OleDbConnection
            myConnection.ConnectionString = ConnString
            Dim dsinvoice As New DataSet
            Dim dainvoice As New OleDbDataAdapter
            Dim sql As String = "insert into emp_mstr(emp_no, fname, mname, lname, dept_no, desig) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "')"
            myConnection.Open()
            Dim com As New OleDbCommand
            Dim dbread
            com = New OleDbCommand(sql, myConnection)
            dbread = com.ExecuteReader()
            'com.ExecuteNonQuery()
            MsgBox("Records Inserted Successfully...")
            Call md_settings()
        Catch
            MsgBox("Records not Inserted...")
        End Try
    End Sub
    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(0).Value) Then
            TextBox1.Text = DataGridView1.CurrentRow.Cells(0).Value
        Else
            TextBox1.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(1).Value) Then
            TextBox2.Text = DataGridView1.CurrentRow.Cells(1).Value
        Else
            TextBox2.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(2).Value) Then
            TextBox3.Text = DataGridView1.CurrentRow.Cells(2).Value
        Else
            TextBox3.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(3).Value) Then
            TextBox4.Text = DataGridView1.CurrentRow.Cells(3).Value
        Else
            TextBox4.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(4).Value) Then
            TextBox5.Text = DataGridView1.CurrentRow.Cells(4).Value
        Else
            TextBox5.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(5).Value) Then
            TextBox6.Text = DataGridView1.CurrentRow.Cells(5).Value
        Else
            TextBox6.Text = ""
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim ConnString As String = System.Configuration.ConfigurationSettings.AppSettings("DSN")
            Dim myConnection As OleDbConnection = New OleDbConnection
            myConnection.ConnectionString = ConnString
            Dim dsinvoice As New DataSet
            Dim dainvoice As New OleDbDataAdapter
            Dim sql As String = "update emp_mstr set emp_no='" & TextBox1.Text & "',fname='" & TextBox2.Text & "',mname='" & TextBox3.Text & "',lname='" & TextBox4.Text & "',dept_no='" & TextBox5.Text & "',desig='" & TextBox6.Text & "' WHERE EMP_NO='" & TextBox1.Text & "'"
            myConnection.Open()
            Dim com As New OleDbCommand
            Dim dbread
            com = New OleDbCommand(sql, myConnection)
            dbread = com.ExecuteReader()
            MsgBox("Records Updated")
            Call md_settings()
        Catch
        End Try
    End Sub
    Private Sub md_fillevents()

        Try
            TextBox1.Text = dS.Tables(0).Rows(a).Item(0)
            TextBox2.Text = dS.Tables(0).Rows(a).Item(1)
            TextBox3.Text = dS.Tables(0).Rows(a).Item(2)
            TextBox4.Text = dS.Tables(0).Rows(a).Item(3)
            TextBox5.Text = dS.Tables(0).Rows(a).Item(4)
            TextBox6.Text = dS.Tables(0).Rows(a).Item(5)
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

    End Sub

    Private Sub cmdfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdfirst.Click
        Try
            a = 0
            Call md_fillevents()
        Catch ex As Exception

        End Try

    End Sub

    Private Sub cmdprior_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdprior.Click
        Try
            If (Not a = 0) Then
                a -= 1
                Call md_fillevents()
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmdnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdnext.Click
        Try
            If (Not a = dS.Tables(0).Rows.Count - 1) Then
                a += 1
                Call md_fillevents()
            End If
        Catch ex As Exception

        End Try

    End Sub

    Private Sub cmdlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdlast.Click
        Try
            If (Not a = dS.Tables(0).Rows.Count - 1) Then
                a = dS.Tables(0).Rows.Count - 1
                Call md_fillevents()
            End If
        Catch ex As Exception

        End Try

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(0).Value) Then
            TextBox1.Text = DataGridView1.CurrentRow.Cells(0).Value
        Else
            TextBox1.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(1).Value) Then
            TextBox2.Text = DataGridView1.CurrentRow.Cells(1).Value
        Else
            TextBox2.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(2).Value) Then
            TextBox3.Text = DataGridView1.CurrentRow.Cells(2).Value
        Else
            TextBox3.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(3).Value) Then
            TextBox4.Text = DataGridView1.CurrentRow.Cells(3).Value
        Else
            TextBox4.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(4).Value) Then
            TextBox5.Text = DataGridView1.CurrentRow.Cells(4).Value
        Else
            TextBox5.Text = ""
        End If
        If Not IsDBNull(DataGridView1.CurrentRow.Cells(5).Value) Then
            TextBox6.Text = DataGridView1.CurrentRow.Cells(5).Value
        Else
            TextBox6.Text = ""
        End If

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.TextBox1.Text = ""
        Me.TextBox2.Text = ""
        Me.TextBox3.Text = ""
        Me.TextBox4.Text = ""
        Me.TextBox5.Text = ""
        Me.TextBox6.Text = ""
        Me.TextBox1.Focus()
    End Sub
End Class