The Workings of the Recruitment System Application
|
The Recruitment System application allows users to work with applicants' data. Users can add, delete, and update an applicant's records. They can also search for a particular applicant's record based on the specified criteria.
When the application is loaded, the Login page is displayed, as shown in Figure 16.7.
Figure 16.7: The Login page.
On the Login page, users provide their user IDs and passwords and then click the Login button. The page is submitted and the file specified in the ACTION attribute of the <FORM> tag is executed. The name of the specified file is processlogin.cfm. The code in processlogin.cfm verifies a user ID and password combination from the Logins table in the RecruitmentApp database. If the combination is valid, the Main Working Console page is displayed. If the user ID or the password is invalid, the Login page loads again with an error message. Figure 16.8 displays the Login page with an error message.
Figure 16.8: The Login page with an error message.
Before learning about the code in the processlogin.cfm file, let's examine how the data source to be used for the project is to be configured.
Configuring the Data Source
As mentioned earlier, the SQL Server 2000 database named RecruitmentApp is used as the back-end database for the Recruitment System application. This section details the way the data source should be configured so that the application code can interact with the RecruitmentApp database.
To configure the data source, perform the following steps:
-
Choose Start, Programs, Macromedia ColdFusion MX, Administrator. The ColdFusion MX Administrator page appears, as shown in Figure 16.9. To use the Administrator page, you will need to provide the administrator password.
Figure 16.9: The ColdFusion MXAdministrator page. -
Under Data & Services, click Data Sources to display the Data Sources interface, shown in Figure 16.10. This interface allows you to add and manage data source connections and Data Source Names (DSNs). There are two sections in this interface. The section at the top is used to add data sources, and the section at the bottom is used to manage the data sources. Here, you'll create a new connection to the RecruitmentApp database.
Figure 16.10: The screen for adding and managing data sources. -
In the Data Source Name field, enter RecruitmentApp. From the Driver drop-down list, select Microsoft SQL Server and click the Add button.
-
In the Database field, enter RecruitmentApp. In the Server field, enter localhost and retain the default value in the Port field. In the Username field, enter the username to be used while connecting to the RecruitmentApp database. Then, enter the password in the Password field. In the Description field, specify the description of the data source. Figure 16.11 shows the screen with values for all the fields.
Figure 16.11: The screen to provide details about the RecruitmentApp data source. -
Click the Submit button to create the data source. Figure 16.12 displays the screen indicating the successful creation of the RecruitmentApp data source.
Figure 16.12: The screen indicating the successful creation of the RecruitmentApp data source. -
In the Connected Data Sources list, the RecruitmentApp data source appears. Using this screen, you can edit, delete, and verify the data source. Figure 16.13 shows the RecruitmentApp data source in the Connected Data Sources list. The icons to the left of the data source name are used to edit, verify, and delete the data source.
Figure 16.13: The screen displaying the RecruitmentApp data source in the Connected Data Sources list.
Validating the Application Users
After configuring the RecruitmentApp data source, you need to write code in the processlogin.cfm file to validate the application users. This file is executed when the user clicks the Login button. The following code in the processlogin.cfm file creates a connection with the RecruitmentApp database by using the <CFQUERY> tag:
<CFQUERY NAME="Verify_login" DATASOURCE="RecruitmentApp"> SELECT UserID, password FROM logins WHERE UserID = '#FORM.user_id#' AND password = '#FORM.password#' </CFQUERY>
The <CFQUERY> tag contains two attributes. NAME indicates the name of the query, and DATASOURCE indicates the name of the data source used in the query. The SQL statement is used to retrieve a user ID and a password from the Logins table, where the user ID and password match the user ID and the password entered in the Login page.
The following code tests if RECORDCOUNT of the Verify_login query is equal to 0:
<CFIF Verify_login.RECORDCOUNT IS 0>
If RECORDCOUNT is equal to 0, the login.cfm file is loaded again with an error message. If RECORDCOUNT is greater than 0, the Main Working Console page is displayed to the user.
Displaying All Records in the Main Working Console Page
The Main Working Console page displays a list of applicant records. The following code in the Mainpage.cfm file establishes a connection with the RecruitmentApp database and fetches the records to be displayed:
<CFQUERY NAME="get_applicants_info" DATASOURCE="Recruitmentapp"> SELECT ApplicantID, ApplicantName, AppliedFor, ApplicationDate FROM Applicants </CFQUERY>
The <CFQUERY> tag specifies the name of a query using the NAME attribute and the data source name using the DATASOURCE attribute. SQL statements are used to retrieve ApplicantID, ApplicantName, AppliedFor, and Applicationdate fields from the Applicants table.
After creating the get_applicants_info query, the following code checks if RECORDCOUNT of the query is equal to 0. If so, a message should be displayed to the user:
<CFIF get_applicants_info.RECORDCOUNT IS 0> <I><FONT FACE="Arial" SIZE="3" Color="red"><B>Currently there are no Records Found in the database.</B></FONT></I><BR><BR> </CFIF>
The <CFIF> tag checks for the condition if RECORDCOUNT is 0. If RECORDCOUNT is greater than 0, all records that are fetched in the query are displayed in a tabular form. Alternate rows have the same background color. The following code shows how the tabular display is created using <CFOUTPUT>, <CFIF>, and <TABLE> tags:
<TABLE BORDER="0" Align ="Left"> <TR bgcolor = "990000"> <TH>Applicant ID</TH> <TH>Applicant Name</TH> <TH>Applied For</TH> <TH>Application Date</TH> <TR> <CFOUTPUT QUERY = "get_applicants_info"> <CFIF CurrentRow Mod 2> <cfset BackColor = "peachpuff"> <CFELSE> <cfset BackColor = "Orange"> </CFIF> <TR bgcolor = #BackColor#> <TD><a href ="ApplicantDetails.cfm?ID=#URLEncodedFormat(ApplicantID)#"> #ApplicantID#</a></TD> <TD>#ApplicantName#</TD> <TD>#AppliedFor#</TD> <TD>#dateformat(ApplicationDate,"dddd, mmmm d, yyyy")#</TD> </TR> </CFOUTPUT> </TABLE>
In this code, the value from the ApplicantID field is displayed as a hyperlink. The anchor tag is used to link the ApplicantID field to the ApplicantDetails.cfm file, and ApplicantID is passed as a parameter to that file. The Application Date field is also formatted using the dateformat function.
In addition to displaying the list of applicants, the Main Working Console page also displays two links, Add New Applicant Details and Search Applicants. The following code in the Mainpage.cfm file shows how these links are coded:
<TABLE Align ="Left"> <TR> <TD><a href ="AddNewApplicant.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Add New Applicant's Details</B></FONT></a></TD> </TR> <TR> <TD><a href ="SearchForm.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Search Applicants</B> </FONT></a></TD> </TR> </TABLE>
When a user clicks the Add New Applicant Details link, the AddNewApplicant.cfm file is loaded. When a user clicks the Search Applicants link, the SearchForm.cfm file is loaded.
Adding a New Applicant's Details
You can add a new applicant's record by using the Add New Applicant Details link on the Main Working Console page. The code for the Add New Applicant page is mostly HTML, except for the creation of the Applicant's Skills List control. To create this List control, the <CFSELECT> tag is used. The Query attribute of the <CFSELECT> tag is set to get_skills. This query, in turn, is created using the <CFQUERY> tag as follows:
<CFQUERY NAME="get_skills" DATASOURCE="Recruitmentapp"> SELECT SkillID, Description FROM Skills ORDER BY SkillID </CFQUERY>
The SQL statement in the preceding query is used to retrieve a skill ID and its description from the Skills table. Records are fetched in ascending order of skill IDs. Users will need to enter data manually in the remaining text fields on the page. Most of these fields are required. If a field is left empty, an error message is displayed. The following is the code for the text fields in the AddNewApplicant.cfm file:
<TABLE> <CFFORM ACTION="InsertRecord.cfm" METHOD="POST"> <TR> <TD><B>Applicant's Name: </B></TD> <TD><CFINPUT TYPE="Text" NAME="App_Name" SIZE="50" MAXLENGTH="50" REQUIRED="YES" MESSAGE="Name Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Address: </B></TD> <TD><CFINPUT TYPE="Text" NAME="App_Address" SIZE="50" MAXLENGTH="50" REQUIRED="YES" MESSAGE="Address Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Contact Number: </B></TD> <TD><CFINPUT TYPE="Text" Name="App_ContactNo" Size="15" MAXLENGTH="15" REQUIRED="YES" VALIDATE="Telephone" MESSAGE="Contact No Field is either Empty or not in Correct format(xxx-xxx-xxxx)."></TD> </TR> <TR> <TD><B>Applicant's Email Address: </B></TD> <TD><CFINPUT TYPE="TEXT" Name = "App_EmailAddress" size= "50" MAXLENGTH="100" REQUIRED="YES" MESSAGE="Email Address Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applied For: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_AppliedFor" Size = "30" MAXLENGTH="30" REQUIRED="YES" MESSAGE="Applied For Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Application Date: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_ApplicationDate" Size = "30" MAXLENGTH="10" REQUIRED="YES" VALIDATE="Date" MESSAGE="Application Date Field is either Empty or not in correct Format(mm/dd/yyyy)."></TD> </TR> <TR> <TD><B>Date Of Joining: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_DateOfJoining" Size = "30" MAXLENGTH="10" REQUIRED="NO" VALIDATE="Date"></TD> </TR> <TR> <TD><B>Applicant's Qualification Details: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_QualDetails" Size = "50" MAXLENGTH="200" REQUIRED="YES" MESSAGE="Qualifications Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Experience (in Years): </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_ExpYears" Size = "50" MAXLENGTH = "2" REQUIRED="YES" MESSAGE="Exp. Years Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Skills: </B></TD> <TD><CFSELECT Name = "App_Skills" Size = "5" MULTIPLE="YES" QUERY="get_skills" Value="SkillID" DISPLAY="Description" REQUIRED="YES" MESSAGE="At Least One Skill Should be Selected."> </CFSELECT></TD> </TR> </TABLE>
Attributes like Validate, Required, and Message are used in <CFINPUT> and <CFSELECT> tags to validate user input before the data on the form can be processed further. If the value for the Required attribute is set to YES for a field, the field is checked to make sure that the user has provided some input in that field. The Message attribute is used to display a message to the user if a field is left empty. The Message attribute also displays a message if the value of a field is not in accordance with the Validate attribute.
After entering an applicant's details, the user clicks the Save Record button to save the applicant's record in the database. This calls the file specified in the ACTION attribute of the <CFFORM> tag. This file implements the functionality to save the applicant's record in the database. The following code shows the attribute settings of the <CFFORM> tag:
<CFFORM ACTION="InsertRecord.cfm" METHOD="POST">
The code in the InsertRecord.cfm file is as follows:
<CFTRANSACTION> <CFQUERY NAME="InsertRecord" DATASOURCE="RecruitmentApp"> INSERT INTO Applicants (ApplicantName, Address, ContactNo, EmailAddress, AppliedFor, ApplicationDate, DateOfJoining, QualificationDetails, ExperienceYears) VALUES ('#Form.App_Name#','#Form.App_Address#','#Form.App_ContactNo#', '#Form.App_EmailAddress#','#Form.App_AppliedFor#', '#Form.App_ApplicationDate#','#Form.App_DateOfJoining#', '#Form.App_QualDetails#',#Form.App_ExpYears#) </CFQUERY> <CFQUERY NAME = "get_AppID" DATASOURCE="RecruitmentApp"> SELECT Max(ApplicantID) as AppID FROM Applicants </CFQUERY> </CFTRANSACTION> <CFOUTPUT QUERY = "get_AppID"> <CFSET varAppID = #AppID#> </CFOUTPUT> <CFSET Length = ListLen(#Form.App_Skills#)> <CFLOOP INDEX = "element" FROM = "1" TO="#Length#"> <CFSET varSkillID = ListGetAt(#Form.App_Skills#,element)> <CFQUERY NAME="AddNew" DATASOURCE="RecruitmentApp"> INSERT INTO Applicants_Skills(ApplicantID,SkillID) VALUES (#varAppID#,'#varSkillID#') </CFQUERY> </CFLOOP> The preceding code inserts the new record in the Applicants table and the specified skills in the Applicants_Skills table. When inserting the new record in the Applicants table, the Applicant ID is generated automatically. This ID is saved in a variable, which is then used in the insert statement to insert records in the Applicants_Skills table.
| Note | SQL statements for inserting the new record in the Applicants table and the select SQL statement are executed as a transaction so that the Applicant ID for the inserted record is saved in a variable. This is done with the multiuser environment in mind. |
While inserting records in Applicants_Skills table, the second required field is SkillID. For example, if the user selects three skills, three records need to be inserted into the Applicants_Skills table. The List control used to accept the skills of an applicant returns selected skills in a comma-separated list. From this list, individual SkillIDs are extracted using ListLen, CFLOOP, and ListGetAt functions. Each SkillID is extracted in a loop, and the record for that SkillID is inserted in the Applicants_Skills table. After the new record is inserted successfully, a message is displayed to the user. Along with the message, two links, Add Another Applicant's Details and Back to Main Working Console, are shown to the user. The Add Another Applicant's Detail link enables users to open the Add Applicant's Detail page. The second link is for navigating back to the Main Working Console page. The complete code is as follows:
<HTML><HEAD><TITLE>Recruitment Application: Inserting New Record</TITLE></HEAD> <BODY BGCOLOR="peachpuff"> <DIV ALIGN="center" Bgcolor="Orange"> <FONT FACE="Arial" SIZE="4" COLOR="##ff0000">New Record Added for Applicant: #Form.App_Name#</FONT> </DIV> <P> <TABLE> <TR> <TD><a href ="AddNewApplicant.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Add Another Applicant's Details</B></FONT></a></TD> </TR> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML>
When interacting with the database, some unexpected errors may occur. It could happen if the field name coded in the file and the name of the field in the table are different. To handle these errors and exceptions, <CFTRY> and <CFCATCH> tags are used. The <CFTRY> tag is used as the container for the code where errors need to be trapped. The <CFCATCH> tag is used to write code that's executed when an error occurs. The following is the <CFCATCH> section of error-trapping code:
<CFCATCH type="database"> <DIV ALIGN ="center"> <FONT FACE = "Arial"> <H3> A Database Error Occurred.</H3> <HR> </FONT> </DIV> <FONT FACE = "Arial"> The Detailed Error Information is displayed below: <p> <CFOUTPUT> <B>TYPE</B>: #CFCATCH.TYPE#<BR> <B>MESSAGE</B>: #CFCATCH.MESSAGE#<BR> <B>DETAILS</B>: <FORM> <TEXTAREA COLS="80" ROWS = "2" NAME = "error_details">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA> </FORM> <CFSET varError = 1> </CFOUTPUT> </FONT> </CFCATCH>
| Note | Various types of exceptions can be handled using the <CFCATCH> tag. In the current application, the database type is used. To display a message to the user, exception-handling variables such as CFCATCH.TYPE, CFCATCH.MESSAGE, and CFCATCH.DETAIL are used. |
Displaying the Details of a Selected Applicant
On the Main Working Console page, the ApplicantID field is a hyperlink. When a user clicks this link, the ApplicantDetails.cfm file is loaded to display the corresponding details. The following code fetches a selected applicant's record from the Applicants table:
<CFQUERY NAME="get_applicants_details" DATASOURCE="Recruitmentapp"> SELECT * FROM Applicants WHERE ApplicantID = #URL.ID# </CFQUERY>
The ApplicantID field is passed to the Applicant Details page, along with the URL. The ApplicantID used in the preceding SQL statement is derived from the URL.
On the Applicant Details page, the user can view the skills of the selected applicant and also add or remove skills. The skills of an applicant are stored in the Applicants_Skills table. The following code displays the skill description of the selected applicant:
<CFQUERY NAME="get_current_skills" DATASOURCE="Recruitmentapp"> SELECT Skills.SkillID,Description FROM Skills, Applicants_Skills WHERE Applicants_Skills.ApplicantID = #URL.ID# AND Skills.SkillID = Applicants_Skills.SkillID </CFQUERY>
The skill descriptions that are fetched in the preceding query are displayed using the Checkbox control. If no skills are stored for the selected applicant, a message is displayed. The following code displays the skills already linked to the selected applicant:
<CFOUTPUT QUERY = "get_current_skills"> <CFINPUT Type = "checkbox" Name = "CurrentSkills" Value = "#SkillID#"> #Description# <BR> </CFOUTPUT>
Other applicant details, such as applicant address and e-mail, are displayed using the get_applicants_details query, as shown here:
<CFOUTPUT QUERY = "get_applicants_details">
After a record is displayed, the user can make changes to the existing information and update the modified information by using the Update Record button. This calls the UpdateRecord.cfm file.
When updating a record in the database, first it's determined whether the record exists in the database. If the record is found, it's updated. The ConfirmRecord query checks the existence of an applicant's record in the database, as shown here:
<CFQUERY NAME="ConfirmRecord" DATASOURCE="RecruitmentApp"> SELECT ApplicantID FROM Applicants WHERE ApplicantID = #Form.AppID# </CFQUERY> <CFIF NOT ConfirmRecord.recordcount> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application: Updating Record</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <I><FONT FACE="Arial" SIZE="3" Color="orangered"><B>There are no Records Found For the current Applicant in the Database.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="orangered"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML> <CFABORT> </CFIF>
In the preceding code, the <CFQUERY> tag is used to create the ConfirmRecord query, and the <CFIF> tag is used to check the existence of an applicant's record in the database.
If the record is not found, a message is displayed and the code execution is stopped using the <CFABORT> tag. If the record is found, it's updated using the UpdateRecord query. All applicant information is stored in the Applicants table, except for information related to skills, which is stored in the Applicant_Skills table. Therefore, the process of updating the information is performed in two steps. The query UpdateRecord is used to update the information in the Applicants table, as shown in this code:
<CFQUERY NAME="UpdateRecord" DATASOURCE="RecruitmentApp"> UPDATE Applicants SET Address = '#Form.App_Address#', ContactNo = '#Form.App_ContactNo#', EmailAddress = '#Form.App_EmailAddress#', AppliedFor = '#Form.App_AppliedFor#', ApplicationDate = '#Form.App_ApplicationDate#', DateOfJoining = '#Form.App_DateOfJoining#', QualificationDetails = '#Form.App_QualDetails#', ExperienceYears = #Form.App_ExpYears# WHERE ApplicantID = #Form.AppID# </CFQUERY>
In addition to modifying the displayed record, the user can delete some of the existing skills of an applicant or add some new skills. The new skills can be specified in the App_Skills List control. The following code extracts each skill ID from the List control and checks if it already exists in the Applicants_Skills table. If it doesn't exist, it's inserted into the table:
<CFIF FORM.App_Skills IS NOT ""> <CFSET Length = ListLen(#Form.App_Skills#)> <CFLOOP INDEX = "element" FROM = "1" TO="#Length#"> <CFSET varSkillID = ListGetAt(#Form.App_Skills#,element)> <CFQUERY NAME="CheckRecord" DATASOURCE="RecruitmentApp"> SELECT SkillID FROM Applicants_Skills WHERE ApplicantID = #Form.AppID# AND SkillID = '#varSkillID#' </CFQUERY> <CFIF CheckRecord.Recordcount IS 0> <CFQUERY NAME="AddNew" DATASOURCE="RecruitmentApp"> INSERT INTO Applicants_Skills(ApplicantID,SkillID) VALUES (#Form.AppID#,'#varSkillID#') </CFQUERY> </CFIF> </CFLOOP> </CFIF>
The existing skills are displayed on the Applicant Details page by using the Checkbox control. The user can select the skills that are to be deleted. When the UpdateRecord.cfm file is executed, it checks for skills selected in the Applicants_Skills table. These skills are deleted from the Applicants_Skills table. This code from the UpdateRecord.cfm file is given as follows:
<CFIF FORM.CurrentSkills IS NOT ""> <CFSET Length = ListLen(#Form.CurrentSkills#)> <CFLOOP INDEX = "element" FROM = "1" TO="#Length#"> <CFSET varSkillID = ListGetAt(#Form.CurrentSkills#,element)> <CFQUERY NAME="DeleteSkill" DATASOURCE="RecruitmentApp"> DELETE FROM Applicants_Skills WHERE ApplicantID = #Form.AppID# AND SkillID = '#varSkillID#' </CFQUERY> </CFLOOP> </CFIF>
In the preceding code, existing skills are displayed using a group of checkboxes named CurrentSkills. Selected skills are stored in a comma-separated string and can be referred to by using Form.CurrentSkills in the code. To extract an individual skill ID from this comma-separated list, various list functions, such as ListLen and ListgetAt, are used.
The Applicant's Details page also provides a link to delete the currently displayed record, as shown here:
<a href ="DeleteRecord.cfm?ID=#AppID#"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Delete Current Record</B></a><BR>
When the user clicks the Delete Current Record link, the DeleteRecord.cfm file is loaded to delete the current record. When a record is deleted, first it's checked to see if it exists in the database. If the record exists, it's deleted. The ConfirmRecord query is created to check the existence of an applicant's record. The following code shows how the ConfirmRecord query is created using the <CFQUERY> tag, and a check for the existence of the record is performed using the <CFIF> tag:
<CFQUERY NAME="ConfirmRecord" DATASOURCE="RecruitmentApp"> SELECT ApplicantID FROM Applicants WHERE ApplicantID = #URL.ID# </CFQUERY> <CFIF NOT ConfirmRecord.recordcount> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application: Deleting Record</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <I><FONT FACE="Arial" SIZE="3" Color="990000"><B>There is no Record Found for the current Applicant in the Database.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML> <CFABORT> </CFIF>
If an applicant's record is not found in the database, a message is displayed and the code execution is stopped using the CFABORT tag. If the record is found, it's deleted using two queries. The following code shows the DeleteSkillRecords query, which deletes records from the Applicants_Skills table for a selected applicant:
<CFQUERY NAME="DeleteSkillRecords" DATASOURCE="RecruitmentApp"> DELETE FROM Applicants_Skills WHERE ApplicantID = #URL.ID# </CFQUERY>
The second query, DeleteRecord, is used to delete an applicant's record from the Applicants table as follows:
<CFQUERY NAME="DeleteRecord" DATASOURCE="RecruitmentApp"> DELETE FROM Applicants WHERE ApplicantID = #URL.ID# </CFQUERY>
Working with the Search Console
The Search Console page is used to search applicant records based on criteria specified by the user. Users can navigate to the Search Console page by using the Search Applicants link on the Main Working Console page. The SearchForm.cfm file is loaded, and it displays the page where search criteria may be specified.
A search can be performed based on the qualifications and experience of applicants. The user can select the comparison operator from the drop-down list and specify the value to be matched in the corresponding text box. If no criteria are specified, all applicant records are displayed. After specifying the criteria, the user clicks the Fetch Matching Records button to display the search results on a new page, which is generated dynamically by the SearchResults.cfm file.
The SearchResults.cfm file first builds the WHERE clause based on the criteria specified by the user. It checks the value in the text box and the drop-down list to decide the construct of the WHERE clause to be used in the get_applicants_info query. The following code shows how the WHERE clause is built dynamically:
<CFSET TheWhereClause = " 1=1 "> <CFIF Form.QualValue GT ""> <CFIF Form.QualOperator EQ "EQUALS"> <CFSET TheWhereClause = TheWhereClause & " and QualificationDetails = '" & Form.QualValue & "'" > <CFELSE> <CFSET TheWhereClause = TheWhereClause & " and QualificationDetails LIKE '" & Form.QualValue & "%'" > </CFIF> </CFIF> <CFIF Form.ExpYearValue GT ""> <CFIF Form.ExpYearOperator EQ "EQUALS"> <CFSET TheWhereClause = TheWhereClause & " and ExperienceYears = " & Form.ExpYearValue > <CFELSEIF Form.ExpYearOperator EQ "GREATER"> <CFSET TheWhereClause = TheWhereClause & " and ExperienceYears > " & Form.ExpYearValue > <CFELSEIF Form.ExpYearOperator EQ "SMALLER"> <CFSET TheWhereClause = TheWhereClause & " and ExperienceYears < " & Form.ExpYearValue > </CFIF> </CFIF>
The TheWhereClause variable is used to store the final WHERE clause. After the WHERE clause is built, it is passed to the get_applicants_info query. The results of the query are displayed in a tabular format, which is the same as the format used in the Main Working Console page for displaying the list of applicants. The following code shows the get_applicants_info query:
<CFQUERY NAME="get_applicants_info" DATASOURCE="Recruitmentapp"> SELECT ApplicantID, ApplicantName, AppliedFor, ApplicationDate FROM Applicants WHERE #PreserveSingleQuotes(TheWhereClause)# </CFQUERY>
In the preceding code, the PreserveSingleQuotes function is used to preserve the single quotes specified in the criteria value text box. The single quotes, if used otherwise, can produce a syntax error in the SQL statement.
Now that you understand the code for all the Web pages, Listing 16.1 provides the complete code for the Login.cfm page.
Listing 16.1: Login.cfm
 |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <CFOUTPUT> <HTML><HEAD><TITLE>Recruitment System Application Login Page</TITLE></HEAD> <BODY BGCOLOR="peachpuff"> <BR><BR> <DIV ALIGN="center"> <B><FONT FACE="Arial" SIZE="5" color="990000"><U>Welcome to Recruitment Application</U></FONT></B><BR><BR> <B><FONT FACE="Arial" SIZE="4" Color="990000"><U>Recruitment Application Login</U></FONT></B> <FORM ACTION="processlogin.cfm" METHOD="POST" name="Login" ><FONT FACE="Arial" SIZE="2"> <CFIF ISDEFINED("CLIENT.user_name")> <B>Welcome to Recruitment Application #CLIENT.user_name#!</B> <P>Please login to start a new session. </CFIF> <TABLE> <TR> <TD><FONT FACE="Arial" SIZE="2" Color="990000"><B>Enter your User ID:</B></FONT></TD> <TD><INPUT TYPE="Text" NAME="user_id" SIZE="20"></TD> </TR> <TR> <TD><FONT FACE="Arial" SIZE="2" Color="990000"><B>Password:</B></FONT></TD> <TD><INPUT TYPE="password" NAME="password" SIZE="20"></TD> </TR> </TABLE> <P><INPUT TYPE="submit" NAME="Submit" VALUE="Login"> </P> </FORM> </DIV> </BODY> </HTML> </CFOUTPUT> |
Listing 16.2 provides the complete code for MainPage.cfm.
Listing 16.2: ProcessLogin.cfm
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <CFQUERY NAME="Verify_login" DATASOURCE="RecruitmentApp"> SELECT UserID, password FROM logins WHERE UserID = '#FORM.user_id#' AND password = '#FORM.password#' </CFQUERY> <CFIF Verify_login.RECORDCOUNT IS 0> <CFOUTPUT> <HTML><HEAD><TITLE>Recruitment Application</TITLE></HEAD> <BODY BGCOLOR="peachpuff"> <DIV ALIGN="center"> <CFINCLUDE TEMPLATE="login.cfm"> <FONT FACE="Arial" SIZE="2" COLOR="red"> <BR> <B>This User ID and Password combination is not valid.<BR>Try again.</B> </FONT> </DIV> </BODY> </HTML> </CFOUTPUT> <CFABORT> <CFELSE> <CFLOCATION URL="MainPage.cfm" ADDTOKEN="no"> </CFIF>
|
Listing 16.3 provides the complete code for the MainPage.cfm file.
Listing 16.3: MainPage.cfm
|
<CFQUERY NAME="get_applicants_info" DATASOURCE="Recruitmentapp"> SELECT ApplicantID, ApplicantName, AppliedFor, ApplicationDate FROM Applicants </CFQUERY> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application List of Applicants</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <P> <DIV Align = "Center"> <B><FONT FACE="Arial" SIZE="4" Color="990000"><U>Recruitment Application: Main Working Console</U></FONT></B></P> </DIV></P> <P><B><FONT FACE="Arial" SIZE="3" Color="990000"><U>List of All Applicants</U></FONT></B></P> <B><FONT FACE="Arial" SIZE="2" Color="990000">(Click on the Applicant ID to work with detailed record.)</FONT></B></P> <CFIF get_applicants_info.RECORDCOUNT IS 0> <I><FONT FACE="Arial" SIZE="3" Color="red"><B>Currently there are no Records Found in the database.</B></FONT></I><BR><BR> </CFIF> <TABLE BORDER="0" Align ="Left"> <TR bgcolor = "990000"> <TH>Applicant ID</TH> <TH>Applicant Name</TH> <TH>Applied For</TH> <TH>Application Date</TH> <TR> <CFOUTPUT QUERY = "get_applicants_info"> <CFIF CurrentRow Mod 2> <cfset BackColor = "peachpuff"> <CFELSE> <cfset BackColor = "Orange"> </CFIF> <TR bgcolor = #BackColor#> <TD><a href ="ApplicantDetails.cfm?ID=#URLEncodedFormat(ApplicantID)#"> #ApplicantID#</a></TD> <TD>#ApplicantName#</TD> <TD>#AppliedFor#</TD> <TD>#dateformat(ApplicationDate,"dddd, mmmm d, yyyy")#</TD> </TR> </CFOUTPUT> </TABLE> <DIV> <TABLE Align ="Left"> <TR> <TD><a href ="AddNewApplicant.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Add New Applicant's Details</B></FONT></a></TD> </TR> <TR> <TD><a href ="SearchForm.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Search Applicants</B></FONT></a></TD> </TR> </TABLE> </DIV> </BODY> </HTML>
|
Listing 16.4 provides the complete code for the ApplicantDetails.cfm file.
Listing 16.4: ApplicantDetails.cfm
|
<CFQUERY NAME="get_applicants_details" DATASOURCE="Recruitmentapp"> SELECT * FROM Applicants WHERE ApplicantID = #URL.ID# </CFQUERY> <CFQUERY NAME="get_current_skills" DATASOURCE="Recruitmentapp"> SELECT Skills.SkillID,Description FROM Skills, Applicants_Skills WHERE Applicants_Skills.ApplicantID = #URL.ID# AND Skills.SkillID = Applicants_Skills.SkillID </CFQUERY> <CFQUERY NAME="get_skills" DATASOURCE="Recruitmentapp"> SELECT SkillID, Description FROM Skills ORDER BY SkillID </CFQUERY> <cfparam name="FORM.CurrentSkills" default=""> <cfparam name="FORM.App_Skills" default=""> <CFFORM ACTION="UpdateRecord.cfm" METHOD="POST"> <CFOUTPUT QUERY = "get_applicants_details"> <CFSET AppID = #ApplicantID#> <HTML> <HEAD> <TITLE>Recruitment Application Details for #ApplicantName#</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <P><B><FONT FACE="Arial" SIZE="3" Color="990000"><U>Application Details For #ApplicantName#</U></FONT></B></P> <TABLE> <TR> <TD><B>Applicant's Address: </B></TD> <TD><CFINPUT TYPE="Text" NAME="App_Address" SIZE="50" Value = #Address# MAXLENGTH="50" REQUIRED="YES" MESSAGE="Address Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Contact Number: </B></TD> <TD><CFINPUT TYPE="Text" Name="App_ContactNo" Size="15" value = #ContactNo# MAXLENGTH="15" REQUIRED="YES" VALIDATE="Telephone" MESSAGE="Contact No Field is either Empty or not in Correct format(xxx-xxx-xxxx)."></TD> </TR> <TR> <TD><B>Applicant's Email Address: </B></TD> <TD><CFINPUT TYPE="TEXT" Name = "App_EmailAddress" size= "50" value = #EmailAddress# MAXLENGTH="100" REQUIRED="YES" MESSAGE="Email Address Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applied For: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_AppliedFor" Size = "50" value = #AppliedFor# MAXLENGTH="30" REQUIRED="YES" MESSAGE="Applied For Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Application Date: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_ApplicationDate" Size = "15" value = #dateformat(ApplicationDate,"mm/dd/yyyy")# MAXLENGTH="10" REQUIRED="YES" VALIDATE="Date" MESSAGE="Application Date Field is either Empty or not in correct Format(mm/dd/yyyy)."></TD> </TR> <TR> <TD><B>Date Of Joining: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_DateOfJoining" Size = "15" Value = #dateformat(Dateofjoining,"mm/dd/yyyy")# MAXLENGTH="10" REQUIRED="NO" VALIDATE="Date"></TD> </TR> <TR> <TD><B>Applicant's Qualification Details: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_QualDetails" Size = "50" Value = #QualificationDetails# MAXLENGTH="200" REQUIRED="YES" MESSAGE="Qualifications Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Experience (in Years): </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_ExpYears" Size = "15" MAXLENGTH="2" Value = #ExperienceYears# REQUIRED="YES" MESSAGE="Exp. Years Field Cannot be Empty"></TD> </TR> </TABLE> <INPUT TYPE = "hidden" Name = "AppID" VALUE = #ApplicantID#> </CFOUTPUT> <P> <B><U>Applicant's Current Skills: </U></B><FONT FACE="Arial" SIZE="2" Color="990000"><B>(Select the Skills to be Deleted and click Update Record button.)</B></FONT> <BR><BR> <CFIF get_current_skills.RECORDCOUNT IS 0> <I><FONT FACE="Arial" SIZE="2" Color="990000"><B>There are no Skills Indicated for this Applicant.</B></FONT></I><BR> </CFIF> <CFOUTPUT QUERY = "get_current_skills"> <CFINPUT Type = "checkbox" Name = "CurrentSkills" Value = "#SkillID#"> #Description# <BR> </CFOUTPUT> <BR> <B>Add Skills for this Applicant:</B><BR> <CFSELECT Name = "App_Skills" Size = "5" MULTIPLE="YES" QUERY="get_skills" Value="SkillID" DISPLAY="Description" REQUIRED="NO" MESSAGE="At Least One Skill Should be Selected."></CFSELECT></TD> <P> <INPUT TYPE="Submit" Name ="Submit" VALUE="Update Record"> <P> <CFOUTPUT> <a href ="DeleteRecord.cfm?ID=#AppID#"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Delete Current Record</B></a><BR> <a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"> <B>Back to Main Working Console</B></FONT></a> </CFOUTPUT> </BODY></HTML></CFFORM>
|
Listing 16.5 provides the complete code for the UpdateRecord.cfm file.
Listing 16.5: UpdateRecord.cfm
|
<cfparam name="FORM.CurrentSkills" default=""> <cfparam name="FORM.App_Skills" default=""> <CFQUERY NAME="ConfirmRecord" DATASOURCE="RecruitmentApp"> SELECT ApplicantID FROM Applicants WHERE ApplicantID = #Form.AppID# </CFQUERY> <CFIF NOT ConfirmRecord.recordcount> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application: Updating Record</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <I><FONT FACE="Arial" SIZE="3" Color="990000"><B>There are no Records Found For the current Applicant in the Database.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML> <CFABORT> </CFIF> <CFQUERY NAME="UpdateRecord" DATASOURCE="RecruitmentApp"> UPDATE Applicants SET Address = '#Form.App_Address#', ContactNo = '#Form.App_ContactNo#', EmailAddress = '#Form.App_EmailAddress#', AppliedFor = '#Form.App_AppliedFor#', ApplicationDate = '#Form.App_ApplicationDate#', DateOfJoining = '#Form.App_DateOfJoining#', QualificationDetails = '#Form.App_QualDetails#', ExperienceYears = #Form.App_ExpYears# WHERE ApplicantID = #Form.AppID# </CFQUERY> <!--- Code for Updating the Skills in Applicants_Skills Table ---> <CFIF FORM.App_Skills IS NOT ""> <CFSET Length = ListLen(#Form.App_Skills#)> <CFLOOP INDEX = "element" FROM = "1" TO="#Length#"> <CFSET varSkillID = ListGetAt(#Form.App_Skills#,element)> <CFQUERY NAME="CheckRecord" DATASOURCE="RecruitmentApp"> SELECT SkillID FROM Applicants_Skills WHERE ApplicantID = #Form.AppID# AND SkillID = '#varSkillID#' </CFQUERY> <CFIF CheckRecord.Recordcount IS 0> <CFQUERY NAME="AddNew" DATASOURCE="RecruitmentApp"> INSERT INTO Applicants_Skills(ApplicantID,SkillID) VALUES (#Form.AppID#,'#varSkillID#') </CFQUERY> </CFIF> </CFLOOP> </CFIF> <!--- Code for Deleting the Selected Current Skills from Applicants_Skills table---> <CFIF FORM.CurrentSkills IS NOT ""> <CFSET Length = ListLen(#Form.CurrentSkills#)> <CFLOOP INDEX = "element" FROM = "1" TO="#Length#"> <CFSET varSkillID = ListGetAt(#Form.CurrentSkills#,element)> <CFQUERY NAME="DeleteSkill" DATASOURCE="RecruitmentApp"> DELETE FROM Applicants_Skills WHERE ApplicantID = #Form.AppID# AND SkillID = '#varSkillID#' </CFQUERY> </CFLOOP> </CFIF> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application: Updating Record Confirmation</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <I><FONT FACE="Arial" SIZE="3" Color="orangered"><B>The Record has been Updated Successfully.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML>
|
Listing 16.6 provides the complete code for the DeleteRecord.cfm file.
Listing 16.6: DeleteRecord.cfm
|
<CFQUERY NAME="ConfirmRecord" DATASOURCE="RecruitmentApp"> SELECT ApplicantID FROM Applicants WHERE ApplicantID = #URL.ID# </CFQUERY> <CFIF NOT ConfirmRecord.recordcount> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application: Deleting Record</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <I><FONT FACE="Arial" SIZE="3" Color="990000"><B>There is no Record Found For the current Applicant in the Database.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML> <CFABORT> </CFIF> <!--- Code for Deleting the Skills in Applicants_Skills Table for this Applicant. The detail Records should be deleted first due to foreign key constraints. ---> <CFQUERY NAME="DeleteSkillRecords" DATASOURCE="RecruitmentApp"> DELETE FROM Applicants_Skills WHERE ApplicantID = #URL.ID# </CFQUERY> <CFQUERY NAME="DeleteRecord" DATASOURCE="RecruitmentApp"> DELETE FROM Applicants WHERE ApplicantID = #URL.ID# </CFQUERY> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application: Updating Record Confirmation</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <I><FONT FACE="Arial" SIZE="3" Color="990000"><B>The Record has been Deleted Successfully.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Page</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML>
|
Listing 16.7 provides the complete code for the AddNewApplicant.cfm file.
Listing 16.7: AddNewApplicant.cfm
|
<CFQUERY NAME="get_skills" DATASOURCE="Recruitmentapp"> SELECT SkillID, Description FROM Skills ORDER BY SkillID </CFQUERY> <HTML> <HEAD> <TITLE>Recruitment Application: Adding New Applicant</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> <P><B><FONT FACE="Arial" SIZE="4" Color="990000"><U>Recruitment Application Adding New Applicant Details</U></FONT></B></P> <cfform ACTION="InsertRecord.cfm" METHOD="POST"> <TABLE> <TR> <TD><B>Applicant's Name: </B></TD> <TD><CFINPUT TYPE="Text" NAME="App_Name" SIZE="50" MAXLENGTH="50" REQUIRED="YES" MESSAGE="Name Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Address: </B></TD> <TD><CFINPUT TYPE="Text" NAME="App_Address" SIZE="50" MAXLENGTH="50" REQUIRED="YES" MESSAGE="Address Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Contact Number: </B></TD> <TD><CFINPUT TYPE="Text" Name="App_ContactNo" Size="15" MAXLENGTH="15" REQUIRED="YES" VALIDATE="Telephone" MESSAGE="Contact No Field is either Empty or not in Correct format(xxx-xxx-xxxx)."></TD> </TR> <TR> <TD><B>Applicant's Email Address: </B></TD> <TD><CFINPUT TYPE="TEXT" Name = "App_EmailAddress" size= "50" MAXLENGTH="100" REQUIRED="YES" MESSAGE="Email Address Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applied For: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_AppliedFor" Size = "30" MAXLENGTH="30" REQUIRED="YES" MESSAGE="Applied For Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Application Date: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_ApplicationDate" Size = "30" MAXLENGTH="10" REQUIRED="YES" VALIDATE="Date" MESSAGE="Application Date Field is either Empty or not in correct Format(mm/dd/yyyy)."></TD> </TR> <TR> <TD><B>Date Of Joining: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_DateOfJoining" Size = "30" MAXLENGTH="10" REQUIRED="NO" VALIDATE="Date"></TD> </TR> <TR> <TD><B>Applicant's Qualification Details: </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_QualDetails" Size = "50" MAXLENGTH="200" REQUIRED="YES" MESSAGE="Qualifications Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Experience (in Years): </B></TD> <TD><CFINPUT TYPE = "TEXT" Name = "App_ExpYears" Size = "50" MAXLENGTH = "2" REQUIRED="YES" MESSAGE="Exp. Years Field Cannot be Empty"></TD> </TR> <TR> <TD><B>Applicant's Skills: </B></TD> <TD><CFSELECT Name = "App_Skills" Size = "5" MULTIPLE="YES" QUERY="get_skills" Value="SkillID" DISPLAY="Description" REQUIRED="YES" MESSAGE="At Least One Skill Should be Selected."></CFSELECT></TD> </TR> </TABLE> <INPUT TYPE="Submit" NAME="btnSave" VALUE="Save Record"><BR> <a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"> <B>Back to Main Working Console</B></FONT></a> </cfform> </BODY> </HTML>
|
Listing 16.8 provides the complete code for the InsertRecord.cfm file.
Listing 16.8: InsertRecord.cfm
|
<CFSET varError = 0> <CFTRY> <CFTRANSACTION> <CFQUERY NAME="InsertRecord" DATASOURCE="RecruitmentApp"> INSERT INTO Applicants (ApplicantName, Address, ContactNo, EmailAddress, AppliedFor, ApplicationDate, DateOfJoining,QualificationDetails,ExperienceYears) VALUES ('#Form.App_Name#','#Form.App_Address#','#Form.App_ContactNo#', '#Form.App_EmailAddress#','#Form.App_AppliedFor#', '#Form.App_ApplicationDate#','#Form.App_DateOfJoining#', '#Form.App_QualDetails#',#Form.App_ExpYears#) </CFQUERY> <CFQUERY NAME = "get_AppID" DATASOURCE="RecruitmentApp"> SELECT Max(ApplicantID) as AppID FROM Applicants </CFQUERY> </CFTRANSACTION> <CFOUTPUT QUERY = "get_AppID"> <CFSET varAppID = #AppID#> </CFOUTPUT> <CFSET Length = ListLen(#Form.App_Skills#)> <CFLOOP INDEX = "element" FROM = "1" TO="#Length#"> <CFSET varSkillID = ListGetAt(#Form.App_Skills#,element)> <CFQUERY NAME="AddNew" DATASOURCE="RecruitmentApp"> INSERT INTO Applicants_Skills(ApplicantID,SkillID) VALUES (#varAppID#,'#varSkillID#') </CFQUERY> </CFLOOP> <CFCATCH type="database"> <DIV ALIGN ="center"> <FONT FACE = "Arial"> <H3> A Database Error Occurred.</H3> <HR> </FONT> </DIV> <FONT FACE = "Arial"> The Detailed Error Information is displayed below: <p> <CFOUTPUT> <B>TYPE</B>: #CFCATCH.TYPE#<BR> <B>MESSAGE</B>: #CFCATCH.MESSAGE#<BR> <B>DETAILS</B>: <FORM> <TEXTAREA COLS="80" ROWS = "2" NAME = "error_details">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA> </FORM> <CFSET varError = 1> </CFOUTPUT> </FONT> </CFCATCH> </CFTRY> <CFOUTPUT> <HTML><HEAD><TITLE>Recruitment Application: Inserting New Record</TITLE></HEAD> <BODY BGCOLOR="peachpuff"> <DIV ALIGN="center" Bgcolor="Orange"> <CFIF varError EQ 0> <FONT FACE="Arial" SIZE="4" COLOR="##ff0000">New Record Added for Applicant: #Form.App_Name#</FONT> </CFIF> </DIV> <P> <TABLE> <TR> <TD><a href ="AddNewApplicant.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Add Another Applicant's Details</B></FONT></a></TD> </TR> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML> </CFOUTPUT> |
Listing 16.9 provides the complete code for the SearchForm.cfm file.
Listing 16.9: SearchForm.cfm
|
<HTML><HEAD><TITLE>Recruitment Application: Search Form</TITLE></HEAD> <BODY BGCOLOR="peachpuff"> <P><B><FONT FACE="Arial" SIZE="4" Color="990000"><U>Recruitment Application: Search Console</U></FONT></B></P> <P> <FORM action = "SearchResults.cfm" Method = "POST"> <TABLE> <TR> <TD><FONT FACE="Arial" SIZE="2"><B>Applicant's Qualifications: </B></FONT></TD> <TD> <SELECT Name="QualOperator"> <OPTION Value = "EQUALS">is <OPTION Value = "BEGINS_WITH">begins with </SELECT> </TD> <TD> <INPUT type = "Text" Name = "QualValue"> </TD> </TR> <TR> <TD><FONT FACE="Arial" SIZE="2"><B>Applicant's Experience: </B></FONT></TD> <TD> <SELECT Name="ExpYearOperator"> <OPTION Value = "EQUALS">is <OPTION Value = "GREATER">more than <OPTION Value = "SMALLER">less than </SELECT> </TD> <TD> <INPUT type = "Text" Name = "ExpYearValue"> </TD> </TR> </TABLE> <P> <INPUT type = "Submit" Name = "Submit" Value = "Fetch Matching Records"><BR><BR> <a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"> <B>Back to Main Working Console</B></FONT></a> </FORM> </BODY> </HTML>
|
Listing 16.10 provides the complete code for the SearchResults.cfm file.
Listing 16.10: SearchResults.cfm
|
<CFSET TheWhereClause = " 1=1 "> <CFIF Form.QualValue GT ""> <CFIF Form.QualOperator EQ "EQUALS"> <CFSET TheWhereClause = TheWhereClause & " and QualificationDetails = '" & Form.QualValue & "'" > <CFELSE> <CFSET TheWhereClause = TheWhereClause & " and QualificationDetails LIKE '" & Form.QualValue & "%'" > </CFIF> </CFIF> <CFIF Form.ExpYearValue GT ""> <CFIF Form.ExpYearOperator EQ "EQUALS"> <CFSET TheWhereClause = TheWhereClause & " and ExperienceYears = " & Form.ExpYearValue > <CFELSEIF Form.ExpYearOperator EQ "GREATER"> <CFSET TheWhereClause = TheWhereClause & " and ExperienceYears > " & Form.ExpYearValue > <CFELSEIF Form.ExpYearOperator EQ "SMALLER"> <CFSET TheWhereClause = TheWhereClause & " and ExperienceYears < " & Form.ExpYearValue > </CFIF> </CFIF> <CFQUERY NAME="get_applicants_info" DATASOURCE="Recruitmentapp"> SELECT ApplicantID, ApplicantName, AppliedFor, ApplicationDate FROM Applicants WHERE #PreserveSingleQuotes(TheWhereClause)# </CFQUERY> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Recruitment Application List of Applicants</TITLE> </HEAD> <BODY BGCOLOR="peachpuff"> CFIF get_applicants_info.RECORDCOUNT IS 0> <I><FONT FACE="Arial" SIZE="3" Color="990000"><B>There are no Records Found For the Criteria Specified.</B></FONT></I> <P> <TABLE Align ="Left"> <TR> <TD><a href ="SearchForm.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Search More Applicants</B></FONT></a></TD> </TR> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML> <CFABORT> </CFIF> <P><B><FONT FACE="Arial" SIZE="4" Color="990000"> <U>Recruitment Application: Search Console</U></FONT></B></P> <P><B><FONT FACE="Arial" SIZE="3" Color="990000"> <U>List of Selected Applicants</U></FONT></B></P> <B><FONT FACE="Arial" SIZE="2" Color="990000"> (Click on the Applicant ID to work with detailed record.)</FONT></B> <TABLE BORDER="0" Align ="Left"> <TR bgcolor = "990000"> <TH>Applicant ID</TH> <TH>Applicant Name</TH> <TH>Applied For</TH> <TH>Application Date</TH> <TR> <CFOUTPUT QUERY = "get_applicants_info"> <CFIF CurrentRow Mod 2> <cfset BackColor = "peachpuff"> <CFELSE> <cfset BackColor = "Orange"> </CFIF> <TR bgcolor = #BackColor#> <TD><a href ="ApplicantDetails.cfm?ID=#URLEncodedFormat(ApplicantID)#"> #ApplicantID#</a></TD> <TD>#ApplicantName#</TD> <TD>#AppliedFor#</TD> <TD>#dateformat(ApplicationDate,"dddd, mmmm d, yyyy")#</TD> </TR> </CFOUTPUT> </TABLE> <TABLE Align ="Left"> <TR> <TD><a href ="SearchForm.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Search More Applicants</B></FONT></a></TD> </TR> <TR> <TD><a href ="MainPage.cfm"><FONT FACE="Arial" SIZE="2" Color="990000"><B>Back to Main Working Console</B></FONT></a></TD> </TR> </TABLE> </BODY> </HTML>
|
|
EAN: 2147483647
Pages: 200