1. Pass parameters using Command objects and Parameter objects. This lecture will mainly use the Microsoft SQL Server 7.0 database. First create a connection file to be used for backup. No special explanation will be given when it is used later.
% ''
Option Explicit
= 0
''Part 1: Establish a connection
Dim Cnn, StrCnn
Set Cnn = ("")
StrCnn = "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"
StrCnn
%>
Note: When using it yourself, set Data Source as the machine name where your database server is located.
In addition, when using Access databases in the past, Microsoft Access97 could easily view fields and data. When using SQL Server databases, especially when debugging ASP scripts on another machine, you need to install additional tools to view fields and data. Here is a tool for you: (Microsoft Query). This file is installed with Office97 and is generally located in the directory "Microsoft Office\Office".
example:
%@ LANGUAGE="VBSCRIPT" %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest, rsTest
''Create Command object
Set cmdTest = ("")
‘Recordset and Command objects can be connected to Connection objects through the ActiveConnection property.
= Cnn
''SQL command - contains two parameters, use? express
= "Update jobs Set job_desc = ? Where job_id = ?"
''Suppose the command type is SQL statement
= adCmdText
''Prepared attribute determines whether to compile the SQL command first, set it to True to speed up the run
= True
''Create Parameter object
Set prmTest=("job_desc",adVarChar,adParamInput,50,"network")
''Append data to Parameters data set
prmTest
Set prmTest = ("job_id",adSmallInt,adParamInput,,"12")
prmTest
''Execute modification – There is no need to return the result, just use it simply
''Reset parameters and run - you can modify another piece of data
("job_id") = "1"
("job_desc") = "test"
''Reset parameters to run
cmdTest("job_id") = "14"
cmdTest("job_desc") = "financial"
Set rsTest = ("Select job_id,job_desc From jobs")
While Not
rsTest(0) & rsTest(1) & " "
Wend
: Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
analyze:
1. The CreateParameter method of the Command object is used to create parameter objects for SQL commands or stored procedures. There are five parameters (the five parameters are optional):
The first parameter: the name of the parameter object;
The second parameter: There are too many types of data types of parameter objects. Please refer to the ADO help, here adVarChar (string value), adSmallInt (2 byte signed integer);
The third parameter: parameter type. It can be: adParamInput (indicates input parameters), adParamOutput (indicates as output parameters), adParamReturnValue (indicates as return value), adParamUnknown (indicates that the parameter type cannot be determined), adParamInputOutput (indicates as input/output parameters);
The fourth parameter: The data length of the parameter is preferably specified to be equal to the corresponding field length in the database to avoid errors during use. Especially when the data type is VarChar, if it is an integer or a date type, the value is not necessary;
The fifth parameter: the initial value of the parameter setting.
2. The method adds a Parameter object to the Parameters data set, and you can also see how to use multiple parameters in this example.
3. From this example, we can see that you only need to reset the input parameters to perform modifications to other data, which is very convenient. This idea is also one of the most commonly used methods when programming.
4. Reset parameters can be used or omitted as cmdTest("job_id").
2. Use stored procedures in ASP
What is a stored procedure (a stored procedure is located in the database server and is a collection of SQL statements that can contain one or more SQL statements), and how to create stored procedures does not belong to the content of this lecture. This lecture mainly illustrates how to call stored procedures in ASP.
The benefits of using stored procedures are great, which are more efficient than running SQL commands in ASP scripts; they can improve overall performance and reduce network load (reduce interaction between network servers and data servers); they can optimize ASP code and enhance code flexibility, and so on.
(I) Use input parameters in stored procedures
The stored procedure used in this example is "byroyalty" that comes with SQL Server 7.0. A SQL statement in it is very simple, it is nothing more than an additional CREATE PROCEDURE byroyalty, and an input parameter is @percentage:
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where = @percentage
serve by
example
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest, rsTest
Set cmdTest = ("")
= "byroyalty" ''Stored procedure name
''Suppose the command type is stored procedure
= adCmdStoredProc
''Create Parameter object
Set prmTest = ("")
The second parameter in the Type property corresponds to
= adInteger ''4 byte signed integer
The third parameter in the Direction property
= adParamInput
The fifth parameter in the Value attribute
= 30
prmTest
Set = Cnn
‘A record set needs to be returned, so use Set rsTest =
Set rsTest =
While Not
rsTest(0) & " "
Wend
Set rsTest = Nothing : Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
The CommandText property can either specify SQL commands, or as stored procedures or table names.
In this example, creating a Parameter object is slightly different. In fact, if you look closely, the meaning is similar. There are two properties in this example that are not used: , and add Type, Direction and Value, corresponding to the five parameters in it.
(II) Use output parameters
When obtaining a record from a database table or calculating a value, a stored procedure that returns the output parameters need to be used. To give an example, first create a new stored procedure OUTemploy in the pubs library of SQL Server. The stored procedure needs to enter two dates and then output a maximum value.
CREATE PROCEDURE OUTemploy
(
@job_lvl tinyint OUTPUT,
@hire_date1 datetime,
@hire_date2 datetime
)
AS
select @job_lvl = MAX(job_lvl) from employee
where hire_date >= @hire_date1 and hire_date = @hire_date2
There are several ways to create stored procedures:
1. Use Microsoft SQL Server's Enterprise Manager, open it in turn in the tree directory on the left: Console Root – Microsoft SQL Servers – SQL Server Group – ICBCZJP(Windows NT) – databases – pubs – stored procedure – New stored procedure. After entering the stored procedure, it can also be syntaxly detected;
2. Using Microsoft SQL Server's Query Analyzer, first connect to the database server and select the pubs database. Enter the stored procedure above and click Execute Query (or press F5);
3. With VB6.0, after opening the menu "View"/"Data View Window", right-click "Data Link"/"New Data Link";
4. Create stored procedures using ASP scripts, example:
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim StrSQL
''Note: & Chr(10) & Chr(13) You can do it at all, mainly for the sake of good looks
StrSQL="CREATE PROCEDURE OUTemploy ( @job_lvl tinyint OUTPUT, " & Chr(10) & Chr(13) &_
"@hire_date1 datetime, @hire_date2 datetime) AS " & Chr(10) & Chr(13) &_
"select @job_lvl = MAX(job_lvl) from employee " &_
"where hire_date >= @hire_date1 and hire_date = @hire_date2"
StrSQL
"Creating stored procedure successfully"
: Set Cnn = Nothing
%>
After the stored procedure is created, in addition to using the menu, you can also delete it using the SQL statement "Drop Procedure OUTemploy".
Example – Send the required input parameters into the stored procedure and obtain the output result.
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest
Set cmdTest = ("")
= Cnn
= "OUTemploy" '''Stored procedure name
= adCmdStoredProc
''Create Parameter object
Set prmTest = ("job_lvl",adTinyInt,adParamOutput)
prmTest
''adTinyInt - 1 byte signed integer
''adDbDate - Date value (yyyymmdd)
Set prmTest = ("hiredate1",adDBDate,adParamInput,,"1993-05-09")
prmTest
Set prmTest = ("hiredate2",adDBDate,adParamInput,,"1994-02-01")
prmTest
‘The following three expressions mean the same
cmdtest("job_lvl") & " "
("job_lvl") & " "
("job_lvl").Value
Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
(III) Use return code parameters
Use the Return statement to return different return codes from stored procedures. For example, the stored procedure first obtains a record set, and then, if there is an employee named Margaret, it will return 1, otherwise it will return 0.
Create Procedure Returnemploy
AS
select emp_id, fname from employee
If Exists(Select fname From employee Where fname=''Margaret'')
Return(1)
Else
Return(0)
example
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest, rsTest
Set cmdTest = ("")
= Cnn
= "Returnemploy" ''Stored procedure name
= adCmdStoredProc
Set prmTest = ("ReturnValue",adInteger,adParamReturnValue)
prmTest
Set rsTest = ()
While Not
rsTest(0) & " ][ " & rsTest(1) & " "
Wend
: Set rsTest = Nothing
''Before returning cmdtest("ReturnValue") , rsTest must be closed first, otherwise the result will be incorrect
If cmdtest("ReturnValue") = 1 Then
"There is this employee"
Else
"No employee"
End If
Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
3. How to deal with big data
The "big data" here mainly refers to the Text (large text) and image (image) fields. The data cannot be obtained correctly using the methods described above. You must first use Size = rsTest(0).ActualSize to get the actual length of the field value, and then use rsTest(0).GetChunk(Size) to get the data. In actual use, since these fields are relatively large, in order to save and use server resources reasonably, segmented reading is generally adopted. example:
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim StrSQL, rsTest
‘pr_info is a text field
StrSQL = "Select pr_info,pub_id From pub_info"
Set rsTest = (StrSQL)
Dim BasicSize, BeginSize, LText
Do While Not
rsTest(1) & " "
‘1024 bytes per read
BasicSize = 1024
BeginSize = 0
While BeginSize rsTest(0).ActualSize
LText = rsTest(0).GetChunk(BasicSize)
BeginSize = BeginSize + BasicSize
‘Output segment by segment to client
LText
Wend
""
Loop
Set rsTest = Nothing: Set Cnn = Nothing
%>
In this example, a maximum of 1024 bytes are read each time and read them in multiple times. On the contrary, if you write big data to the database, the method is similar to the above, but instead of using the GetChunk method, you use the AppendChunk method:
rsTest(0).AppendChunk Ltext
Note: Finally, I will introduce a little tip about SQL Server database. If you have encountered this situation: the Chinese data in the database is displayed in garbled code, please don’t panic. You just need to go to my site to download the file with the same name under "C:\Windows\System". The source of the problem is the SQL Server driver, which typically occurs in Windows 98 version 2 (the version number of the SQL Server driver is 3.70.06.23) or Windows 2000 or MDAC2.5 is installed (version number is 3.70.08.20).
% ''
Option Explicit
= 0
''Part 1: Establish a connection
Dim Cnn, StrCnn
Set Cnn = ("")
StrCnn = "Provider=sqloledb; User ID=sa; Password=; Initial Catalog=pubs; Data Source=ICBCZJP"
StrCnn
%>
Note: When using it yourself, set Data Source as the machine name where your database server is located.
In addition, when using Access databases in the past, Microsoft Access97 could easily view fields and data. When using SQL Server databases, especially when debugging ASP scripts on another machine, you need to install additional tools to view fields and data. Here is a tool for you: (Microsoft Query). This file is installed with Office97 and is generally located in the directory "Microsoft Office\Office".
example:
%@ LANGUAGE="VBSCRIPT" %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest, rsTest
''Create Command object
Set cmdTest = ("")
‘Recordset and Command objects can be connected to Connection objects through the ActiveConnection property.
= Cnn
''SQL command - contains two parameters, use? express
= "Update jobs Set job_desc = ? Where job_id = ?"
''Suppose the command type is SQL statement
= adCmdText
''Prepared attribute determines whether to compile the SQL command first, set it to True to speed up the run
= True
''Create Parameter object
Set prmTest=("job_desc",adVarChar,adParamInput,50,"network")
''Append data to Parameters data set
prmTest
Set prmTest = ("job_id",adSmallInt,adParamInput,,"12")
prmTest
''Execute modification – There is no need to return the result, just use it simply
''Reset parameters and run - you can modify another piece of data
("job_id") = "1"
("job_desc") = "test"
''Reset parameters to run
cmdTest("job_id") = "14"
cmdTest("job_desc") = "financial"
Set rsTest = ("Select job_id,job_desc From jobs")
While Not
rsTest(0) & rsTest(1) & " "
Wend
: Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
analyze:
1. The CreateParameter method of the Command object is used to create parameter objects for SQL commands or stored procedures. There are five parameters (the five parameters are optional):
The first parameter: the name of the parameter object;
The second parameter: There are too many types of data types of parameter objects. Please refer to the ADO help, here adVarChar (string value), adSmallInt (2 byte signed integer);
The third parameter: parameter type. It can be: adParamInput (indicates input parameters), adParamOutput (indicates as output parameters), adParamReturnValue (indicates as return value), adParamUnknown (indicates that the parameter type cannot be determined), adParamInputOutput (indicates as input/output parameters);
The fourth parameter: The data length of the parameter is preferably specified to be equal to the corresponding field length in the database to avoid errors during use. Especially when the data type is VarChar, if it is an integer or a date type, the value is not necessary;
The fifth parameter: the initial value of the parameter setting.
2. The method adds a Parameter object to the Parameters data set, and you can also see how to use multiple parameters in this example.
3. From this example, we can see that you only need to reset the input parameters to perform modifications to other data, which is very convenient. This idea is also one of the most commonly used methods when programming.
4. Reset parameters can be used or omitted as cmdTest("job_id").
2. Use stored procedures in ASP
What is a stored procedure (a stored procedure is located in the database server and is a collection of SQL statements that can contain one or more SQL statements), and how to create stored procedures does not belong to the content of this lecture. This lecture mainly illustrates how to call stored procedures in ASP.
The benefits of using stored procedures are great, which are more efficient than running SQL commands in ASP scripts; they can improve overall performance and reduce network load (reduce interaction between network servers and data servers); they can optimize ASP code and enhance code flexibility, and so on.
(I) Use input parameters in stored procedures
The stored procedure used in this example is "byroyalty" that comes with SQL Server 7.0. A SQL statement in it is very simple, it is nothing more than an additional CREATE PROCEDURE byroyalty, and an input parameter is @percentage:
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where = @percentage
serve by
example
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest, rsTest
Set cmdTest = ("")
= "byroyalty" ''Stored procedure name
''Suppose the command type is stored procedure
= adCmdStoredProc
''Create Parameter object
Set prmTest = ("")
The second parameter in the Type property corresponds to
= adInteger ''4 byte signed integer
The third parameter in the Direction property
= adParamInput
The fifth parameter in the Value attribute
= 30
prmTest
Set = Cnn
‘A record set needs to be returned, so use Set rsTest =
Set rsTest =
While Not
rsTest(0) & " "
Wend
Set rsTest = Nothing : Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
The CommandText property can either specify SQL commands, or as stored procedures or table names.
In this example, creating a Parameter object is slightly different. In fact, if you look closely, the meaning is similar. There are two properties in this example that are not used: , and add Type, Direction and Value, corresponding to the five parameters in it.
(II) Use output parameters
When obtaining a record from a database table or calculating a value, a stored procedure that returns the output parameters need to be used. To give an example, first create a new stored procedure OUTemploy in the pubs library of SQL Server. The stored procedure needs to enter two dates and then output a maximum value.
CREATE PROCEDURE OUTemploy
(
@job_lvl tinyint OUTPUT,
@hire_date1 datetime,
@hire_date2 datetime
)
AS
select @job_lvl = MAX(job_lvl) from employee
where hire_date >= @hire_date1 and hire_date = @hire_date2
There are several ways to create stored procedures:
1. Use Microsoft SQL Server's Enterprise Manager, open it in turn in the tree directory on the left: Console Root – Microsoft SQL Servers – SQL Server Group – ICBCZJP(Windows NT) – databases – pubs – stored procedure – New stored procedure. After entering the stored procedure, it can also be syntaxly detected;
2. Using Microsoft SQL Server's Query Analyzer, first connect to the database server and select the pubs database. Enter the stored procedure above and click Execute Query (or press F5);
3. With VB6.0, after opening the menu "View"/"Data View Window", right-click "Data Link"/"New Data Link";
4. Create stored procedures using ASP scripts, example:
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim StrSQL
''Note: & Chr(10) & Chr(13) You can do it at all, mainly for the sake of good looks
StrSQL="CREATE PROCEDURE OUTemploy ( @job_lvl tinyint OUTPUT, " & Chr(10) & Chr(13) &_
"@hire_date1 datetime, @hire_date2 datetime) AS " & Chr(10) & Chr(13) &_
"select @job_lvl = MAX(job_lvl) from employee " &_
"where hire_date >= @hire_date1 and hire_date = @hire_date2"
StrSQL
"Creating stored procedure successfully"
: Set Cnn = Nothing
%>
After the stored procedure is created, in addition to using the menu, you can also delete it using the SQL statement "Drop Procedure OUTemploy".
Example – Send the required input parameters into the stored procedure and obtain the output result.
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest
Set cmdTest = ("")
= Cnn
= "OUTemploy" '''Stored procedure name
= adCmdStoredProc
''Create Parameter object
Set prmTest = ("job_lvl",adTinyInt,adParamOutput)
prmTest
''adTinyInt - 1 byte signed integer
''adDbDate - Date value (yyyymmdd)
Set prmTest = ("hiredate1",adDBDate,adParamInput,,"1993-05-09")
prmTest
Set prmTest = ("hiredate2",adDBDate,adParamInput,,"1994-02-01")
prmTest
‘The following three expressions mean the same
cmdtest("job_lvl") & " "
("job_lvl") & " "
("job_lvl").Value
Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
(III) Use return code parameters
Use the Return statement to return different return codes from stored procedures. For example, the stored procedure first obtains a record set, and then, if there is an employee named Margaret, it will return 1, otherwise it will return 0.
Create Procedure Returnemploy
AS
select emp_id, fname from employee
If Exists(Select fname From employee Where fname=''Margaret'')
Return(1)
Else
Return(0)
example
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim cmdTest, prmTest, rsTest
Set cmdTest = ("")
= Cnn
= "Returnemploy" ''Stored procedure name
= adCmdStoredProc
Set prmTest = ("ReturnValue",adInteger,adParamReturnValue)
prmTest
Set rsTest = ()
While Not
rsTest(0) & " ][ " & rsTest(1) & " "
Wend
: Set rsTest = Nothing
''Before returning cmdtest("ReturnValue") , rsTest must be closed first, otherwise the result will be incorrect
If cmdtest("ReturnValue") = 1 Then
"There is this employee"
Else
"No employee"
End If
Set prmTest = Nothing
Set cmdTest = Nothing: Set Cnn = Nothing
%>
3. How to deal with big data
The "big data" here mainly refers to the Text (large text) and image (image) fields. The data cannot be obtained correctly using the methods described above. You must first use Size = rsTest(0).ActualSize to get the actual length of the field value, and then use rsTest(0).GetChunk(Size) to get the data. In actual use, since these fields are relatively large, in order to save and use server resources reasonably, segmented reading is generally adopted. example:
% @LANGUAGE = VBScript %>
!--#include file=""-->
!--#include file=""-->
% ''
Dim StrSQL, rsTest
‘pr_info is a text field
StrSQL = "Select pr_info,pub_id From pub_info"
Set rsTest = (StrSQL)
Dim BasicSize, BeginSize, LText
Do While Not
rsTest(1) & " "
‘1024 bytes per read
BasicSize = 1024
BeginSize = 0
While BeginSize rsTest(0).ActualSize
LText = rsTest(0).GetChunk(BasicSize)
BeginSize = BeginSize + BasicSize
‘Output segment by segment to client
LText
Wend
""
Loop
Set rsTest = Nothing: Set Cnn = Nothing
%>
In this example, a maximum of 1024 bytes are read each time and read them in multiple times. On the contrary, if you write big data to the database, the method is similar to the above, but instead of using the GetChunk method, you use the AppendChunk method:
rsTest(0).AppendChunk Ltext
Note: Finally, I will introduce a little tip about SQL Server database. If you have encountered this situation: the Chinese data in the database is displayed in garbled code, please don’t panic. You just need to go to my site to download the file with the same name under "C:\Windows\System". The source of the problem is the SQL Server driver, which typically occurs in Windows 98 version 2 (the version number of the SQL Server driver is 3.70.06.23) or Windows 2000 or MDAC2.5 is installed (version number is 3.70.08.20).