Frog frog recommendation: how to make an efficient ASP database operation program

<! --
Frog frog recommendation: how to make an efficient ASP database operation program
In general, the ASP database programs we use ADO + Access, and some query strings and record sets are used to operate databases, at most, only two objects, connection and recordset, and several common attributes and methods are used. In fact, the use of ADO is far from that. We also have command objects and parameters objects that are useless, these two objects will improve the performance of your entire ASP program.
I wrote a lyrics Management Program, which is implemented by the SQL Server database and stored procedure. (parameterized query is not used here. It is precisely to demonstrate the usage of ADO for SQL Server and stored procedure ).
Hope you can learn new things from my sample code.
Note: I replaced the ASP boundary in the sample code (that is, the ID with a percentage sign in the angle brackets) with the angle brackets in the full-angle Chinese text, because many forums will filter this symbol, after you copy it, remember to replace it with a half-width English.
-->
<! -- Database script -->
<! -- First create a database Song In sqlserver, then select this database in the query analyzer, assign the following T-SQL code to execute a batch query, and finally put this page under the virtual directory, modify the database connection string to the string suitable for your database configuration. Run this page. -->
<! --
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [check_song] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [check_song]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [insert_song] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [insert_song]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_song_list] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_song_list]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_wawa_song] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_wawa_song]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [wawa_song] ') and objectproperty (ID, n' isusertable') = 1)
Drop table [DBO]. [wawa_song]
Go

Create Table [DBO]. [wawa_song] (
[Song_id] [int] identity (1, 1) not null,
[Song_name] [char] (40) Collate chinese_prc_ci_as not null,
[Song_content] [varchar] (4000) Collate chinese_prc_ci_as not null,
[Song_author] [char] (20) Collate chinese_prc_ci_as null,
[Author_id] [int] Null
) On [primary]
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go

/*
In the process of check_song, the @ song_name variable is used to query whether repeated records exist in the data table. If yes, the value of the input parameter @ State is set to 1, which directly affects the operation of the addnew process.
*/
Create proc check_song
@ Song_name char (40 ),
@ State int output
As
Begin
If exists (select song_name from wawa_song
Where song_name = @ song_name)
Set @ State = 1
Else
Set @ State = 0
End

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go

/*
Process insert_song
*/
Create proc insert_song
@ Song_name char (40 ),
@ Song_content varchar (4000 ),
@ Song_author char (20)
As
Begin
Declare @ State int
Exec check_song @ song_name, @ state output
If @ State = 0
Begin
Begin tran
Insert into wawa_song (song_name, song_content, song_author) values (@ song_name, @ song_content, @ song_author)
Commit tran
Raiserror ('% s added successfully! ', 16,1, @ song_name)
End
Else
Begin
Raiserror ('user name % s already exists! ', 16,1, @ song_name)
Return
End
End

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Set quoted_identifier off
Go
Set ansi_nulls off
Go

Create procedure [p_song]
Select * From wawa_song order by song_id DESC
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Set quoted_identifier off
Go
Set ansi_nulls off
Go

Create proc p_wawa_song
@ ID int
As
Select song_id, song_name, song_author, song_content from wawa_song where song_id = @ ID
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

