在上一篇教學中,讀者發現直接修改區域變數 NumVisits 無法改變 Application("NumVisits") 的值。正確做法是直接對 Application 物件賦值:
<%
If Application("NumVisits") < 999 Then
Application("NumVisits") = 999 ' 直接設定初始值
End If
Application.Lock
Application("NumVisits") = Application("NumVisits") + 1
Application.Unlock
%>
SELECT - 查詢INSERT, UPDATE, DELETE - 資料增刪改CREATE, DROP, ALTER - 結構定義GRANT, REVOKE - 權限控制本文專注於 DML 三大核心指令。
INSERT INTO table_name (col1, col2, col3)
VALUES (value1, value2, value3);
範例: 新增銷售員
INSERT INTO Salesreps (Name, EmpNum, Sales, HireDate, Salary)
VALUES ('書生', 9, 10000, '1999-02-23', 2000);
INSERT INTO OldOrders (OrderNum, OrderDate, Amount)
SELECT OrderNum, OrderDate, Amount
FROM Orders
WHERE OrderDate < '1998-12-30';
UPDATE table_name
SET col1 = val1, col2 = val2, ...
WHERE condition;
⚠ 必須加 WHERE 條件,否則全表所有列都會被更新!
UPDATE Customers
SET CreditLimit = 100000, SalesRepID = 99
WHERE CustName = 'ASP教學網';
UPDATE Customers
SET CreditLimit = 200000, AreaCode = '021'
WHERE CustID IN (80, 90, 100, 120);
DELETE FROM table_name
WHERE condition;
⚠ 僅省略 WHERE 會清空整張表! 生產環境建議先用 SELECT 驗證條件。
DELETE FROM Orders WHERE OrderID = 99;
<!--#include file="conn.asp"-->
<%
Dim cmd, sql
sql = "UPDATE Products SET Price=?, Stock=? WHERE ProdID=?"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.CommandType = 1 ' adCmdText
' 依序對應 ? 參數
cmd.Parameters.Append cmd.CreateParameter("@price", 5, 1, , 299) ' adDouble
cmd.Parameters.Append cmd.CreateParameter("@stock", 3, 1, , 50) ' adInteger
cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , 101) ' adInteger
cmd.Execute
Response.Write "更新完成,影響 " & cmd.RecordsAffected & " 筆"
Set cmd = Nothing
%>
優點: 防 SQL 注入、型別安全、效能較好 (命令可重複使用)。
CREATE TABLE Bills (
BillID INT PRIMARY KEY,
CustName VARCHAR(50) NOT NULL,
Amount DECIMAL(10,2),
BillDate DATETIME DEFAULT GETDATE()
);
DROP TABLE Bills;
DELETE ... WHERE Date < ...