企業內部網 (Intranet) 整合資料庫與 Web 技術,可將分散各地分支機構的業務數據自動匯總、統計。本文以真實案例示範:ASP + ADO 連接 UNIX 上的 Informix 資料庫,實現分支機構「業績數據輸入」與「總部查詢匯總」雙向應用。
cxdb,表 test(br char(6), dt date, sco money)SetNet32 設定伺服器資訊 (IP/主機名、服務名/埠號、协议)cxdb,指向 Informix 驅動、資料庫 cxdbILogin32 Demo 測試連線成功<%
Dim conn, connStr
connStr = "Driver={INFORMIX-CLI 2.5 (32BIT)};Database=cxdb;Server=11.32.65.45;UID=informix;PWD=*****;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
' ... 操作 ...
conn.Close
Set conn = Nothing
%>
<html><head><title>分支機構業績輸入</title></head><body>
<h2>業績數據輸入</h2>
<form method="POST" action="todb.asp">
<p>單位代碼:<input name="T1" size="10" maxlength="6"></p>
<p>日期 (YYYY-MM-DD):<input name="T2" size="12"></p>
<p>業績金額:<input name="T3" size="15"></p>
<p><input type="submit" value="提交"> <input type="reset" value="重填"></p>
</form>
</body></html>
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim conn, connStr, sql, br, dt, sco
br = Request.Form("T1")
dt = Request.Form("T2")
sco = Request.Form("T3")
' 簡易驗證
If br="" Or dt="" Or sco="" Then
Response.Write ""
Response.End
End If
connStr = "Driver={INFORMIX-CLI 2.5 (32BIT)};Database=cxdb;Server=11.32.65.45;UID=informix;PWD=*****;"
Set conn = Server.CreateObject("ADODB.Connection")
On Error Resume Next
conn.Open connStr
If Err.Number <> 0 Then
Response.Write "資料庫連線失敗:" & Err.Description
Response.End
End If
' 參數化查詢 (防注入) - ADO Command 物件
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO test (br, dt, sco) VALUES (?, ?, ?)"
cmd.Parameters.Append cmd.CreateParameter("@br", 200, 1, 6, br) ' adVarChar
cmd.Parameters.Append cmd.CreateParameter("@dt", 135, 1, 10, dt) ' adDBTimeStamp
cmd.Parameters.Append cmd.CreateParameter("@sco", 131, 1, 18, sco) ' adNumeric
cmd.Execute
If Err.Number <> 0 Then
Response.Write "寫入失敗:" & Err.Description
Else
Response.Write "資料已成功儲存!
繼續輸入"
End If
cmd.Parameters.Clear
Set cmd = Nothing
conn.Close
Set conn = Nothing
%>
<%@ Language=VBScript %>
<% Option Explicit %>
<html><head><title>業績查詢匯總</title></head><body>
<h2>業績查詢</h2>
<form method="GET">
起始日期:<input name="dt1" size="12">
結束日期:<input name="dt2" size="12">
單位代碼:<input name="br" size="10"> (留空查全部)
<input type="submit" value="查詢">
</form>
<%
Dim conn, connStr, sql, rs, br, dt1, dt2, whereClause
If Request.QueryString("dt1") <> "" Then
dt1 = Request.QueryString("dt1")
dt2 = Request.QueryString("dt2")
br = Request.QueryString("br")
whereClause = "WHERE dt BETWEEN '" & dt1 & "' AND '" & dt2 & "'"
If br <> "" Then whereClause = whereClause & " AND br = '" & br & "'"
sql = "SELECT br, dt, SUM(sco) AS total_sco FROM test " & whereClause & " GROUP BY br, dt ORDER BY dt DESC, br"
connStr = "Driver={INFORMIX-CLI 2.5 (32BIT)};Database=cxdb;Server=11.32.65.45;UID=informix;PWD=*****;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
Set rs = conn.Execute(sql)
If rs.EOF Then
Response.Write "無符合資料
"
Else
%>
單位 日期 業績合計
<% Do While Not rs.EOF %>
<%= rs("br") %>
<%= rs("dt") %>
<%= FormatNumber(rs("total_sco"), 2) %>
<% rs.MoveNext : Loop %>
<%
End If
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
End If
%>
</body></html>
? 佔位符On Error Resume Next + Err.Number 捕捉連線/執行錯誤Close → Set Nothing 三件套 (Recordset, Command, Connection)'YYYY-MM-DD',跨資料庫最相容Session 登入驗證,區分分支機構只能輸入/查詢自己的資料Response.ContentType="application/vnd.ms-excel")ASP + ADO + Informix ODBC 打通了「異構平台 (UNIX/Windows) + 異構資料庫 + Web 前端」的整合路徑。核心在於:正確安裝配置 32-bit ODBC 驅動、使用 DSN-less 連線字串、堅持參數化查詢、嚴謹資源管理。此架構在 2000 年代初廣泛支撐企業級 Intranet 應用,至今仍有存續系統在運行,理解其原理有助於維護與現代化遷移。