Imports System.Data            '<----------explained above
Imports System.Data.OleDb      '<----------explained above

Public Class frmLogIn

'as the form loads a connection to the database is set
    ' Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
        ' provider is where you specify the type of database you are connecting to, in this case we use "Microsoft.ACE.OLEDB.12.0" for Access 2007
        ' source is where you specify the location of the database.For an explanation of |DataDirectory please see above

'The connection must then be opened
    ' conn.Open()
    ' I leave the connection open while I'm working with the data, it is not neccessary but but be aware that it can cause some problems if you close it     '

prematurely, such as:
    '     If you run commands or call methods of the SqlCommand or OleDbCommand object, you receive the following error message if a connection is not open:
    '     An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
    '    see http://support.microsoft.com/kb/311544 for more info

'You can then create a DataAdapter and fill it with whatever data you require(brush up on your SQL commands for this). In the example I just select everything
'Next you take the information from the dataadapter and dispose of the adapter.
    'Dim myadapter As New OleDbDataAdapter("SELECT * FROM tblEmployee", conn)
    'Dim dtset As New DataSet
        'myadapter.Fill(dtset)
        'myadapter.Dispose()

'From the dataset you can now create a datatable. The datatable basically represents a specific table from your database(trivial now because we only have one table
' but it makes things easier when working with larger projects)
'You have to specify where in the dataset the table is stored, the index starts at zero, so the first table in your database is found at position 0
    'Dim table As DataTable = dtset.Tables(0)

