程式|資料|資料庫 <!--
蛙蛙推薦:如何做一個高效的ASP資料庫操作程式
一般情況下我們做的ASP資料庫程式都是ADO+ACCESS,並且都是使用一些查詢字串加記錄集來操作資料庫,最多也只使用了connection和recordset兩個對象以及它們的幾個常用的屬性和方法,其實ADO的使用遠不僅這些,我們還有command對象和Parameters對象沒有用呢,而這兩個對象用好了會提高你整個ASP程式的效能.
我這裡寫了一個歌詞管理程式,用的是sqlserver資料庫和預存程序實現的,(這裡沒有用參數化查詢,也正是為了示範ado對sqlserver和預存程序的用法).
希望大家能從我的範例程式碼中學到新的東西,嘿嘿.
注意:我把範例程式碼裡面的asp邊界符(就是角括弧加上一個百分比符號的那個標識)替換成了全形中文的角括弧,因為很多論壇會過濾這個符號,再你複製後記著把它替換成英文半形的.
-->
<!-- 資料庫指令碼 -->
<!-- 先在sqlserver裡建立個資料庫song然後在查詢分析器裡選擇這個資料庫,賦值下面的t-sql代碼執行批查詢,最後把這個頁放在虛擬目錄下,並把其中的資料庫連接字串修改成適合你的資料庫配置的字串,運行本頁就可以了 -->
<!--
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
/*
過程check_song,通過@song_name變數來查詢資料表中是否有重複的記錄，如果有則設定@state這個輸入參數的值為1,該值直接影響到addnew過程的運行
*/
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
/*
過程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添加成功！',16,1,@song_name)
end
else
begin
raiserror ('使用者名稱%s已存在！',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
-->
<!-- /資料庫指令碼 -->
<!-- 資料庫連接 -->
《％
Dim conn,strconn
Set conn = Server.CreateObject("ADODB.Connection")
'如果你的資料庫的連接字串和下面一句不符合,可以修改下句代碼來適合你的資料庫配置
strconn="Driver={sql server};server=192.168.0.110;database=song1;uid=sa;pwd=sa;"
conn.Open strconn
％》
<!-- /資料庫連接 -->
<!-- 擷取本頁地址 -->
《％
Dim fileName,postion
fileName = Request.ServerVariables("script_name")
postion = InstrRev(fileName,"/")+1
fileName = Mid(fileName,postion)
％》
<!-- /擷取
