18-2 網????庫???基???

bASP{]p̡AΨӦsƮwΪƪκ ADO]ActiveX Data Objects^AoO@ ASP تƮwsAiHg JavaScript/JScriptBVBScript yӱƮwsAåishظƮwA]A SQL ServerBOracleBAccess 䴩ODBCƮwCADO Dn]t ConnectionBRecordset Command TتAp`NлP Connection Ʈwާ@C

ϥ ADO Connection ӶiƮw˵dߡADnHU|ӨBJG

  1. إ߸ƮwsAM}ҸƮwG
    ϥΡuServer.CreateObjectvwq@ ADO Connection AMϥΨuOpenvkӶ}ҸƮwӷAdҵ{XpUG conn = Server.CreateObject("ADODB.Connection"); ۧڭ̥iH]w conn ConnectionString ʽӫwƮwA@|ا@kG
    1. w Access ƮwbwЪ|G conn.ConnectionString = "DBQ=Ʈwɮ;Driver={Microsoft Access Driver (*.mdb)};Driverld=25;FIL=MS Access;UID=**;PWD=**";
    2. w DSN]ƨӷW١^G conn.ConnectionString = "ƨӷW";
    3. s SQL Server ƮwG conn.ConnectionString = "Driver={SQL Server};Datebase=ƮwW;Server=};UID=**;PWD=**";
    4. s UNIX MySQL ƮwG conn.ConnectionString = "Driver={MySQL};Datebase=ƮwW;Server=};UID=**;PWD=**";
    ϥΥHW@kYisAQnsλݸƮwC̫A conn Open kAӶ}ҸƮwG conn.Open();

  2. SQLOAñNdߵGxs Recordset GYO˵dߡAڭ̥iNGs RecordSet ܼơursvAHKΡA嫬{XpUG sql = "Select * from testTable"; rs = conn.Execute(sql); HW{XN SQL OҬdߨ쪺Gxs Recordset rs CYO˵dߡAhݭnNGsܼ rsC
  3. oW٤ΤeGYO˵dߡAڭ̥iHϥΤUC覡ӨoW٤ΤeTG
    • rs.EOFGO_w̫@ơAOTrueAϤFalse
    • rs.Fields.CountGRecordSets
    • rs(i).NameGi쪺W
    • rs("W")GŪYӯSwW٪
    • rs(i)Gi쪺
    • rs.MoveNextGNвU@
    • rs.MovePrevGNвW@
    • rs.MoveFirstGNвĤ@
    • rs.MoveLastGNв̫@
    ҦpAYnLXW١AiHϥΤUC嫬{XG for (i=0; i<rs.Fields.Count; i++) Response.write(rs(i).Name+"<br>"); YnLXC@ƪC@ȡAiHϥΤUC嫬{XG while (!rs.EOF){ for (i=0; i<rs.Fields.Count; i++) Response.write(rs(i)+" "); Response.write("<br>\n"); rs.MoveNext(); } HW{X rs(i) ŪƮw쪺ơArs.MoveNext() N Recordset ƫвU@Ag rs.EOF ӧP_O_wF̥ơAðtX while jYioҦdߵGơC

  4. RecordSet θƮwsGdҵ{XpUG rs.Close(); conn.Close();
ҦpAڭ̥iHbLX Access Ʈw asp/example/database/test.mdb ̭ƪ testTable eG

Example]database/listdb01.asp^G

WzdҪlɦpUG

lɡ]database/listdb01.asp^G]ǦϰUYi^
<%@ language="jscript" %>
<% title="H JScript iƮwCGϥθƮw|" %>
<!--#include file="../head.inc"-->
<hr>

<%
//====== Step 1Gإ߸ƮwsAM}ҸƮw
conn = Server.CreateObject("ADODB.Connection");
conn.ConnectionString = "DBQ=" + Server.MapPath("test.mdb") + ";Driver={Microsoft Access Driver (*.mdb)};Driverld=25;FIL=MS Access;";
conn.Open();

//====== Step 2GSQLOAñNdߵGxs Recordset 
sql = "SELECT * FROM testTable";	//qƪ testTable XҦ
rs = conn.Execute(sql);
%>

<table border=1 align=center>
<tr bgcolor="cyan">
<%
//====== Step 3GzL RecordSet Xo쪺e
//LXW
for (i=0; i<rs.Fields.Count; i++)
	Response.write("<th>"+rs(i).Name+"</th>\n");
%>
</tr>
<%
//LXC@
while (!rs.EOF) {
	Response.write("<tr>\n");
	for (i=0; i<rs.Fields.Count; i++)
		Response.write("<td>"+rs(i)+"&nbsp;</td>\n");
	rs.MoveNext();
}
%>
</table>

<%
//====== Step 4G RecordSet θƮws
rs.Close();
conn.Close();
%>

<hr>
<!--#include file="../foot.inc"-->

bWzdҤAڭ̨ϥΡuwƮwbwЪ|v覡ӳsƮwA䤤 SQL OuSELECT * FROM testTablevNuqƪ testTable XҦơvC䥦ҥHѪ覡gb{XAҥHbAحzCYŪ̹ VBScript xA]iHϥ VBScript Ӷiu@Ay{@ˡAiѦҦdҡGasp/example/database/listdb01_vbs.aspC

Hint
bWzdҤ̫@ơA䤤 NickName M Percentage 쳣JơAO NickName 쪺õLw]ȡA]ѸƮw^Ӫܬ nullFtAPercentage 쪺w]ȬO 0AҥHSLX 0Co쪺ݩʤΨw]ȳi Access ƪu]p˵vӳ]wC

Ynϥ DSN sƮwAڭ̥bA]w DSN]ԲӬy{ШW@p`^AMNiHb ASP g DSN ӫwƮw]iHOݩάOݡ^CHW@ӽdҦӨAYn DSN ӳsƮwAӤwƮwAunU@CԭzG

