'''''''''''''''''''''''''''''''''''''
' Craig Persiko
' CS 112D Programming lab 2 Solution
' Allows user to enter data for new Employee records. 
' New departments are created as needed, as well.
'''''''''''''''''''''''''''''''''''''

Option Strict On
Imports System.Data.SqlClient

Public Class frmEmployeeEntry
    Inherits System.Windows.Forms.Form

    Private conNovelty As SqlConnection

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents txtSalary As System.Windows.Forms.TextBox
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents txtDeptId As System.Windows.Forms.TextBox
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents txtLastName As System.Windows.Forms.TextBox
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents txtFirstName As System.Windows.Forms.TextBox
    Friend WithEvents btnSave As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label4 = New System.Windows.Forms.Label()
        Me.txtSalary = New System.Windows.Forms.TextBox()
        Me.Label5 = New System.Windows.Forms.Label()
        Me.txtDeptId = New System.Windows.Forms.TextBox()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.txtLastName = New System.Windows.Forms.TextBox()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.txtFirstName = New System.Windows.Forms.TextBox()
        Me.btnSave = New System.Windows.Forms.Button()
        Me.SuspendLayout()
        '
        'Label4
        '
        Me.Label4.Location = New System.Drawing.Point(200, 48)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(48, 23)
        Me.Label4.TabIndex = 29
        Me.Label4.Text = "Salary:"
        '
        'txtSalary
        '
        Me.txtSalary.Location = New System.Drawing.Point(256, 48)
        Me.txtSalary.Name = "txtSalary"
        Me.txtSalary.Size = New System.Drawing.Size(88, 20)
        Me.txtSalary.TabIndex = 28
        Me.txtSalary.Text = ""
        '
        'Label5
        '
        Me.Label5.Location = New System.Drawing.Point(8, 48)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(88, 23)
        Me.Label5.TabIndex = 27
        Me.Label5.Text = "Department ID:"
        '
        'txtDeptId
        '
        Me.txtDeptId.Location = New System.Drawing.Point(104, 48)
        Me.txtDeptId.Name = "txtDeptId"
        Me.txtDeptId.Size = New System.Drawing.Size(64, 20)
        Me.txtDeptId.TabIndex = 26
        Me.txtDeptId.Text = ""
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(184, 16)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(64, 23)
        Me.Label2.TabIndex = 25
        Me.Label2.Text = "Last Name:"
        '
        'txtLastName
        '
        Me.txtLastName.Location = New System.Drawing.Point(256, 16)
        Me.txtLastName.Name = "txtLastName"
        Me.txtLastName.Size = New System.Drawing.Size(88, 20)
        Me.txtLastName.TabIndex = 24
        Me.txtLastName.Text = ""
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(8, 16)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(64, 23)
        Me.Label1.TabIndex = 23
        Me.Label1.Text = "First Name:"
        '
        'txtFirstName
        '
        Me.txtFirstName.Location = New System.Drawing.Point(80, 16)
        Me.txtFirstName.Name = "txtFirstName"
        Me.txtFirstName.Size = New System.Drawing.Size(88, 20)
        Me.txtFirstName.TabIndex = 22
        Me.txtFirstName.Text = ""
        '
        'btnSave
        '
        Me.btnSave.Location = New System.Drawing.Point(136, 80)
        Me.btnSave.Name = "btnSave"
        Me.btnSave.TabIndex = 30
        Me.btnSave.Text = "Save "
        '
        'frmEmployeeEntry
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(352, 110)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnSave, Me.Label4, Me.txtSalary, Me.Label5, Me.txtDeptId, Me.Label2, Me.txtLastName, Me.Label1, Me.txtFirstName})
        Me.Name = "frmEmployeeEntry"
        Me.Text = "Employee Input"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmEmployeeEntry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Create an instance of a Connection object
        conNovelty = New SqlConnection( _
                    "data source=L-BAT424-CPERSI\SQLEXPRESS;initial catalog=novelty;" & _
                    "integrated security=SSPI;persist security info=False;" & _
                    "workstation id=L-BAT424-CPERSI;packet size=4096")
        conNovelty.Open()
    End Sub

    Private Sub frmEmployeeEntry_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        conNovelty.Close()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim EmpDeptTrans As SqlTransaction
        Dim cmd As SqlCommand = New SqlCommand()
        Dim result As Integer

        EmpDeptTrans = conNovelty.BeginTransaction()
        ' Set command's connection and command text
        cmd.Connection = conNovelty
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "InsertEmployeeOrg"
        cmd.Transaction = EmpDeptTrans

        'Create input parameters and set values:
        cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.VarChar, 50))
        cmd.Parameters("@FirstName").Value = txtFirstName.Text
        cmd.Parameters("@FirstName").Direction = ParameterDirection.Input

        cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.VarChar, 70))
        cmd.Parameters("@LastName").Value = txtLastName.Text
        cmd.Parameters("@LastName").Direction = ParameterDirection.Input

        cmd.Parameters.Add(New SqlParameter("@DepartmentID", SqlDbType.Int))
        cmd.Parameters("@DepartmentID").Value = CInt(txtDeptId.Text)
        cmd.Parameters("@DepartmentID").Direction = ParameterDirection.Input

        cmd.Parameters.Add(New SqlParameter("@Salary", SqlDbType.Money))
        cmd.Parameters("@Salary").Value = CDbl(txtSalary.Text)
        cmd.Parameters("@Salary").Direction = ParameterDirection.Input

        ' We need to put the code inside a Try-Catch block
        ' since a failed command ALSO generates a run-time error
        Try
            result = cmd.ExecuteNonQuery()
            EmpDeptTrans.Commit()
        Catch ex As Exception
            Dim DeptId As Integer
            DeptId = checkAddDepartment(CInt(txtDeptId.Text), ex, EmpDeptTrans)
            If DeptId > 0 Then
                ' department has been added, so now add employee
                Try
                    cmd.Parameters("@DepartmentID").Value = DeptId
                    result = cmd.ExecuteNonQuery()
                    EmpDeptTrans.Commit()
                Catch newEx As Exception
                    result = 0
                    EmpDeptTrans.Rollback()
                End Try
            Else
                result = 0
                EmpDeptTrans.Rollback()
            End If
        End Try

        ' Show results of command execution
        If result = 0 Then
            MsgBox("Command execution failed")
        Else
            MsgBox("Employee record added successfully.")
        End If
        Me.txtDeptId.Clear()
        Me.txtFirstName.Clear()
        Me.txtLastName.Clear()
        Me.txtSalary.Clear()
    End Sub

    Function checkAddDepartment(ByVal deptId As Integer, ByVal execException As Exception, _
                                ByVal EmpDeptTrans As SqlTransaction) As Integer
        Dim cmd As SqlCommand = New SqlCommand()
        Dim result As Integer = 0
        Dim sql As String = "SELECT DepartmentName FROM tblDepartment WHERE ID = " & CStr(deptId)
        Dim newDeptName As String
        Dim existingDeptName As Object

        Try
            cmd.Connection = conNovelty
            cmd.CommandType = CommandType.Text
            cmd.Transaction = EmpDeptTrans
            cmd.CommandText = sql
            existingDeptName = cmd.ExecuteScalar()
            If existingDeptName Is Nothing Then  'nothing returned from select query: department doesn't exist
                ' add record to tblDepartment
                newDeptName = InputBox("You entered a nonexistant Department ID: " & CStr(deptId) & _
                                       ". Enter the name of the new department you want created.")
                If newDeptName = "" Then
                    MsgBox("Employee table insertion canceled")
                Else
                    cmd.CommandText = "INSERT INTO tblDepartment(DepartmentName) VALUES ('" & _
                                    newDeptName & "')"
                    result = cmd.ExecuteNonQuery()
                    cmd.CommandText = "SELECT ID FROM tblDepartment WHERE DepartmentName = '" & _
                                      newDeptName & "'"
                    result = CInt(cmd.ExecuteScalar())
                    MsgBox("Department ID " & CStr(result) & " created with Department Name " & newDeptName)
                End If
            Else ' Dept Id already exists.  Other problem:
                MsgBox("Error inserting data into tblEmployee: " & execException.Message)
            End If

        Catch e As Exception
            result = 0
        End Try
        Return result
    End Function
End Class