取得新加入的 Identity 欄位值 (自動編號欄位值)的方法
--------------------------------------------------------------------------------
【uestc95】 于 00-3-14 上午 09:14:03 加贴在 Joy ASP ↑：
如何取得新加入的 Identity 欄位值 (自動編號欄位值)
玩過 SQL Server 的人應該都對 Identity 欄位蠻熟悉的,這個欄位相當於 Access 中的自動編號欄位,通常會使用到 Identity 欄位的原因是因為在你新規劃的資料表格中,缺乏一個足以作為主鍵值(Primary Key) 的欄位,但是你又希望在往後的表格查詢時能有一個主鍵值欄位當作索引欄以加快查詢的速度,通常我們的做法是另外加上一個 Identy 欄位,在 Access 中,表格設計精靈會自動詢問你是否要加上主索引欄位。在一個流量小的站台,要取得最新加入資料庫的那筆資料之 Identity 欄位值並不困難 (我已經在 FAQ 區中討論過了),但是在一個高流量的站台要做到同樣的事並不容易,底下我將帶領大家完成這個工作,也讓大家更了解 Identity 欄位的特性,首先先讓我們看看在 SQL Server 中表格設計的雛形,其中在識別欄位屬性打勾的欄位就是 Identity 欄位,如下所示:
在進行底下的工作之前,希望你能先了解 Identity 欄位的某些特性
· Identity 欄位的資料型態必須是 int, smallint, tinyint, decimal 或者 numeric 。
· 不允許空值。
· 不可以設定預設值。
· 識別值增量可以是任何整數值 (1, -1, 5, ....等等),但不可以是小數或是 0
· 預設的識別值種子是 1,但是如果你沒有指定任何識別值種子的話,則識別值種子將變成 0。
能在新增資料到資料庫之後隨即取得 Identity 欄位的值是很方便的,你或許不曾想過我為什麼要取得這樣的值呢?坦白說,一般人使用的機會並不多,但是如果你想做某些特殊功能的時候,事先取得 Identity 欄位的值就變得很重要了,舉個例子來說,在一個樹狀結構的留言版或討論區中,想要呈現階層的關係必須使用遞回的方式來重複索引表格,有的時候會拖垮伺服器 (流量大的時候),為了要很快將資料從資料庫中讀出又不採用遞回的方式,這時候你可以用一個欄位儲存 Identity 欄位的值,就可以達成相同的效果,該怎麼做你們可以想想看。
現在就讓我們看看該如何做吧!!下面包含了三種方式,第三種方式是我比較建議大家使用的,其他兩種方式雖然也可以達成同樣的目的,但是第三種方式允許你可以一次插入多筆資料並取得個別的 Identity 欄位值,在擴充性來講我覺得比較好一點。
1.適用於所有 ADO 版本
Set loConn = CreateObject("ADODB.Connection")' 建立資料庫連結loConn.Open "Provider=SQLOLEDB; Data Source=JACKIE; Initial Catalog=pubs; User ID=sa; Password= "'新增一筆資料到資料庫內
lsSQL = "INSERT INTO tMembers (MemberName) VALUES ('Manohar')"' 執行 SQL 敘述
loConn.Execute(lsSQL)' 透過 @@IDENTITY 函數取得最後一筆資料的 Identity 欄位值,並傳給 IIID 變數
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = loConn.Execute(lsSQL)llID = loRs.Fields("NewID").value' 關閉資料庫連結
loConn.Close()Set loConn = Nothing
%>
你可以看到我們將新增資料到資料庫與取得 Identity 欄位值分開執行,這是因為在比較舊的 ADO 版本中並不支援在一個 SQL 敘述中執行多筆工作,但是只要在同一個資料連結時段中所執行的 SQL 敘述都是被認可的。
2.適用於 ADO 2.0 以後的版本
Set loConn = CreateObject("ADODB.Connection")' 建立資料庫連結loConn.Open "Provider=SQLOLEDB; Data Source=JACKIE; Initial Catalog=pubs; User ID=sa; Password= "''新增一筆資料到資料庫內,並隨即取得 Identity 欄位值
lsSQL = "INSERT INTO tMembers (MemberName) VALUES ('Manohar');" &_ "SELECT @@IDENTITY AS NewID;"' 執行 SQL 敘述
Set loRs = loConn.Execute(lsSQL)' 利用資料集合物件中的 NextRecordset() 方法將 SQL 敘述中的第二個敘述執行結果傳給loRs ' 變數
Set loRs = loRs.NextRecordSet()' 將最後一筆資料的 Identity 欄位值傳給 IIID 變數llID = loRs.Fields("NewID").value' 關閉資料庫連結
loConn.Close()Set loConn = Nothing
%>
由於只有 ADO 2.0 以後的版本才支援 NextRecordset() 方法,所以這種方法不是非常適用,這裡我只是要向各位介紹有這種方法可以使用,如果你的 ADO 是屬於 2.0以後的版本,用這種方式取得 Identity 欄位值其實並無不可。
3.適用於所有 ADO 版本
Dim loConn, lsSQL, loRs
Set loConn = CreateObject("ADODB.Connection")' 建立資料庫連結
loConn.Open("DSN=myDSN;UID=something;PWD=Something;")' 新增一筆資料到資料庫並取得取得最後一筆資料的 Identity 欄位值
lsSQL = "SET NOCOUNT ON;" &_ "INSERT INTO tMembers (MemberName) VALUES ('Manohar');" &_ "SELECT @@IDENTITY AS NewID;"' 執行 SQL 敘述
Set loRs = loConn.Execute(lsSQL)' 將 Identity 欄位值傳給 IIID 變數
llID = loRs.Fields("NewID").value' 關閉資料庫連結
loConn.Close()
Set loConn = Nothing
%>
第二種方法跟第三種方法很像,只是在 SQL 敘述中多定義了一行 SET NOCOUNT ON,什麼叫做 SET NOCOUNT ON 呢?在 SQL 敘述中不會傳回資料集合物件的敘述 (例如:Insert, Delete, Update......等等)都會被 SET NOCOUNT ON 略過不計,只有那些會傳回資料集合的敘述才會被計算,在第三個範例中只有 "SELECT @@IDENTITY AS NewID; 這一段敘述才會被視做資料集合並被計算