'Next you have to set the datasource of the comboBox(that is where it is getting it's data from) and then specify which column you want to display(The column name is

' the name you created in the database itself
    'Combo1.DataSource = table
        'Combo1.DisplayMember = "EmpID"

'Finally you must close the connection.This is important as it can cause problems if you leave the connection open, such as preventing other users from accessing the

'database
    'conn.Close()

'I set the text of the comboBox to "" for asthetic reasons only

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
        conn.Open()
        Dim myadapter As New OleDbDataAdapter("SELECT * FROM tblEmployee", conn)
        Dim dtset As New DataSet
        myadapter.Fill(dtset)
        myadapter.Dispose()

        Dim table As DataTable = dtset.Tables(0)

        Combo1.DataSource = table
        Combo1.DisplayMember = "EmpID"



        conn.Close()
        
    Combo1.Text = ""
    End Sub


'Once the user has select a user name and entered a password, you must check that the two match up.

    Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
    
'Checks that the user has indeed entered a username and password
        If Combo1.Text = "" Then
            MsgBox("You need to enter your name to log in")
            Exit Sub
        ElseIf mtxtPassword.Text = "" Then
            MsgBox("Your password is required to log in")
            Exit Sub
        End If

'Opens the connection, creates an adapter, this time using the WHERE clause to retrieve only those records whose EmpPassword field matches. In this case there should

'only be one,this is obviously a REALLY REALLY simplified non-effective way of doing this, remember this is a small project you can build in complexity as you feel

'more comfortable.
'Notice the use of the ' around the inclusion of the mtxtPassword.text. ' is used whenever you include a string literal in your SQL statement.
' see http://www.unix.com.ua/orelly/oracle/prog2/ch02_03.htm for more info
'If EmpPassword was a number you would change it to
'    '(EmpPassword=" + mtxtPassword.Text + ")"    notice the lack of '


        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
        conn.Open()
        Dim myadapter As New OleDbDataAdapter("Select EmpPassword FROM tblEmployee WHERE (EmpID='" + combo1.Text + "')", conn)
        Dim dtset As New DataSet
        myadapter.Fill(dtset)
        myadapter.Dispose()

        Dim table As DataTable = dtset.Tables(0)
      
        conn.Close()


'Here I make use of the datatable to compare a value in the textbox to the actual value stored in the database. Remember that the index starts at zero, so the first

'record of the first column will be  
    'table.Rows(0).Item(0)

        If mtxtPassword.Text <> table.Rows(0).Item(0) Then
            MsgBox("The password you have entered is incorrect")
            Exit Sub
        Else
            frmMain.Show()
            Me.Close()

        End If






    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

  
End Class

Imports System.Data
Imports System.Data.OleDb

Public Class frmAddUsers

'Takes the user back to the main form

    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
        frmMain.Show()
        Me.Close()

    End Sub


'Once the user has added the new employee details they click confirm
    Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
      
'This first part is a check to see that the user didn't accidently click confirm
     Dim result As MsgBoxResult
        MsgBox("Confirm", MsgBoxStyle.OkCancel, "Confirm User Addition")
        If result = MsgBoxResult.Cancel Then
            Exit Sub

        End If

'This part checks to see that no information has been left out

        If txtEmpID.Text = "" Then
            MsgBox("The employee's ID is required")
            Exit Sub
        ElseIf txtEmpName.Text = "" Then
            MsgBox("The employee's name is required")
            Exit Sub
        ElseIf txtEmpPassword.Text = "" Then
            MsgBox("The employee's password is required")
            Exit Sub
        End If


'Once again a connection is required and opened
        Try
            Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
            conn.Open()

'Heres where the difference lies.
'Instead of retrieving data from the database we are now putting data back.
' I store the sql command in a string because to illustrate that this can be done and for me it makes it a bit easier to read here
' This is not a tutorial on all things SQL. There are plenty of books written on the subject, so I realise these commands are simplistic but they are just to '

demonstrate the basics on how it is done.

            Dim addUser As String = "INSERT INTO tblEmployee ( EmpID, EmpName, EmpPassword ) VALUES (" + txtEmpID.Text + ",'" + txtEmpName.Text + "','" +         

txtEmpPassword.Text + "')"

'Because I am using an Access back, it needs to be and oledbcommand. If you want to have a look at a basic SQL version
' program you can look here http://www.exforsys.com/images/vbnet/sourecode/vbnet_t7_sample1.html

'To use a OleDBCommand you have first specify the SQL string that you wish to execute(addUser in this case) and then you have to specify which connection you want to

'use(conn)

            Dim cmd As OleDbCommand = New OleDbCommand(addUser, conn)
            
'Then you have to tell the command to run(executeNonQuery basically just means "Go")
            cmd.ExecuteNonQuery()

'If everything goes according to plan, just let the user know
            MsgBox("User added successfully")

            conn.Close()

'Basic error catching
        Catch ex As Exception
            MsgBox(ex.Message.ToString, , "Addition Error")

        End Try


    End Sub


'Clears the form, ready for the next input
    Private Sub btnAddNewEmp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNewEmp.Click
        txtEmpID.Text = ""
        txtEmpName.Text = ""
        txtEmpPassword.Text = ""
    End Sub
End Class

Imports System.Data
Imports System.Data.OleDb

Public Class frmModifyUser

    
    Private Sub frmModifyUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
'Used to populate the comboBox
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
        conn.Open()

        Dim myadapter As New OleDbDataAdapter("SELECT * FROM tblEmployee", conn)

        Dim dtset As New DataSet
        myadapter.Fill(dtset)
        myadapter.Dispose()

        Dim table As DataTable = dtset.Tables(0)

        cboEmployee.DataSource = table
        cboEmployee.DisplayMember = "EmpID"



        conn.Close()


    End Sub

Private Sub cboEmployee_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboEmployee.SelectedIndexChanged
'calls populatefields to setup data in the textboxes

            populateFields()
    End Sub


'This method is used to set the information in the textboxes according to which employee has been selected in the combobox

Private Sub populateFields()
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
        conn.Open()

        Dim userDetailsAdapter As New OleDbDataAdapter("SELECT * FROM tblEmployee where EmpID =" + cboEmployee.Text, conn)
        Dim userDetailsDataSet As New DataSet
        userDetailsAdapter.Fill(userDetailsDataSet)
        userDetailsAdapter.Dispose()
        Dim userDetailsTable As DataTable = userDetailsDataSet.Tables(0)

        txtEmpID.Text = userDetailsTable.Rows(0).Item(0)
        txtEmpName.Text = userDetailsTable.Rows(0).Item(1)
        txtEmpPassword.Text = userDetailsTable.Rows(0).Item(2)

        conn.Close()
    End Sub

Private Sub btnConfirmEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirmEdit.Click
        'Normal check
    Dim confirm As MsgBoxResult
        confirm = MsgBox("Confirm changes?", MsgBoxStyle.OkCancel, "Confirm Edit")

        If confirm = MsgBoxResult.Ok Then
            'Update records

            Try
                Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
                conn.Open()
'Update the employees details where the employee ID in the database matches the employee ID in the combobox
' As illustrated in the example you can specify which fields in the database you want changed. Remember to pay attention to your usage of the ' ' around string '

literals. I know I've been caught out and had to spend ages trying to find the problem because i wasn't paying attention
                Dim updateString As New OleDbCommand("UPDATE tblEmployee SET EmpID = " + txtEmpID.Text + ", EmpName ='" + txtEmpName.Text + "', EmpPassword + '" +     

        txtempPassword + "'  WHERE EmpID = " + cboEmployee.Text, conn)


                updateString.ExecuteNonQuery()

                conn.Close()

                
            Catch ex As Exception
                MsgBox(ex.Message.ToString, , "Edit Error")
            End Try
          
        Else
            Exit Sub

        End If
    End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

        Dim result As MsgBoxResult
        result = MsgBox("Confirm deletion of this user?", MsgBoxStyle.OkCancel, "Delete User")
        If result = MsgBoxResult.Ok Then

            'Delete Record
            Try

                Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
                conn.Open()
                
'Simply state what record you wish to delete,specified with the WHERE clause.
' Here I delete every record where the EmpID in the database matches the EmpID in the comboBox
                Dim deleteUserString As New OleDbCommand("DELETE FROM tblEmployee WHERE EmpID = " + cboEmployee.Text, conn)


                deleteUserString.ExecuteNonQuery()

                conn.Close()

                MsgBox("User deleted", , "Confirmation")

                comboLoad()
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
            End Try
        End If
    End Sub

Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Tables\Employees.accdb")
        conn.Open()
        Dim myadapter As New OleDbDataAdapter("SELECT * FROM tblEmployee", conn)
        Dim dtset As New DataSet
        myadapter.Fill(dtset)
        myadapter.Dispose()

        Dim table As DataTable = dtset.Tables(0)

        Combo1.DataSource = table
        Combo1.DisplayMember = "EmpID"



        conn.Close()

Dim addUser As String = "INSERT INTO tblEmployee ( EmpID, EmpName, EmpPassword ) VALUES (" + txtEmpID.Text + ",'" + txtEmpName.Text + "','" +         txtEmpPassword.Text + "')"

Dim updateString As New OleDbCommand("UPDATE tblEmployee SET EmpID = " + txtEmpID.Text + ", EmpName ='" + txtEmpName.Text + "', EmpPassword + '" +     
txtempPassword + "'  WHERE EmpID = " + cboEmployee.Text, conn)

Dim deleteUserString As New OleDbCommand("DELETE FROM tblEmployee WHERE EmpID = " + cboEmployee.Text, conn)