Common ASP Operations Database classes

Here's the code: <% Class Dbclass ’------------------------------------------------------------------------- ' Variable description ' Conn-----------Connection object ' strSQL---------The statement that executes the query ' Vtbname table name--------Query paging ' Vpkey----------The primary key of a table that queries pagination ' vpgfields------Query paging fields to display ' Vpgsize--------The number of records displayed per page of the query page ' Vcurrpg the current page displayed--------query paging ' vconditions----Query paging conditions ' Vorderby-------Query paging sorting ’------------------------------------------------------------------------- Private Conn,strsql,vtbname,vpkey,vpgfields,vpgsize,vcurrpg,vconditions,vorderby ' Initialization of the class Private Sub Class_Initialize () ' Set the following two variables when it is an MS SQL database webjx.com The name or IP address of the ' Dim dbserver ' Database Server Name of the ' Dim dbname ' Database Dim DBPath ' If an Access database, set its path here Dim Dbuser ' Login Username for database Dim dbpass ' Login Password for database Dim connstr DBPath = "/testasp/data/data.mdb" Set database path Dbuser = "Admin" Webjx.com Dbpass = "123456" ' If you have access, and you have a password ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & Server.MapPath (DBPath) &_ "; User id= "& Dbuser &"; password=; Jet oledb:database password= "& Dbpass ' If access, and no password ' ConnStr = ' Provider = microsoft.jet.oledb.4.0;data Source = ' & Server.MapPath (DBPath) ' If the Ms-sql database ' ConnStr = ' Provider = SQLOLEDB; User ID = "& Dbuser &"; Password = "& Dbpass &_ ’ "; Initial Catalog = "& dbname &"; Data Source = "& DBServer Webjx.com On Error Resume Next Set Conn=server. CreateObject ("Adodb.connection") Conn.Open ConnStr ErrMsg "Connection Database" End Sub ' Class end Private Sub Class_Terminate () Conn.close Set conn=nothing End Sub ’------------------------------------------------------------------------- ' Set the value of the variable for the class ’------------------------------------------------------------------------- ' Set up SQL statements Public Property Let Sqlstr (Byval Values) Strsql=values End Property ' Set table name for query paging Public Property Let Tbname (Byval Values) Vtbname=values End Property ’-------------------------------------------------------- ' Set the primary key for a table that queries pagination Public Property Let Pkey (ByVal Values) Vpkey=values End Property ’-------------------------------------------------------- ' Set the fields displayed Public Property Let Pgfields (ByVal Values) Vpgfields=values End Property ’-------------------------------------------------------- ' Set the number of records to display 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 criteria for a query 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 query Public Property Let by (ByVal Values) If Len (Values) >0 Then vorderby= "ORDER BY" &values Else Vorderby=values End If End Property '-------------------------------------------------------------Web Teaching Network ' 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 total pages Public Property Get Vpgcount () Web Teaching Network Irscount2=vrscount () If IRsCount2 mod vpgsize =0 Then Vpgcount=int (Irscount2/vpgsize) Else Vpgcount=int (irscount2/vpgsize) +1 End If End Property ' Querying 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 '-------------------------------------------------------------------------webjx.com ' Recordset function to implement pagination Public Function pagers () On Error Resume Next Dim startrs Startrs= (vCurrPg-1) *vpgsize ’------------------------------------------------------------------------------------------- ' Use this statement to modify the code based on the parameters, specifically if the sort is ASC < change to >,min 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 ' &A mp;vpkey& "<" ' strsql=strsql& ' (select min ("&vPKey&") from (select Top &startRs& "&vPKey&" from "WEBJX . Com ' strsql=strsql&vtbname& ' "&vConditions&" "&vOrderBy&") as IDTable) "&vorderby ' End If ’--------------------------------------------------------------- If Startrs=0 Then Strsql= "SELECT Top" &vPgSize& "&vPgFields&" "&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 Webjx.com End If ’------------------------------------------------------------------- Set Pagers=server. CreateObject ("Adodb.recordset") Pagers.open strsql,conn,0,1 ErrMsg "Record Paging" End Function '------SQL stored procedure paging------------------------------------------------------ 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 Webjx.com Sqlpage.locktype = 1 Sqlpage.open Cm ErrMsg "Record Paging" End Function ’---------------------------------------------------------------------------- ' Close recordset objRS ’---------------------------------------------------------------------------- Public Function cRs (ByVal objRS) Objrs.close () Set objRS = Nothing End Function '----------------------pagination page navigation--------------------------------------- Public Function Pagenav () Irscount=vrscount () ' Total number of records MYPAGE=VCURRPG ' current page Pgcount=vpgcount () ' Total 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" > Total "&iRsCount&" record "&mypage&"/"&pgcount & "</span>" webjx.com 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 Next astr=astr& "<a href= '" currpage= "&nextPage&" ' class= ' AW ' > </a><a ' href= '? currpage= ' &PgCount& "' class= ' AW ' > End </a></div>" Pagestr=pagestr&astr Pagenav=pagestr End Function ' Output with paging function table Function SHOWTB (ByVal tbtil) webjx.com Set Rstb=pagers () ' Call sqlpage if stored procedure () tbrs= Rstb.getrows () CRs (RSTB) Itblrow=ubound (tbrs,2) Itblcol=ubound (Tbtil) tbstr= "<table border= ' 0 ' cellspacing= ' 0 ' cellpadding= ' 0 ' ><tbody>" For R1=0 to Itblcol tr1=tr1& "<td width= '" &split (Tbtil (R1), "|") (1) & "' >" &split (Tbtil (R1), "|") (0) & "</td>" Next 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>" Next tr=tr& "<tr>" &td& "</tr>" Td=null Next 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 SQL statements so that statements are checked for errors Public Sub Prnsql () Response. Write (strSQL) End Sub ’------------------------------------------------------------------------- ' Fault-tolerant function ’------------------------------------------------------------------------- Private Function errmsg (errmsg) If err.number<>0 Then ' Problems can be used here code to print out descriptive information for easy debugging. can be commented out Response. Write (Cstr (err.description) & "<br>") Err.Clear Response.Write "<font color= ' #FF0000 ' >" &errMsg& "Error </font>" notes Response.End () End If End Function ’------------------------------------------------------------------------- ' Fault tolerance function ended ’------------------------------------------------------------------------- End Class %>