增加和显示新的记录二
详细代码如下：
< %@LANGUAGE="VBSCRIPT"% >
< %
Dim commAddJob__CompanyID
commAdd Job__CompanyID = ""
if(Request("CompanyID") < > "") then
commAddJob__CompanyID = Request("CompanyID")
Dim commAddJob__JobTypeID
commAddJob__JobTypeID = ""
if(Request("JobTypeID") < > "") then
commAddJob__JobTypeID = Request("JobTypeID")
Dim commAddJob__RegionID commAddJob__RegionID = ""
if(Request("RegionID") < > "") then
commAddJob__RegionID = Request("RegionID")
Dim commAddJob__JobTitle commAddJob__JobTitle = ""
if(Request("JobTitle") < > "") then
commAddJob__JobTitle = Request("JobTitle")
Dim commAddJob__StartDate commAddJob__StartDate = ""
if(Request("StartDate") < > "") then
commAddJob__StartDate = Request("StartDate")
Dim commAddJob__CloseDate commAddJob__CloseDate = ""
if(Request("CloseDate") < > "") then
commAddJob__CloseDate = Request("CloseDate")
Dim commAddJob__JobDescription commAddJob__JobDescription = ""
if(Request("JobDescription") < > "") then commAddJob__JobDescription
= Request("JobDescription")
Dim commAddJob__Qualifications commAddJob__Qualifications = ""
if(Request("Qualifications") < > "") then
commAddJob__Qualifications = Request("Qualifications")
Dim commAddJob__SalaryBenefits commAddJob__SalaryBenefits = ""
if(Request("SalaryBenefits") < > "") then
commAddJob__SalaryBenefits = Request("SalaryBenefits")
Dim commAddJob__Resp commAddJob__Resp = ""
if(Request("Resp") < > "") then commAddJob__Resp =
Request("Resp")
Dim commAddJob__Availability commAddJob__Availability = ""
if(Request("Availability") < > "") then
commAddJob__Availability = Request("Availability")
Dim commAddJob__ToApply commAddJob__ToApply = ""
if(Request("ToApply") < > "") then
commAddJob__ToApply = Request("ToApply")
Dim commAddJob__EmailJobs commAddJob__EmailJobs = ""
if(Request("EmailJobs") < > "") then
commAddJob__EmailJobs = Request("EmailJobs")
Dim commAddJob__CategoryID commAddJob__CategoryID = ""
if(Request("CategoryID") < > "") then
commAddJob__CategoryID = Request("CategoryID")
Dim commAddJob__PopulationID commAddJob__PopulationID = ""
if(Request("PopulationID") < > "") then
commAddJob__PopulationID = Request("PopulationID")
% >
< %
set commAddJob = Server.CreateObject("ADODB.Command")
commAddJob.ActiveConnection = "dsn=Jobs;" commAddJob.CommandText =
"dbo.spAddJob" commAddJob.Parameters.Append
commAddJob.CreateParameter("RETURN_VALUE", 3, 4)
commAddJob.Parameters.Append commAddJob.CreateParameter("@CompanyID",
3, 1, 4, commAddJob__CompanyID) commAddJob.Parameters.Append
commAddJob.CreateParameter("@JobTypeID", 3, 1, 4,
commAddJob__JobTypeID) commAddJob.Parameters.Append
commAddJob.CreateParameter("@RegionID", 3, 1, 4,
commAddJob__RegionID) commAddJob.Parameters.Append commAddJob.CreateParameter("@JobTitle",
200, 1, 100, commAddJob__JobTitle) commAddJob.Parameters.Append
commAddJob.CreateParameter("@StartDate", 135, 1, 8,
commAddJob__StartDate) commAddJob.Parameters.Append
commAddJob.CreateParameter("@CloseDate", 135, 1, 8,
commAddJob__CloseDate) commAddJob.Parameters.Append
commAddJob.CreateParameter("@JobDescription", 200, 1, 4000,
commAddJob__JobDescription) commAddJob.Parameters.Append
commAddJob.CreateParameter("@Qualifications", 200, 1, 4000,
commAddJob__Qualifications) commAddJob.Parameters.Append commAddJob.CreateParameter("@SalaryBenefits",
200, 1, 4000, commAddJob__SalaryBenefits) commAddJob.Parameters.Append
commAddJob.CreateParameter("@Resp", 200, 1, 4000, commAddJob__Resp)
commAddJob.Parameters.Append commAddJob.CreateParameter("@Availability",
200, 1, 200, commAddJob__Availability) commAddJob.Parameters.Append
commAddJob.CreateParameter("@ToApply", 200, 1, 2000,
commAddJob__ToApply) commAddJob.Parameters.Append
commAddJob.CreateParameter("@EmailJobs", 200, 1, 100,
commAddJob__EmailJobs) commAddJob.Parameters.Append
commAddJob.CreateParameter("@CategoryID", 3, 1, 4,
commAddJob__CategoryID) commAddJob.Parameters.Append
commAddJob.CreateParameter("@PopulationID", 3, 1, 4,
commAddJob__PopulationID) commAddJob.Parameters.Append commAddJob.CreateParameter("@JobID",
3, 2) commAddJob.CommandType = 4 commAddJob.CommandTimeout = 0
commAddJob.Prepared = true commAddJob.Execute()
% >
< %
Dim rsJobSearch__JobID rsJobSearch__JobID = "1"
if(commAddJob.Parameters.Item("@JobID").Value < > "")
then rsJobSearch__JobID = commAddJob.Parameters.Item("@JobID").Value
% >
< %
Dim rsJobCategories__JobID rsJobCategories__JobID = "1"
if(commAddJob.Parameters.Item("@JobID").Value < > "")
then rsJobCategories__JobID =
commAddJob.Parameters.Item("@JobID").Value
% >
< %
set rsJobCategories = Server.CreateObject("ADODB.Recordset")
rsJobCategories.ActiveConnection = "dsn=Jobs;" rsJobCategories.Source
= "{call dbo.spJobCategories(" + Replace(rsJobCategories__JobID,
"'", "''") + ")}" rsJobCategories.CursorType = 3
rsJobCategories.CursorLocation = 3
rsJobCategories.LockType = 1
rsJobCategories.Open rsJobCategories_numRows = 0
% >
< %
set rsJobSearch = Server.CreateObject("ADODB.Recordset")
rsJobSearch.ActiveConnection = "dsn=Jobs;"
rsJobSearch.Source = "{call dbo.spJobID(" + Replace(rsJobSearch__JobID,
"'", "''") + ")}"
rsJobSearch.CursorType = 0
rsJobSearch.CursorLocation = 3
rsJobSearch.LockType = 3
rsJobSearch.Open rsJobSearch_numRows = 0
% >
< html >< head >
总体解释
我们使用存储过程来插入数据，因为我们要插入记录到两个表格（Jobs和JobCategory）中去。当用户在AddJobs.asp表单中点击Add Job
(增加工作)按钮，它将作为Request(请求)变量发送所有的表单数值给AddConfirm.asp页面。当网页装载时候这个commJobAdd存储过程运行。可以利用Request变量和使用信息来插入数据到Jobs表格中。
Jobs表格使用JobID作为主键区域。SQL Server 7用一个特殊数据库数值，即@@标识，它等于主键区域的数值，可以用于新插入记录。这个存储记录使用@@标识数值作为JobID。
你会注意到我们也创建了存储过程来传递一个OUTPUT数值给@JobID，@JobID示新插入工作记录的JobID数值。这个JobID数值首先传递给spJobSearch存储过程（这个过程集中了rsJobSearch记录集），同时传递给spJobCategories存储过程（这个过程集中了rsJobCategories记录集）并且显示所有的新增工作信息给用户。
Stored Procedures(存储过程)
以下是spCompanyLogin的代码：
Alter Procedure sp_CompanyLogin
(@CompanyID int, @Password varchar)
AS
SELECT CategoryType.Category1, CompanyType.CompanyType,
Population.PopulationType, Region.Region, Company.*
FROM CategoryType INNER JOIN
Company ON CategoryType.Category1ID = Company.CategoryTypeID INNER JOIN
CompanyType ON Company.CompanyTypeID = CompanyType.CompanyTypeID INNER JOIN
Population ON Company.PopulationID = Population.PopulationID INNER JOIN
Region ON Company.RegionID = Region.RegionID
WHERE CompanyID = @CompanyID AND Password = @Password AND Verify = 1
而spCategory代码如下：
Alter Procedure spCategory
As
SELECT * from CategoryType
ORDER BY Category1
return
commJobAdd代码如下：
Alter PROCEDURE spAddJob
-- Declare variables for inserts to 2 tables
(@CompanyID [int], @JobTypeID [int], @RegionID [int], @JobTitle [varchar](100),
@StartDate [datetime], @CloseDate [datetime], @JobDescription [varchar](4000),
@Qualifications [varchar](4000), @SalaryBenefits [varchar](4000), @Resp
[varchar](4000), @Availability [varchar](200), @ToApply [varchar](2000),
@EmailJobs [varchar](100), @CategoryID [int], @JobID [int] OUTPUT)
AS
-- Insert into Jobs table
INSERT INTO Jobs ([CompanyID], [JobTypeID], [RegionID], [JobTitle],
[StartDate], [CloseDate], [JobDescription], [Qualifications], [SalaryBenefits],
[Resp], [Availability], [ToApply], [EmailJobs])
VALUES (@CompanyID, @JobTypeID, @RegionID, @JobTitle, @StartDate, @CloseDate,
@JobDescription, @Qualifications, @SalaryBenefits, @Resp, @Availability,
@ToApply, @EmailJobs)
-- Retrieve the automatically generated JobID VALUE from the Jobs table
SET @JobId = @@IDENTITY
-- Insert new values into JobCategory table
INSERT INTO JobCategory (JobID, CategoryID)
VALUES (@JobID, @CategoryID)
Return
spJobSearch代码如下：
Alter Procedure spJobID
@JobID int
As
SELECT Jobs.*, Company.*, Region.Region, JobType.JobType
FROM Jobs INNER JOIN
Region ON Jobs.RegionID = Region.RegionID INNER JOIN
JobType ON Jobs.JobTypeID = JobType.JobTypeID INNER JOIN
Company ON Jobs.CompanyID = Company.CompanyID
WHERE JobID = @JobID AND Jobs.Verify = 1
SpJobCategories代码如下：
Alter Procedure spJobCategories
@JobID int
As
SELECT JobCategory.JobID, JobCategory.CategoryID, CategoryType.Category
FROM CategoryType INNER JOIN JobCategory
ON JobCategory.CategoryID = CategoryType.CategoryID
WHERE JobCategory.JobID = @JobID ORDER BY CategoryType.Category
return