Employee Class


Public Function GetEmployee(EmpID)
    Dim Conn As New ADODB.Connection
    If IsNumeric(EmpID) = False Then
        Err.Raise vbObjectError + 1, "SampleServer", _
            "Employee ID must be passed as a number!"
    End If
    Conn.Open "EmpsDatabase", "Admin", ""
    Set GetEmployee = Conn.Execute("select * from tblEmps where " _
        & "EmpID = " & EmpID)
    If GetEmployee.EOF Then
        Err.Raise vbObjectError + 2, "SampleServer", _
            "No employee record was found!"
    End If
End Function

Public Function AddEmployee(LastName, FirstName, DepartmentID, Salary, BirthDate)
If IsNumeric(DepartmentID) = False Then
    Err.Raise vbObjectError + 3, "SampleServer", _
        "Department ID must be passed as a number!"
ElseIf IsNumeric(Salary) = False Then
    Err.Raise vbObjectError + 4, "SampleServer", _
        "Salary must be passed as a number!"
ElseIf IsDate(BirthDate) = False Then
    Err.Raise vbObjectError + 5, "SampleServer", _
        "BirthDate must be passed as a date!"
Else
    Dim Conn As New ADODB.Connection
    Dim RSEmpID As ADODB.Recordset
    Conn.Open "EmpsDatabase", "Admin", ""
    Conn.Execute "insert into tblEmps (LastName, FirstName, DepartmentID, " _
        & "Salary, BirthDate) values (" _
        & "'" & LastName & "', " _
        & "'" & FirstName & "', " _
        & DepartmentID & ", " _
        & Salary & ", " _
        & "'" & BirthDate & "')"
    Set RSEmpID = Conn.Execute("select Max(EmpID) as MaxID from tblEmps")
    AddEmployee = RSEmpID("MaxID")
End If
End Function

Public Sub EditEmployee(EmpID, LastName, FirstName, DepartmentID, Salary, BirthDate)
If IsNumeric(EmpID) = False Then
    Err.Raise vbObjectError + 1, "SampleServer", _
        "Employee ID must be passed as a number!"
ElseIf IsNumeric(DepartmentID) = False Then
    Err.Raise vbObjectError + 3, "SampleServer", _
        "Department ID must be passed as a number!"
ElseIf IsNumeric(Salary) = False Then
    Err.Raise vbObjectError + 4, "SampleServer", _
        "Salary must be passed as a number!"
ElseIf IsDate(BirthDate) = False Then
    Err.Raise vbObjectError + 5, "SampleServer", _
        "BirthDate must be passed as a date!"
Else
    Dim Conn As New ADODB.Connection
    Dim RSEmpID As ADODB.Recordset
    Conn.Open "EmpsDatabase", "Admin", ""
    Set RSEmpID = Conn.Execute("select EmpID from tblEmps where " _
        & "EmpID = " & EmpID)
    If RSEmpID.EOF Then
        Err.Raise vbObjectError + 2, "SampleServer", _
            "No such employee record was found to edit!"
    End If
    Conn.Execute "Update tblEmps set " _
        & "LastName = '" & LastName & "', " _
        & "FirstName = '" & FirstName & "', " _
        & "DepartmentID = " & DepartmentID & ", " _
        & "Salary = " & Salary & ", " _
        & "BirthDate = '" & BirthDate & "' " _
        & "where EmpID = " & EmpID
End If
End Sub


Public Sub DeleteEmployee(EmpID)
    Dim Conn As New ADODB.Connection
    If IsNumeric(EmpID) = False Then
        Err.Raise vbObjectError + 1, "SampleServer", _
            "Employee ID must be passed as a number!"
    End If
    Conn.Open "EmpsDatabase", "Admin", ""
    Conn.Execute "Delete from tblEmps where EmpID = " & EmpID
End Sub



