server| stored Procedures | data | Database in our user authentication program, it is easy to use ASP to call the SQL statements to retrieve the data table is a condition-consistent record, and then use ASP for related processing.
all Roads Pass Rome! Of course, we can also use SQL Server database stored procedures to easily implement this functionality. Although relatively complex, but the improvement of its efficiency is very obvious, because the stored procedure is in the database has been compiled a good program, we need to use the ASP to use the various parameters of the correct transmission.
This article is also mainly to pass a simple example, to tell you how to call the stored procedures with parameters in ASP. I hope you can get more inspiration from it.
first step, set up data table UserInfo
ID int (4) not NULL,
fullname varchar (m) NOT NULL,
password varchar () NOT NULL,
nikename varchar (m) NOT null
The second step, establish the stored procedure Usercheck
CREATE procedure Usercheck
@infullname varchar (50),
@inpassword varchar (50),
@outcheck char (3) OUTPUT
as
if exists (SELECT * from UserInfo where fullname= @infullname and password= @inpassword)
Select @outcheck = ' Yes '
Else
Select @outcheck = ' No '
Note: Here is a stored procedure with three parameters, the first parameter @infullname, this is an input parameter, (user name), the second parameter @inpassword, is also an input parameter, (password), the third parameter @outcheck, this is an output parameter, (whether this user exists), the word "output" must be added after the data type when defining an export parameter.
then, we take the top two input parameters to retrieve the existence of a qualified user in the SQL statement, or "no" if there is a value for the output parameter.
third Step, write ASP program, call stored procedure
<%
' form submission flag
If request ("OK") =1 then
' Establish a database connection
Set comm=server.createobject ("Adodb.command")
comm.activeconnection= "Dsn=localserver; Uid=sa; pwd=;D Atabase=chaiwei "
' establishes a stored procedure connection with the Comm object, and 4 represents the connection type as a stored procedure
comm.commandtext= "Usercheck"
comm.commandtype=4
' parameter method for establishing the Comm object with P1 name. Appends the first parameter fullname to the P1 collection
' FullName The name of the first argument invoked
' 200 parameter type varchar type
' 1 parameter flow input, input is 1, output is 2
' 50 parameters of length 50
' Request (' FullName ') assigns the parameter to the beginning value
Set p1=comm.createparameter ("FullName", 200,1,50,request ("FullName"))
Comm.Parameters.Append P1
' parameter method for establishing the Comm object with P1 name. Appends the second parameter password to the P1 collection
' specific Ibid
Set p1=comm.createparameter ("Password", 200,1,20,request ("password"))
Comm.Parameters.Append P1
' parameter method for establishing the Comm object with P1 name. Appends the third parameter check to the P1 collection
' 129 parameter type char
' 2 parameter flow to output
' 3 parameter length 3
Set p1=comm.createparameter ("Check", 129,2,3)
Comm.Parameters.Append P1
' Run stored procedures
Comm.execute
' bring out the result and deal with it
If comm ("check") = "yes" then
Response.Write "Welcome into the system!" User name: "& Comm (" FullName ") &" Password: "& Comm (" Password ")
Else
Response.Write "Sorry, you have not registered!" "
End If
' Release connection
Set comm=nothing
Else
' form part
%>
<html>
<head>
<meta http-equiv= "Content-type" content= "text/html"; charset=gb2312 ">
<meta name= "generator" content= "Microsoft FrontPage 4.0" >
<meta name= "ProgId" content= "FrontPage.Editor.Document" >
<title>new Page 1</title>
</head>
<body>
<form method= "POST" action= "check.asp" >
<p><input type= "text" Name= "FullName" size= "><br>
"
<input type= "password" name= "password" size= "></p>
"
<p><input type= "Submit" value= "submitted" name= "B1" ></p>
<input type= "hidden" name= "OK" value= "1" >
</form>
</body>
</html>
<%
End If
%>