前段日子发表的文章,数据库的连接代码可以直接在ASP文件中显示出来。这次又进行了一次封装。
打开vb,新建Activex控件,工程名称为WebDb,类模块名称为GetInfomation
引用”Microsoft Activex Data Object 2.6 Library ”
Private Conn As ADODB.Connection
Private Rs As ADODB.Recordset
‘作用:判断数据库是否正确连结
'自己可以更改连接串
Public Function GetConn()
Conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=yang"
If Err.Number <> 0 Then
GetConn = False
Else
GetConn = True
End If
End Function
‘根据输入的雇员ID,得到雇员的名称
Public Function GetEmployeeName(strEmployeeID As Integer) As String
Dim strSql As String
Set rs = New ADODB.Recordset
strSql = "select LastName+firstname from employees where EmployeeID=" & strEmployeeID
rs.Open strSql, Conn, adOpenStatic, adLockOptimistic
If rs.EOF Then
GetEmployeeName = ""
Else
GetEmployeeName = rs.Fields(0)
End If
rs.Close
End Function
‘返回所有的雇员列表
Public Function GetEmployeeList() As ADODB.Recordset
Dim strSql As String
Set rs = New ADODB.Recordset
strSql = "select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City from employees"
rs.CursorLocation = adUseClient
rs.Open strSql, Conn, adOpenStatic
Set GetEmployeeList = rs
'rs.Close
End Function
我们进行测试
新建ASP页面,”TestWebDb1.asp”。主要用来测试GetEmployeeList()方法
1<head>
2<!--- 测试页 --->
3<!--- 功能:测试组件 --->
4<!--- 作者:龙卷风.NET --->
Dim strTopic
Dim strTitle
Dim strContents
Dim DataQuery
Dim Rs
Dim Myself
Myself=Request.ServerVariables("script_name")
Set DataQuery=Server.CreateObject("WebDb.GetInfomation")
Set Rs=Server.CreateObject("adodb.recordset")
1
2<title>
3
4数据组件测试页
5
6</title>
7<h1><center>欢迎使用数据组件( www.domain.com)</center></h1>
Dim Flag
Flag=DataQuery.GetConn()
If Flag=false then
ResPonse.Write "数据库没有连结,请检查"
ResPonse.End
End if
Set Rs=DataQuery.GetEmployeeList()
if rs.eof then
Response.write "没有数据,请查询"
Response.end
end if
Rs.PageSize =3
Page= CLng(Request.QueryString ("Page"))
If Page < 1 Then Page = 1
If Page > Rs.PageCount Then Page = Rs.PageCount
Response.Write "<center><table border="1" cellpadding="2" cellspacing="0">"
Response.Write "<tr align="center" bgcolor="silver">"
Response.Write "<td>EmployeeID</td>"
Response.Write "<td>LastName</td>"
Response.Write "<td>FirstName</td>"
Response.Write "<td>Title</td>"
Response.Write "<td>TitleOfCourtesy</td>"
Response.Write "<td>BirthDate</td>"
Response.Write "<td>HireDate</td>"
Response.Write "<td>Address</td>"
Response.Write "<td>City</td>"
Response.Write "</tr>"
Rs.AbsolutePage = Page
For iPage = 1 To Rs.PageSize
Response.Write "<tr align="right">"
for i=0 to Rs.fields.count-1
Response.Write "<td>"&Rs.fields.item(i)&"</td>"
next
Response.Write "</tr>"
Rs.MoveNext
If Rs.EOF Then Exit For
next
Response.Write "</table></center>"
1
2<form method="get" name="myform">
If Page <> 1 Then
1
2<a href="```
3=Myself
4```?Page=1">第一页</a>
5<a href="```
6=Myself
7```?Page=```
8=(Page-1)
9```">上一页</a>
End If
If Page <> Rs.PageCount Then
1
2<a href="```
3=Myself
4```?Page=```
5=(Page+1)
6```">下一页</a>
7<a href="```
8=Myself
9```?Page=```
10=Rs.PageCount
11```">最后的一页</a>
End If
1
2页次:<font color="Red">```
3=Page
4```/```
5=Rs.PageCount
6```</font>
7</form>
Rs.close
1
2新建ASP页面,”TestWebDb2.asp”。主要用来测试GetEmployeeName()方法
3
4这个页面相对简单一些
5
6<head>
7<!--- 测试页 --->
8<!--- 功能:测试组件 --->
9<!--- 作者:龙卷风.NET --->
Dim DataQuery
Dim strID
Dim strResult
Set DataQuery=Server.CreateObject("WebDb.GetInfomation")
1
2<title>
3数据组件测试页
4</title>
5<h1><center>欢迎使用数据组件( www.domain.com)</center></h1>
If Len(Request.QueryString("ID")) > 0 Then
strID = Request.QueryString("ID")
Dim Flag
Flag=DataQuery.GetConn()
If Flag=false then
ResPonse.Write "数据库没有连结,请检查"
ResPonse.End
End if
strResult=DataQuery.GetEmployeeName(cint(strID))
if strResult="" then
Response.Write "对不起,没有这个编号,请查询"
Response.End
else
ResPonse.Write strResult
end if
End If
1
2<form name="MyForm">
3<input name="EmpID" size="40" type="TEXTBOX"/><p>
4<input language="VBScript" onclick="window.location.href = 'TestWebDb2.asp?ID=' _
5
6\+ MyForm.EmpID.Value" type="BUTTON" value="Search"/>
7</p></form>
8</head>
9
10一些想法:数据库连结如果放到组件里,如果要修改数据库连结,则要重新编译组件,可能会由此引发一些问题(但是推荐这种,毕竟数据库名和服务器名不会经常改)
11
12如果数据库连结放到ASP页面,可以通过属性传值到组件中,但是安全性会降低。
13
14真是……
15
16好了,这篇文章结束了,希望对大家有帮助
17
18今后的话题便是如何实现AspToDll这个软件的功能,我们一步一步实现。</head>