Public Function FormWithFields(EmpID, FormAction, PostGet)
    If IsNumeric(EmpID) = False Then
        Err.Raise vbObjectError + 1, "SampleServer", _
            "Employee ID must be passed as a number!"
    End If
    Dim Conn As New ADODB.Connection
    Dim RSEmp As ADODB.Recordset
    Conn.Open "EmpsDatabase", "Admin", ""
    Set RSEmp = Conn.Execute("select * from tblEmps where " _
        & "EmpID = " & EmpID)
    If RSEmp.EOF Then
        Err.Raise vbObjectError + 2, "SampleServer", _
            "No such employee record was found to edit!"
    End If
    FormWithFields = "<FORM ACTION=""" & FormAction _
        & """ METHOD=""" & PostGet & """>"
    FormWithFields = FormWithFields & _
        "<INPUT TYPE=""hidden"" NAME=""EmpID"" VALUE=""" & RSEmp("EmpID") & """>"
    FormWithFields = FormWithFields _
         & "First Name: <BR><INPUT TYPE=""text"" NAME=""FirstName"" VALUE=""" _
         & RSEmp("FirstName") & """><P>"
    FormWithFields = FormWithFields _
         & "Last Name: <BR><INPUT TYPE=""text"" NAME=""LastName"" VALUE=""" _
         & RSEmp("LastName") & """><P>"
    FormWithFields = FormWithFields _
         & "Department ID: <BR><INPUT TYPE=""text"" NAME=""DepartmentID"" VALUE=""" _
         & RSEmp("DepartmentID") & """><P>"
    FormWithFields = FormWithFields _
         & "Salary: <BR><INPUT TYPE=""text"" NAME=""Salary"" VALUE=""" _
         & RSEmp("Salary") & """><P>"
    FormWithFields = FormWithFields _
         & "Birth Date: <BR><INPUT TYPE=""text"" NAME=""BirthDate"" VALUE=""" _
         & RSEmp("BirthDate") & """><P>"
    FormWithFields = FormWithFields _
        & "<INPUT TYPE=""submit"" NAME=""Submit"" VALUE=""Submit""></FORM>"
End Function

Public Function EmpInfo(EmpID)
    If IsNumeric(EmpID) = False Then
        Err.Raise vbObjectError + 1, "SampleServer", _
            "Employee ID must be passed as a number!"
    End If
    Dim Conn As New ADODB.Connection
    Dim RSEmp As ADODB.Recordset
    Conn.Open "EmpsDatabase", "Admin", ""
    Set RSEmp = Conn.Execute("select * from tblEmps where " _
        & "EmpID = " & EmpID)
    If RSEmp.EOF Then
        Err.Raise vbObjectError + 2, "SampleServer", _
            "No such employee record was found to edit!"
    End If
    EmpInfo = "Employee ID: " & RSEmp("EmpID") & "<BR>"
    EmpInfo = EmpInfo & "First Name: " & RSEmp("FirstName") & "<BR>"
    EmpInfo = EmpInfo & "Last Name: " & RSEmp("LastName") & "<BR>"
    EmpInfo = EmpInfo & "Department: " & RSEmp("DepartmentID") & "<BR>"
    EmpInfo = EmpInfo & "Salary: " & FormatCurrency(RSEmp("Salary")) & "<BR>"
    EmpInfo = EmpInfo & "Birth Date: " & RSEmp("BirthDate") & "<BR>"
End Function


Employees Class

Public Function GetAllEmployees()
    Dim Conn As New ADODB.Connection
    Conn.Open "EmpsDatabase", "Admin", ""
    Set GetAllEmployees = Conn.Execute("select * from tblEmps")
End Function

Public Function LastNameRecords(LastNameLike)
    Dim Conn As New ADODB.Connection
    Conn.Open "EmpsDatabase", "Admin", ""
    Set LastNameRecords = Conn.Execute("select * from tblEmps " _
        & "where LastName Like '*" & LastNameLike & "*'")
End Function

Public Function GetAllEmployeesHTMLTable()
    Dim Conn As New ADODB.Connection
    Dim RSEmps As ADODB.Recordset
    Dim MyField
    Conn.Open "EmpsDatabase", "Admin", ""
    Set RSEmps = Conn.Execute("select * from tblEmps")
    GetAllEmployeesHTMLTable = "<TABLE><TR>"
    For Each MyField In RSEmps.Fields
        GetAllEmployeesHTMLTable = GetAllEmployeesHTMLTable & "<TD>" _
            & MyField.Name & "</TD>"
    Next
    GetAllEmployeesHTMLTable = GetAllEmployeesHTMLTable & "</TR>"
    Do Until RSEmps.EOF
        GetAllEmployeesHTMLTable = GetAllEmployeesHTMLTable & "<TR>"
        For Each MyField In RSEmps.Fields
            GetAllEmployeesHTMLTable = GetAllEmployeesHTMLTable & "<TD>" _
                & RSEmps(MyField.Name) & "</TD>"
        Next
        GetAllEmployeesHTMLTable = GetAllEmployeesHTMLTable & "</TR>"
        RSEmps.MoveNext
    Loop
    GetAllEmployeesHTMLTable = GetAllEmployeesHTMLTable & "</Table>"
End Function

Public Function SelectControl()
    Dim Conn As New ADODB.Connection
    Dim RSEmps As ADODB.Recordset
    Conn.Open "EmpsDatabase", "Admin", ""
    Set RSEmps = Conn.Execute("select EmpID, FirstName & ' ' & LastName " _
        & "as EmpName from tblEmps")
    SelectControl = "<SELECT NAME=""EmpID"" SIZE=1>"
    Do Until RSEmps.EOF
        SelectControl = SelectControl & "<OPTION VALUE=""" _
            & RSEmps("EmpID") & """>" & RSEmps("EmpName")
        RSEmps.MoveNext
    Loop
    SelectControl = SelectControl & "</SELECT>"
End Function


Public Function FormWithSelectControl(FormAction, PostGet)
    Dim Conn As New ADODB.Connection
    Dim RSEmps As ADODB.Recordset
    Conn.Open "EmpsDatabase", "Admin", ""
    Set RSEmps = Conn.Execute("select EmpID, FirstName & ' ' & LastName " _
        & "as EmpName from tblEmps")
    FormWithSelectControl = "<FORM ACTION=""" & FormAction _
        & """ METHOD=""" & PostGet & """>EmpID:"
    FormWithSelectControl = FormWithSelectControl & "<SELECT NAME=""EmpID"" SIZE=1>"
    Do Until RSEmps.EOF
        FormWithSelectControl = FormWithSelectControl & "<OPTION VALUE=""" _
            & RSEmps("EmpID") & """>" & RSEmps("EmpName")
        RSEmps.MoveNext
    Loop
    FormWithSelectControl = FormWithSelectControl & "</SELECT><P>" _
        & "<INPUT TYPE=""submit"" NAME=""Submit"" VALUE=""Submit""></FORM>"
End Function