ASP tutorial: database operation class written by yourself

Author：Eve Cole Update Time：2025-06-04 13:32:02

Recommended: Talk about writing code similar to search engine functions using asp
First, create an access database. There is a URLINDEX table in the library, where the URL and Keywords fields are indexed respectively, as follows: The following is the referenced content: URL text (index

There are a lot of database operation categories searching online. I am not better than those, but I just wrote them myself and used them more. So there is nothing special about me, I just use my habits. As for efficiency and other aspects, please give me advice! !

Program code:

The following is the quoted content:

Class dbClass

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'Variable description

'conn---------------------------------------------------------------------------------------------------------------------------

'strsql-------------------------------------------------------------------------------------------------------------------------

'vTbName------------------------------------------------------------------------------------------------------------------------

'vPKey-------------------------------------------------------------------------------------------------------------------------

'vPgFields---------Query the fields to be displayed on the page

'vPgSize--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'vCurrPg-----------------------------------------------------------------------------------------------------------------------

'vConditions----Query paging conditions

'vOrderBy-------------Sorting of query pages

private conn,strsql,vTbName,vPKey,vPgFields,vPgSize,vCurrPg,vConditions,vOrderBy

'Initialization of Class

private Sub Class_Initialize()

'When it is an MS Sql database, set the following two variables

'dim dbServer' name or ip address of the database server

'dim dbname' database name

dim dbPath 'If it is an Access database, set its path here

dim dbUser 'The login username of the database

dim dbPass 'Domain's login password

dim connstr

dbPath = /testasp/data/data.mdb 'Set the database path

dbUser = admin

dbPass = 123456

'If it's access, and has a password

connstr = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & Server.MapPath(dbPath) &_

;User ID= & dbUser & ;Password=;Jet OLEDB:Database Password= & dbPass

'If it's access and there's no password

'connstr = Provider = Microsoft.Jet.OLEDB.4.0;Data Source = & Server.MapPath(dbPath)

'If it is an ms-sql database

'connstr = Provider = Sqloledb; User ID = & dbUser & ; Password = & dbPass &_

' ; Initial Catalog = & dbname & ; Data Source = & dbServer

on error resume next

set conn=server.CreateObject(adodb.connection)

conn.open connstr

errMsg Connect to the database

End Sub

'Class End

Private Sub Class_terminate()

conn.close

set conn=nothing

End Sub

'Set values for variables of the class

'Set SQL statements

Public Property Let sqlStr(Byval Values)

strsql=Values

End Property

'Set the table name of the query page

public property let tbName(Byval Values)

vTbName=Values

end property

'Set the primary key of the query paged table

public property let pKey(ByVal Values)

vPKey=Values

end property

'Set the displayed fields

public property let pgFields(ByVal Values)

vPgFields=Values

end property

'Set the number of records displayed per page

public property let pgSize(ByVal Values)

vPgSize=Values

end property

'Set the number of pages currently displayed

public property let currPg(ByVal Values)

vCurrPg=Values

end property

'Set the query conditions

public property let conditions(ByVal Values)

if Len(Values)>0 then

vConditions= where &Values

else

vConditions= where 1=1

end if

end property

'Set the sort of queries

public property let orderBy(ByVal Values)

if Len(Values)>0 then

vOrderBy= order by &Values

else

vOrderBy=Values

end if

end property

'Get the total number of records

public property get vRsCount()

if vCurrPg=1 then

sqlc=select count(&vPKey&) as Idcount from &vTbName& &vConditions

set rsc=server.CreateObject(adodb.recordset)

rsc.open sqlc,conn,0,1

RsNum=rsc(IdCount)

rsc.close

set rsc=nothing

if RsNum>0 then

response.Cookies(iRecord)=RsNum

vRsCount=RsNum

else

vRsCount=0

end if

else

vRsCount=request.Cookies(iRecord)

end if

end property

'Get the total number of pages

public property get vPgCount()

iRsCount2=vRsCount()

if iRsCount2 mod vPgSize =0 then

vPgCount=int(iRsCount2/vPgSize)

else

vPgCount=int(iRsCount2/vPgSize) 1

end if

end property

'Query the database

Public Function rsDB()

on error resume next

'Simple query results

' set rsDB = Server.CreateObject(ADODB.RecordSet)

' rsDB.Open strsql,conn,1,3

Set rsDB=conn.Execute(strsql)

errMsg query database

End Function

'Add, update, delete database records

public Function upDB()

on error resume next

conn.execute(strsql)

errMsg Edit database records

end Function

'The record set function used to implement paging

public function pageRs()

on error resume next

dim startRs

startRs=(vCurrPg-1)*vPgSize

'If you use this statement, you need to modify the code according to the parameters. Specifically, if the sort is asc, then <change to>, and min is changed to max

' if startRs=0 then

' strsql=select top &vPgSize& &vPgFields& from &vTbName& &vConditions& &vOrderBy

' else

' strsql=select top &vPgSize& &vPgFields& from &vTbName& &vConditions& and &vPKey& <

' strsql=strsql&(select min(&vPKey&) from (select top &startRs& &vPKey& from

' strsql=strsql&vTbName& &vConditions& &vOrderBy&) as idTable) &vOrderBy

' end if

if startRs=0 then

strsql=select top &vPgSize& &vPgFields& from &vTbName& &vConditions& &vOrderBy

else

strsql=select top &vPgSize& &vPgFields& from &vTbName& &vConditions& and &vPKey& not

strsql=strsql&in (select top &startRs& &vPKey& from &vTbName& &vConditions& &vOrderBy

strsql=strsql&) &vOrderBy

end if

set pageRs=server.CreateObject(adodb.recordset)

pageRs.open strsql,conn,0,1

errMsg Record pagination

end function

public function sqlPage()

on error resume next

Set sqlPage=server.CreateObject(Adodb.RecordSet)

Set Cm=Server.CreateObject(Adodb.Command)

Cm.CommandType = 4

Cm.ActiveConnection = conn

Cm.CommandText=sp_Util_Page

Cm.parameters(1) = vPgFields

Cm.parameters(2) = vTbName

Cm.parameters(3) = vConditions

Cm.parameters(4) = vOrderBy

Cm.parameters(5) = vPKey

Cm.parameters(6) = vCurrPg

Cm.parameters(7) = vPgSize

Cm.parameters(8) = vRsCount()

Cm.parameters(9) =

sqlPage.CursorLocation = 3

sqlPage.LockType = 1

sqlPage.Open Cm

errMsg Record pagination

end function

'Close the record set objRs

Public Function cRs(ByVal ObjRs)

ObjRs.close()

Set ObjRs = Nothing

End Function

public function pageNav()

iRsCount=vRsCount()'Total number of records

mypage=vCurrPg'CurrPg'CurrPg'Current page count

PgCount=vPgCount()'Total number of pages

prePage=mypage-1

if prePage<1 then

prePage=1

end if

nextPage=mypage 1

if nextPage>PgCount then

nextPage=PgCount

end if

pagestr=<div id=fy><span id=rpc>There are a total of &iRsCount& records&mypage&/&PgCount&</span>

pagestr=pagestr&<a href='?currpage=1' class='aW'>Home</a><a href='?currpage=&prePage&' class='aW'>Previous page</a>

if (mypage-1) mod 4=0 then

firstPage=mypage

elseif int((mypage-1)/4)=0 then

firstPage=1

else

firstPage=int((mypage-1)/4)*4 1

end if

endPage=firstPage 4

astr=

for i=firstPage to endPage

astr=astr&<a href='?currpage=&i&'

if Cstr(mypage)=Cstr(i) then

astr=astr& id='currP'

end if

astr=astr&>&i&</a>

if i>PgCount-1 then exit for

astr=astr&<a href='?currpage=&nextPage&' class='aW'>Last page</a><a href='?currpage=&PgCount&' class='aW'>Last page</a></div>

pagestr=pagestr&astr

pageNav=pagestr

end function

'Output table with paging function

Function showTb(ByVal TbTil)

set rsTb=pageRs()'If it is a stored procedure, call sqlPage()

tbRs= rsTb.getrows()

cRs(rsTb)

iTblRow=Ubound(tbRs,2)

iTblCol=Ubound(TbTil)

tbStr=<table border='0' cellpacing='0' cellpadding='0' cellpadding='0'><tbody>

for r1=0 to iTblCol

tr1=tr1&<td width='&split(TbTil(r1),|)(1)&'>&split(TbTil(r1),|)(0)&</td>

tr1=<tr>&tr1&</tr>

for ri=0 to iTblRow

for ci=0 to iTblCol

td=td&<td width='&split(TbTil(ci),|)(1)&'>&tbRs(ci,ri)&</td>

tr=tr&<tr>&td&</tr>

td=null

TbTil=null

tbRs=null

response.Write(tbStr&tr1&tr&<tr><td colspan='&iTblCol 1&'>&pageNav()&</td></tr></tbody></table>)

tbStr=null

tr1=null

tr=null

End Function

'Print the SQL statement so that the statement will check if there are any errors

Public Sub prnSql()

response.Write(strsql)

End Sub

'Fault-tolerant functions

Private Function errMsg(errMsg)

If Err.number<>0 Then

'If there is a problem, you can use the code here to print out the description information for easy debugging. Comment out

response.Write(Cstr(Err.description)&<br>)

Err.Clear

Response.Write <font color='#FF0000'>&errMsg&Error</font> 'Comment

Response.End()

End If

End Function

'The fault tolerance function ends

End Class

Share: FileSystemObject sample code
In the sample code described in this section, real-life examples are provided to illustrate many of the features available in the FileSystemObject object pattern. This code shows all the features of how to use object patterns together, and how to use them effectively in your own code