Program | data | Database <!--
Frog Frog recommended: How to do an efficient ASP database operating procedures
In general, we do ASP database programs are ado+access, and are using some query string plus recordset to manipulate the database, up to only the use of connection and recordset two objects and their several commonly used properties and methods, In fact, the use of ADO is far more than that, we have command objects and parameters objects are not used, and these two objects will improve your entire ASP program performance.
I've written a lyrics management program that uses SQL Server databases and stored procedures, (no parameterized queries, just to demonstrate ADO's use of SQL Server and stored procedures).
I hope you can learn something new from my sample code, hehe.
Note: I replaced the ASP boundary character in the sample code (that is, the one with the bracket with a percent sign) to the angle bracket in full-width Chinese, because many forums will filter the symbol, and then you copy the PostScript to replace it with the English half corner.
-->
<!--database Script-->
<!--Create a new database in SQL Server song and then select the database in Query Analyzer, assign the following T-SQL code to execute a batch query, and finally place the page in a virtual directory, and modify the database connection string into a string that fits your database configuration. You can 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] (COLLATE) chinese_prc_ci_as not NULL,
[Song_content] [varchar] (4000) COLLATE chinese_prc_ci_as not NULL,
[Song_author] [Char] (m) COLLATE chinese_prc_ci_as NULL,
[AUTHOR_ID] [INT] Null
) on [PRIMARY]
Go
SET QUOTED_IDENTIFIER ON
Go
SET ANSI_NULLS on
Go
/*
Process Check_song, through the @song_name variable to query whether there are duplicate records in the datasheet, if there is set @state the value of the input parameter is 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] As
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 conform to the following sentence, you can modify the following code to fit your database configuration
strconn= "Driver={sql Server};server=192.168.0.110;database=song1;uid=sa;pwd=sa;"
Conn. Open strconn
％》
<!--/Database connection-->
<!--get this page address-->
《％
Dim filename,postion
FileName = Request.ServerVariables ("Script_name")
postion = InStrRev (FileName, "/") +1
FileName = Mid (filename,postion)
％》
<!--/Get
