In the article published some time ago, the database connection code can be displayed directly in the ASP file. This time it was encapsulated again.
Open vb, create a new Activex control, the project name is WebDb, the class module name is GetInfomation,
reference "Microsoft Activex Data Object 2.6 Library"
Private Conn As ADODB.Connection
Private Rs As ADODB.Recordset
'Function: Determine whether the database is correctly connected
' You can change the connection string
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
'According to the entered employee ID, get the employee's name
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
'Return all employee listsPublic
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
We
create a new ASP page for testing, "TestWebDb1.asp". Mainly used to test the GetEmployeeList() method
<HEAD>
<!- Test page->
<!- Function: Test component->
<!- Author: Tornado.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")
%>
<TITLE>
Data component test page
</TITLE>
<H1><CENTER>Welcome to the data component ( www.downcodes.com)</CENTER></H1 >
<%
Dim Flag
Flag=DataQuery.GetConn()
If Flag=false then
ResPonse.Write "There is no link to the database, please check"
ResPonse.End
End if
Set Rs=DataQuery.GetEmployeeList()
if rs.eof then
Response.write "No data, please query"
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 cellspacing=0 cellpadding=2>"
Response.Write "<tr BGCOLOR=silver align=center>"
Response.Write "<td>EmployeeID</TD>"
Response.Write "<td>LastName</td>"
Response.Write "<td>FirstName</td>"
Response.Write "<td>Title</a></td>"
Response.Write "<td>TitleOfCourtesy</ a></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>"
%>
<Form name="myform" method="get">
<%If Page <> 1 Then%>
<A HREF="<%=Myself%>?Page=1">First page</A>
<A HREF ="<%=Myself%>?Page=<%=(Page-1)%>">Previous page</A>
<%End If%>
<%If Page <> Rs.PageCount Then%>
<A HREF="<%=Myself%>?Page=<%=(Page+1)%>">Next page</A>
<A HREF="<%=Myself%>?Page=<%=Rs. PageCount%>">Last page</A>
<%End If%>
Page:<FONT COLOR="Red"><%=Page%>/<%=Rs.PageCount%></FONT>
< /Form>
<%
Rs.close
%>
Create a new ASP page, "TestWebDb2.asp". Mainly used to test the GetEmployeeName() method,
this page is relatively simple
<HEAD>
<!- Test page->
<!- Function: Test component->
<!- Author: Tornado.NET ->
<%
Dim DataQuery
Dim strID
Dim strResult
Set DataQuery=Server.CreateObject("WebDb.GetInfomation")
%>
<TITLE>
Data component test page
</TITLE>
<H1><CENTER>Welcome to Data Components ( www.downcodes.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 "There is no link to the database, please check"
ResPonse.End
End if
strResult=DataQuery.GetEmployeeName(cint(strID))
if strResult="" then
Response.Write "Sorry, there is no such number, please query"
Response.End
else
ResPonse.Write strResult
end if
End If
%>
<FORM NAME="MyForm">
<INPUT TYPE=TEXTBOX NAME="EmpID" SIZE=40><P>
<INPUT LANGUAGE="VBScript"
TYPE="BUTTON"
VALUE="Search"
ONCLICK="window.location.href = 'TestWebDb2.asp?ID=' _
+ MyForm.EmpID.Value">
</FORM>
</HEAD>
Some ideas: If the database link is placed in a component, if you want to modify the database link, you will need to recompile the component, which may cause some problems (but this is recommended, after all, the database name and server name will not change often)
If the database link is placed on the ASP page, the value can be passed to the component through attributes, but the security will be reduced.
Really...
Okay, this article is over. I hope it will be helpful to everyone.
The future topic is how to implement the functions of the AspToDll software. We will implement it step by step.