在ASP中,可以通过ADO访问数据库,经常会用到ASP的三个内建对象(ADO):Connection、Recordset、Cmmand对象;以下的示例简单的说明了如何使用三个ADO对数据库进行操作,简单起见,采用了Microsoft Access数据库的文件DSN;使用的是VBScript。

<%@ Language=VBScript %>
<html>
<head>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<title>操作数据库的几种方法</title>
</head>

<body>
Please Input Personal Information:<br>
<form method=post action="dbmeth.asp">
FirstName:<input type=text name="firstname">
<br>LastName:<input type=text name="lastname">
<br>Sex:<input type=text name="sex">
<br>Age:<input type=text name="age">
<br><input type=submit value="connection" NAME="use_db">
<input type=submit value="recordset" NAME="use_db">
<input type=submit value="command" NAME="use_db">
</form>

<%
strFName = Request.Form("firstname")
strLName = Request.Form("lastname")
strSex = Request.Form("sex")
intAge = Request.Form("age")
strCommand = Request.Form("use_db")
Response.Write strFName
Response.Write strLName
Response.Write strSex
Response.Write intAge
Response.Write strCommand

IF  strCommand = "connection" Then
‘使用Connection对象进行操作
 strDSN = "FILEDSN=test.dsn"
 set cn = Server.CreateObject("ADODB.Connection")
 cn.Open strDSN

 strSQL = "INSERT INTO info(firstname,lastname,sex,age) VALUES(‘"&strFName&"’,'"&strLName&"’,'"&strSex&"’,"&intAge&")"
 cn.Execute(strSQL)
 

 strSQL = "SELECT * FROM info"
 set rsInfo = cn.Execute(strSQL) ‘返回Recordset对象
 

 set objno = rsInfo("No")
 set objfname = rsInfo("firstname")
 set objlname = rsInfo("lastname")
 set objsex = rsInfo("sex")
 set objage = rsInfo("age")

 Response.Write "<table cellspacing=0 cellpadding=0 border=1><tr><td>No</td><td>firstname</td><td>lastname</td><td>sex</td><td>age</td></tr>"

 Do Until rsInfo.EOF
 Response.Write "<tr><td>"&objno&"</td><td>"&objfname&"</td><td>"&objlname&"</td><td>"&objsex&"</td><td>"&objage&"</td></tr>"
 rsInfo.MoveNext
 Loop

 Response.Write "</table>"

 cn.Close
 set rsInfo = Nothing
 set cn = Nothing
ElseIf strCommand = "recordset" Then
‘使用Recordset对象进行操作
 strDSN = "FILEDSN=test.dsn"
 ’set cn = Server.CreateObject("ADODB.Connection")
 ’cn.Open strDSN
 
 ’strSQL = "INSERT INTO info(firstname,lastname,sex,age) VALUES(‘good’,'hoo’,'male’,43)"
 ’cn.Execute(strSQL)
 
 set rsInfo = Server.CreateObject("ADODB.Recordset")
 
 strSQL = "INSERT INTO info(firstname,lastname,sex,age) VALUES(‘"&strFName&"’,'"&strLName&"’,'"&strSex&"’,"&intAge&")"
 ’rsInfo.Open strSQL,cn
 rsInfo.Open strSQL,strDSN
 
 strSQL = "SELECT * FROM info" 
 ’rsInfo.Open strSQL,cn
 rsInfo.Open strSQL,strDSN

 set objno = rsInfo("No")
 set objfname = rsInfo("firstname")
 set objlname = rsInfo("lastname")
 set objsex = rsInfo("sex")
 set objage = rsInfo("age")

 Response.Write "<table cellspacing=0 cellpadding=0 border=1><tr><td>No</td><td>firstname</td><td>lastname</td><td>sex</td><td>age</td></tr>"

 Do Until rsInfo.EOF
 Response.Write "<tr><td>"&objno&"</td><td>"&objfname&"</td><td>"&objlname&"</td><td>"&objsex&"</td><td>"&objage&"</td></tr>"
 rsInfo.MoveNext
 Loop

 Response.Write "</table>"

 rsInfo.Close
 set rsInfo = Nothing

ElseIf strCommand = "command" Then
‘使用Command对象进行操作
 strDSN = "FILEDSN=test.dsn"
 set cn = Server.CreateObject("ADODB.Connection")
 cn.Open strDSN
 
 set cm = Server.CreateObject("ADODB.Command")
 set cm.ActiveConnection = cn
 
 cm.CommandText = "INSERT INTO info(firstname,lastname,sex,age) VALUES(?,?,?,?)"
 cm.Prepared = True
 
 cm.Parameters.Append cm.CreateParameter("vfname",200,,255)
 cm.Parameters.Append cm.CreateParameter("vlname",200,,255)
 cm.Parameters.Append cm.CreateParameter("vsex",200,,255)
 cm.Parameters.Append cm.CreateParameter("vage",3,,4)
 ’CreateParameter中的Type参数应该使用数值
 
 cm("vfname")=strFName
 cm("vlname")=strLName
 cm("vsex")=strSex
 cm("vage")=intAge
 cm.Execute
 
 strSQL = "SELECT * FROM info"
 set rsInfo = cn.Execute(strSQL) ‘返回Recordset对象
 

 set objno = rsInfo("No")
 set objfname = rsInfo("firstname")
 set objlname = rsInfo("lastname")
 set objsex = rsInfo("sex")
 set objage = rsInfo("age")

 Response.Write "<table cellspacing=0 cellpadding=0 border=1><tr><td>No</td><td>firstname</td><td>lastname</td><td>sex</td><td>age</td></tr>"

 Do Until rsInfo.EOF
 Response.Write "<tr><td>"&objno&"</td><td>"&objfname&"</td><td>"&objlname&"</td><td>"&objsex&"</td><td>"&objage&"</td></tr>"
 rsInfo.MoveNext
 Loop

 Response.Write "</table>"

 cn.Close
 set rsInfo = Nothing
 set cm = Nothing
 set cn = Nothing 
End If
strGet = ""
%>

</body>
</html>


评论

该日志第一篇评论

发表评论

评论也有版权!