-->
<! --/Database script -->
<! -- Database connection -->
%
Dim Conn, strconn
Set conn = server. Createobject ("ADODB. Connection ")
'If your database connection string does not match the following sentence, you can modify the following code to suit your database configuration.
Strconn = "driver = {SQL Server}; server = 192.168.0.110; database = song1; uid = sa; Pwd = sa ;"
Conn. Open strconn
%
<! --/Database connection -->
<! -- Get the address of this page -->
%
Dim filename, postion
Filename = request. servervariables ("script_name ")
Postion = Limit Rev (filename, "/") + 1
Filename = mid (filename, postion)
%
<! --/Get the address of this page -->
<! -- Let the database data output functions in the original format -->
%
Function wawahtml (result)
If not isnull (result) then
Result = server. htmlencode (result)
Result = Replace (result, vbcrlf, "<br> ")
Result = Replace (result, "", "& nbsp ;")
Result = Replace (result, CHR (9), "& nbsp;") 'tab key
Wawahtml = Result
Else
Wawanhtml = "NO content"
End if
End Function
%
<! --/Function for outputting database data in the original format -->
<! -- Read and display all the songs in the database -->
%
Dim rs_wawa
Set rs_wawa = server. Createobject ("ADODB. recordset ")
Rs_wawa.open "p_song", Conn, 1, 1, 4
Dim pages, allpages, page
Pages = 10
Rs_wawa.pagesize = pages
Allpages = rs_wawa.pagecount
Page = clng (Request ("page "))
If isempty (PAGE) or page <0 or page = 0 Then page = 1
If page> rs_wawa.pagecount then page = rs_wawa.pagecount
If not (rs_wawa.bof and rs_wawa.eof) then
Rs_wawa.absolutepage = page
End if
%
<! --/Read and display all the songs in the database -->
<! -- Read a record of a song from the database based on parameters -->
%
If request ("action") = "View" then
If request ("ID") <> "then
Dim ID
Id = clng (TRIM (request. querystring ("ID ")))
Set CM = server. Createobject ("ADODB. Command ")
Set cm. activeconnection = Conn
Cm. commandtext = "p_wawa_song"
Cm. commandtype = 4
Set P = cm. Parameters
P. append cm. createparameter ("@ ID", 3,1, ID)
Dim rs_song
Set rs_song = server. Createobject ("ADODB. recordset ")
Rs_song.open cm, 1, 1
Else
Response. Write ("No parameter passed ")
Response. End ()
End if
End if
%
<! --/Read a record of a song from the database based on parameters -->
<! -- Add form data to the database -->
%
If not isempty (request. Form ("Submit") then
Call addnew
End if
Sub addnew
On Error resume next
Song_author = request ("song_author ")
Song_content = request ("song_content ")
Song_name = request ("song_name ")
Set CM = server. Createobject ("ADODB. Command ")
Set cm. activeconnection = Conn
Cm. commandtext = "insert_song"
Cm. commandtype = 4
Set P = cm. Parameters
P. append cm. createparameter ("@ song_name", 130,1, 60, song_name)
P. append cm. createparameter ("@ song_content", 202,1, 4000, song_content)
P. append cm. createparameter ("@ song_author", 130,1, 20, song_author)
Cm. Execute
If err. Number <> 0 then
Response. Write err. Description
Else
Response. Write "DDD"
Response. End
End if

Set CM = nothing
Response. Redirect filename
End sub
%
<! --/Add form data to the database -->
<Table width = "90%" border = "0" align = "center">
<Tr>
<TD valign = "TOP">
<! -- Add a song user interface -->
% If request ("action") = "add" then %
<Table width = "500" border = "0" align = "center" cellspacing = "1" bgcolor = "# 0000ff">
<Form action = "" method = "Post">
<Tr bgcolor = "# ffffff">
<TD colspan = "2"> <Div align = "center"> Add lyrics </div> </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD width = "50%" align = "right"> song Author: </TD>
<TD width = "50%"> <input name = "song_author" type = "text" id = "song_author"> </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD width = "50%" align = "right"> song name: </TD>
<TD width = "50%"> <input name = "song_name" type = "text" id = "song_name"> </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD align = "right"> song content: </TD>
<TD> <textarea name = "song_content" Cols = "50" rows = "5" id = "song_content"> </textarea> </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD align = "right"> <input type = "Submit" name = "Submit" value = "Submit"> </TD>
<TD> <input type = "reset" name = "submit2" value = "reset"> </TD>
</Tr>
</Form>
</Table>
% End if %
<! --/Add song user interface -->

<! -- Display song user interface -->
% If request ("action") = "View" then %
<Table width = "500" border = "0" align = "center" cellspacing = "1" bgcolor = "# 0000ff">
<Tr bgcolor = "# ffffff">
<TD colspan = "2"> <Div align = "center"> View lyrics </div> </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD width = "50%" align = "right"> song Author: </TD>
<TD width = "50%"> % = rs_song ("song_author") % </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD width = "50%" align = "right"> song name: </TD>
<TD width = "50%"> % = rs_song ("song_name") % </TD>
</Tr>
<Tr align = "Left" bgcolor = "# ffffff">
<TD colspan = "2"> %= wawahtml (CSTR (rs_song ("song_content") % </TD>
</Tr>
</Table>
% End if %
<! --/Display song user interface -->
<! --/Welcome interface -->
% If request ("action") = "hello" or request ("action") = "" Then %"
<Table width = "500" border = "0" align = "center" cellspacing = "1" bgcolor = "# 0000ff">
<Tr bgcolor = "# ffffff">
<TD colspan = "2"> <Div align = "center"> welcome to the frog lyrics Management System </div> </TD>
</Tr>
<Tr bgcolor = "# ffffff">
<TD colspan = "2" align = "Left"> <p> residual load listening rain, pear blossom snow, <br>
The season is full of fun. <Br>
Who will get Fenglin drunk? Tears are everywhere. <br>
Live is happy! <Br>
Confidence is beautiful! <Br>
Love is happiness. <Br>
<Br>
<A href = "http://blog.csdn.net/onlytiancai/" target = "_ blank"> </a> </P>
</TD>
</Tr>
</Table>
% End if %
<! --/Welcome interface -->

</TD>
<TD valign = "TOP">
<Center>
<A href = "% = filename %"> homepage </a> & nbsp; <a href = "%= filename %"? Action = Add "> Add a song </a>
<Table width = "300" border = "0" align = "center" cellspacing = "1" bgcolor = "# 0000ff">
%
If not (rs_wawa.bof and rs_wawa.eof) then
While not rs_wawa.eof and pages> 0
%
<Tr bgcolor = "# ffffff">
<TD> <a href = "'% = filename %'? Action = view & id = "% = rs_wawa (0) %"> "% = rs_wawa (1) %" </a> </TD>
</Tr>
%
Rs_wawa.movenext
Pages = pages-1
Wend
Rs_wawa.close: Set rs_wawa = nothing
Else
%
<Tr bgcolor = "# ffffff">
<TD> no songs have been added </TD>
</Tr>
</Table>
%
End if
Conn. Close: Set conn = nothing
%
% If page <> 1 then %
<A href = "'% = filename %'? Page = 1 "> homepage <A/> & nbsp; <a href =" % = filename % "? Action = Hello &? Page = % = (page-1) % "> previous page </a> & nbsp;
% End if %
% If page <> allpages then %
<A href = "'% = filename %'? Page = % = (page + 1) % "> next page </a> & nbsp; <a href =" %= filename % "? Page = % = (allpages) % "> last page </a> & nbsp;
% End if %
Current page % = Page % & nbsp; total Page % = allpages %
</Center>
</TD>
</Tr>
</Table>