ASP 3.0 Advanced Programming (42)

Recommended: Use ASP to implement the online examination system
With the development of Internet technology, online teaching will become the main form of people's re-education and lifelong education. In online schools, people can choose any course anywhere, anywhere without being restricted by time and space. The development of online schools proposes the development of online examinations

The processing of function return values ​​is different from the processing of stored procedures, which often leads to confusion. In functions, a Boolean value is often returned to indicate whether the function runs successfully or not.

If SomeFunctionName() = True Then

' Function succeeded

However, when calling a stored procedure, the same method cannot be used because the storage is run with the Execute method and returns a record set.

Set rsAuthors = cmdAuthors.Execute

If you cannot get a return value, how do you determine whether the stored procedure has been executed correctly? When an error occurs, an error is reported so that the error handling code provided in the previous chapter can be used to handle the error. But what to do with some non-fatal logical errors?

For example, consider adding a new employee to the employee table. You may not want to prevent two employees from having the same name, but you want to indicate this situation. Then, a return value can be used to indicate whether a clerk with the same name already exists. The stored procedure is as follows:

CREATE PROCEDURE usp_AddEmployee

@Emp_ID Char(9),

@FName Varchar(20),

@Minit Char(1),

@LName Varchar(30),

@Job_ID SmallInt,

@Job_Lvl TinyInt,

@Pub_ID Char(4),

@Hire_Date Datetime

AS

BEGIN

DECLARE @Exists Int -- Return value

-- See if an employee with the same name exists

IF EXISTS(SELECT *

FROM Employee

WHERE FName = @FName

AND MInit = @MInit

AND LName = @LName)

SELECT @Exists = 1

ELSE

SELECT @Exists = 0

INSERT INTO Employee (emp_id, fname, minit, lname,

job_id, job_lvl, pub_id, hire_date)

VALUES (@Emp_Id, @FName, @MInit, @LName, @Job_ID,

@Job_Lvl, @Pub_ID, @Hire_Date)

RETURN @Exists

END

The process first checks whether there are employees with the same name, and sets the corresponding variable Exists accordingly. If the same name exists, it is set to 1, otherwise it is 0. Then add the staff to the table, and return the value of Exists as the return value.

Note that although a value is returned, it is not declared as a parameter to the stored procedure.

The ASP code that calls this procedure is as follows:

<!-- #INCLUDE FILE=../include/Connection.asp -->

<%

Dim cmdEmployee

Dim lngRecs

Dim lngAdded

Set cmdEmployee = Server.CreateObject(ADODB.Command)

' Set the properties of the command

With cmdEmployee

.ActiveConnection = strConn

.CommandText = usp_AddEmployee

.CommandType = adCmdStoredProc

' Create the parameters

' Notice that the return value is the first parameter

.Parameters.Append .CreateParameter (RETURN_VALUE, adInteger, _

adParamReturnValue)

.Parameters.Append .CreateParameter (@Emp_id, adChar, adParamInput, 9)

.Parameters.Append .CreateParameter (@fname, adVarWChar, adParamInput, 20)

.Parameters.Append .CreateParameter (@minit, adChar, adParamInput, 1)

.Parameters.Append .CreateParameter (@lname, adVarWChar, adParamInput, 30)

.Parameters.Append .CreateParameter (@job_id, adSmallInt, adParamInput)

.Parameters.Append .CreateParameter (@job_lvl, adUnsignedTinyInt, adParamInput)

.Parameters.Append .CreateParameter (@pub_id, adChar, adParamInput, 4)

.Parameters.Append .CreateParameter (@hire_date, adDBTimeStamp, _

adParamInput, 8)

' Set the parameter values

.Parameters(@Emp_id) = Request.Form(txtEmpID)

.Parameters(@fname) = Request.Form(txtFirstName)

.Parameters(@minit) = Request.Form(txtInitial)

.Parameters(@lname) = Request.Form(txtLastName)

.Parameters(@job_id) = Request.Form(lstJobs)

.Parameters(@job_lvl) = Request.Form(txtJobLevel)

.Parameters(@pub_id) = Request.Form(lstPublisher)

.Parameters(@hire_date) = Request.Form(txtHireDate)

' Run the stored procedure

.Execute lngRecs, , adExecuteNoRecords

' Extract the return value

lngAdded = .Parameters(RETURN_VALUE)

End With

Response.Write New employee added.<P>

If lngAdded = 1 Then

Response.Write An employee with the same name already exists.

End If

Set cmdEmployee = Nothing

%>

It should be noted that the return value should be created as the first parameter in the set. Even if the return value does not appear as a parameter in the stored procedure, it is always the first Parameters in the Parameters collection.

Share: ASP 3.0 Advanced Programming (Thirty-six)
Chapter 8 Basics of ADO In the first 7 chapters of this book, it has already talked about the content of ASP and how ASP brings dynamic content to the Web site. It has been seen that its scripting program allows custom web pages, allowing us to build more powerful ASP pages. Now, ASP will be studied