Conn.ConnectionString = "DBQ=" + Server.MapPath("test.mdb") + ";Driver={Microsoft Access Driver (*.mdb)};Driverld=25;FIL=MS Access;"; 令U@CYiG Conn.ConnectionString = "dsn4test"; 䤤 dsn4test wQ]wV test.mdb ODBC ƨӷC JScript dҥi asp/example/database/listdb02.aspAVBScript dҫhi asp/example/database/listdb02.aspC

Ʈw˵MCLAO`QΨ쪺\A]ڭ̱N\g@Ө listQueryResult()Aæs listQueryResult.inc A䤺epUG

lɡ]listQueryResult.inc^G]ǦϰUYi^
<!-- List a table in a given database -->
<!-- "database" is the full path to a database -->
<!-- "table" is the table to be listed -->

<script runat=server language=jscript>
function listQueryResult(database, sql){
var Conn = Server.CreateObject("ADODB.Connection");
Conn.ConnectionString = "DBQ=" + Server.MapPath(database) + ";Driver={Microsoft Access Driver (*.mdb)};Driverld=25;FIL=MS Access;";
Conn.Open();
var RS = Conn.Execute(sql);

Response.write("<table border=1 align=center>");
Response.write("<tr align=center bgcolor=cyan>");

for (var i=0; i<RS.Fields.Count; i++)
	Response.write("<th>"+RS(i).Name+"</th>\n");
Response.write("</tr>")

color=["#ffffdd", "#ffeeee", "#eeffee", "#e0e0f9", "#eeeeff"];	// Cx}
k=0;
while (!RS.EOF) {
	Response.write("<tr bgcolor=" + color[k] + ">");
	for (i=0; i<RS.Fields.Count; i++)
		Response.write("<td>" + RS(i) + "&nbsp;</td>");
	Response.write("</tr>");
	k=k+1;
	if (k==color.length)
		k=0;
	RS.MoveNext();
}
RS.Close();
Conn.Close();
Response.write("</table>");
}
</script>


<script runat=server language=vbscript>
Function listQueryResult(database, sql)
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DBQ=" & Server.MapPath(database) & ";Driver={Microsoft Access Driver (*.mdb)};Driverld=25;FIL=MS Access;"
Set RS = Conn.Execute(sql)

Response.Write("<table border=1 align=center>")
Response.Write("<tr align=center bgcolor=cyan>")
dim i
For i=0 to RS.Fields.Count-1
	Response.Write("<th>" & RS(i).Name & "</th>")
next
Response.Write("</tr>")

color=Array("#ffffdd", "#ffeeee", "#eeffee", "#e0e0f9", "#eeeeff")	' Cx}
k=0
Do While NOT RS.EOF
	Response.Write("<tr bgcolor=" & color(k) & ">")
	For i=0 to RS.Fields.Count-1
		Response.Write("<td>" & RS(i) & "&nbsp;</td>")
	next
	Response.Write("</tr>")
	k=k+1
	If k=ubound(color)+1 Then
		k=0
	End If
	RS.MoveNext
Loop
RS.Close
Conn.Close
Response.Write("</table>")
End Function
</script>

bWzlXAڭ̤OgFAΩ JScript M VBScript ơA]L׬Oϥ JScript VBScript ASP AiHϥΦ]tɨӦCXƮwdߪGC ϥΤWzƨӶiƮwCA{XN|bbAdҦpUG

Example]database/listdb03.asp^G

lXpUG

lɡ]database/listdb03.asp^G]ǦϰUYi^
<%@ language="jscript" %>
<% title="H JScript iƮwCGϥ listQueryResult() " %>
<!--#include file="../head.inc"-->
<hr>

<!--#include file="../listQueryResult.inc"-->
<%
database="test.mdb";
sql="select * from testTable";
listQueryResult(database, sql);
%>

<hr>
<!--#include file="../foot.inc"-->

Yϥ VBScriptAhidҡGasp/example/database/listdb03_vbs.asp

ezdһFpiƮw˵dߡAñNGܩ ASP C@ӨASQL Owg㦳Ʈwi˵BsWBקBR\]p`Բӻ^A]unϥξA SQL OAA[WezkAYiƮwi槹BzC


JScript {]pPΡGΩAݪ ASP