CREATE procedure JobAdd
@PositionTitle nvarchar(50),
@ReferenceNo nvarchar(10),
@Location nvarchar(30),
@ClosingDate smalldatetime,
@JobDescription nvarchar(1000),
@Requirements nvarchar(1000), 
@Email varchar(75), 
@JobID int output
AS
INSERT INTO JobVacancies
(
PositionTitle,
ReferenceNo,
Location,
ClosingDate,
JobDescription,
Requirements,
Email 
 
)
VALUES
(
@PositionTitle,
@ReferenceNo,
@Location,
@ClosingDate,
@JobDescription,
@Requirements,
@Email 
 
)
SELECT @JobID = @@IDENTITY
GO

 
PrivateFunction AddVacancyDetails() As Int32
 
Dim myConnection AsNew SqlConnection(ConfigurationSettings.AppSettings("strSqlConnectionString")) 
Dim myCommand = New SqlCommand("jobAdd", myConnection) 
myCommand.CommandType = CommandType.StoredProcedure 
 
Dim paramPosition As SqlParameter = myCommand.Parameters.Add("@PositionTitle", SqlDbType.NVarChar, 50) 
paramPosition.Value = txtPosition.Text 
 
Dim paramLocation As SqlParameter = myCommand.Parameters.Add("@Location", SqlDbType.NVarChar, 30) 
paramLocation.Value = txtLocation.Text
 
Dim paramClose As SqlParameter = myCommand.Parameters.Add("@ClosingDate", SqlDbType.SmallDateTime) 
paramClose.Value = dtClose.SelectedDate.ToShortDateString()
 
Dim paramRef As SqlParameter = myCommand.Parameters.Add("@ReferenceNo", SqlDbType.NVarChar, 4) 
paramRef.Value = txtRef.Text.ToUpper
 
Dim paramDescription As SqlParameter = myCommand.Parameters.Add("@JobDescription", SqlDbType.NVarChar, 1000) 
If txtJobDescription.Text = "" Then 
paramDescription.Value = DBNull.Value 
Else 
paramDescription.Value = txtJobDescription.Text 
EndIf
 
Dim paramRequirements As SqlParameter = myCommand.Parameters.Add("@Requirements", SqlDbType.NVarChar, 1000) 
If txtReq.Text = "" Then 
paramRequirements.Value = DBNull.Value 
Else 
paramRequirements.Value = txtReq.Text 
EndIf
 
Dim paramEmail As SqlParameter = myCommand.Parameters.Add("@Email", SqlDbType.VarChar, 75) 
Dim at AsString 
at = txtEmail.Text.IndexOf("@") 
If at = -1 Then 
paramEmail.Value = txtEmail.Text & "@goldenwest.org.au" 
Else 
paramEmail.Value = txtEmail.Text 
EndIf
 
Dim paramJobID As SqlParameter = myCommand.Parameters.Add("@JobID", SqlDbType.Int) 
paramJobID.Direction = ParameterDirection.Output 
myConnection.Open() 
myCommand.ExecuteNonQuery() 
Return Convert.ToInt32(paramJobID.Value)
 
EndFunction
 

CREATE procedure jobGetDetails
@JobID int,
@PositionTitle nvarchar(50) output,
@ReferenceNo nvarchar(10) output,
@Location nvarchar(30) output,
@ClosingDate smalldatetime output,
@JobDescription nvarchar(1000) output,
@Requirements nvarchar(1000) output, 
@Email varchar(75) output
 
AS
SELECT
@JobID = JobID,
@PositionTitle = PositionTitle,
@ReferenceNo = ReferenceNo ,
@Location = Location,
@ClosingDate = ClosingDate ,
@JobDescription = JobDescription,
@Requirements = Requirements, 
@Email = Email
FROM JobVacancies
WHERE JobID = @JobID
 
IF @@ROWCOUNT = 0
SET @JobID = 0
GO

PrivateSub ShowEdit(ByVal JobID As Int32)
 
Dim myConnection AsNew SqlConnection(ConfigurationSettings.AppSettings("strSqlConnectionString"))
Dim myCommand = New SqlCommand("jobGetDetails", myConnection)myCommand.CommandType = CommandType.StoredProcedure
 
Dim paramJobID As SqlParameter = myCommand.Parameters.Add("@JobID", SqlDbType.Int)
paramJobID.Value = JobID
 
Dim paramPosition As SqlParameter = myCommand.Parameters.Add("@PositionTitle", SqlDbType.NVarChar, 50)
paramPosition.Direction = ParameterDirection.Output

Dim paramRef As SqlParameter = myCommand.Parameters.Add("@ReferenceNo", SqlDbType.NVarChar, 4)
paramRef.Direction = ParameterDirection.Output

Dim paramLocation As SqlParameter = myCommand.Parameters.Add("@Location", SqlDbType.NVarChar, 30)
paramLocation.Direction = ParameterDirection.Output

Dim paramClose As SqlParameter = myCommand.Parameters.Add("@ClosingDate", SqlDbType.SmallDateTime)
paramClose.Direction = ParameterDirection.Output

Dim paramDescription As SqlParameter = myCommand.Parameters.Add("@JobDescription", SqlDbType.NVarChar, 1000)
paramDescription.Direction = ParameterDirection.Output

Dim paramReq As SqlParameter = myCommand.Parameters.Add("@Requirements", SqlDbType.NVarChar, 1000)
paramReq.Direction = ParameterDirection.Output

Dim paramEmail As SqlParameter = myCommand.Parameters.Add("@Email", SqlDbType.VarChar, 75)
paramEmail.Direction = ParameterDirection.Output

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
 
txtPosition.Text = paramPosition.Value
txtRef.Text = paramRef.Value
txtLocation.Text = paramLocation.Value
txtEmail.Text = paramEmail.Value
 
If paramReq.Value Is DBNull.Value Then
txtReq.Text = ""
Else
txtReq.Text = paramReq.Value
EndIf

If paramDescription.Value Is DBNull.Value Then
txtJobDescription.Text = ""
Else
txtJobDescription.Text = paramDescription.Value
EndIf

dtClose.SelectedDate = paramClose.Value
txtEmail.Text = txtEmail.Text.Remove(txtEmail.Text.IndexOf("@"), 18)
 
If dtClose.SelectedDate < dtClose.TodaysDate Then
lblStatus.Text = "Status:" & "&nbsp;" & "Expired Vacancy"
Else
lblStatus.Text = "Status:" & "&nbsp;" & "Current Vacancy"
EndIf
EndSub