The functions of the program have a general framework. In fact, you can add some functions yourself, such as the initial database connection. You can set
variables first and then select different types of databases through INIT()
<%
'On Error Resume Next
ClassConnEx
publicConnEx
public DBpath '---------database path
public DBtype '---------Database type 1 (Access) 2 (SqlServer) 3 (expandable)
public ConnMethod '--------Connection method (DSN, non-DSN)
public User
public Pass
Sub Class_initialize
End Sub
Sub Init()
ConnStr = "Driver={Microsoft Access Driver (*.mdb)};dbq="&Server.MapPath("Date.mdb")
Set ConnEx = Server.Createobject("ADODB.CONNECTION")
ConnEx.Open ConnStr
CatchError("Class_Terminate")
End Sub
Sub CatchError( Str )
If Err Then
Err.Clear
Class_Terminate()
Response.Write("Error caught, program ends! At "&Str&"")
Response.End()
End If
End Sub
'******************************************
'*Searching for the existence of records through SQL statements is error-prone'************************************ ******
Function HasRecordBySql(Sql)
Call CheckSql(Sql,"R")
Dim Rs,HasR
Set Rs = ConnEx.Execute( Sql )
CatchError("HasReordSql")
If Not (Rs.eof Or Rs.bof) Then
HasR=False
Else
HasR=True
End If
Rs.Close
Set Rs = Nothing
HasRecordBySql = HasR
End Function
'******************************************
'*Find whether the record exists by ID'**********************************
Function HasRecordById(StrTableName, IntID)
'CheckValue(IntID, 1)
Dim Rs,HasR
Sql = "Select top 1 * from "&StrTableName&" Where Id = "&IntID
Call CheckSql(Sql,"R")
Set Rs = ConnEx.Execute(Sql)
CatchError("HasRecordByID")
If Not (Rs.eof Or Rs.bof) Then
HasR=False
Else
HasR=True
End If
Rs.close
Set Rs = Nothing
HasRecordById = HasR
End Function
'**********************************************
'*Get the record set through SQL statement'****************************************** *****
Function GetRsBySql( Sql )
Call CheckSql(Sql,"R")
Dim Rs
Set Rs = Server.CreateObject("Adodb.RecordSet")
Rs.Open Sql,ConnEx,1,1
Set GetRsBySql = Rs
End Function
'************************************************
'*Get the value of a certain field'****************************************** ****
Function GetValueBySql( Sql )
Call CheckSql(Sql,"R")
Dim Rs,ReturnValue
Set Rs = ConnEx.Execute(Sql)
CatchError("GetValueBySql")
If Not( Rs.Eof Or Rs.Bof ) Then
ReturnValue = Rs(0)
Else
ReturnValue = "No record"
End If
Rs.Close
Set Rs = Nothing
GetValueBySql = ReturnValue
End Function
'============================Update,Insert================ ====
'************************************************
'*Use SQL to modify data'****************************************** **
Function UpdateBySql( Sql )
Call CheckSql(Sql,"w")
ConnEx.Execute(Sql)
CatchError("UpdateBySql")
UpdateBySql = True
End Function
'************************************************
'*Insert data using SQL statements'****************************************** **
Function InsertBySql(Sql)
Call CheckSql(Sql,"w")
ConnEx.Execute(Sql)
CatchError("InsertBySql")
InsertBySql = True
End Function
'======================Delete======================
'*** *****************************************
'*Delete via SQL statement'************************************************ *
Function DeleteBySql(Sql)
Call CheckSql(Sql,"D")
ConnEx.Execute(Sql)
CatchError("DeleteBySql")
DeleteBySql = True
End Function
'************************************************
'*Check the SQL statement permissions and detect the permissions owned by the statement according to the flag'********************************** *************
Sub CheckSql( Sql , Flag )
Dim StrSql,SinCounts,DouCounts,i
StrSql = Lcase(Sql)
SinCounts = 0
DouCounts = 0
For i = 1 to Len(StrSql)
If Mid(StrSql,i,1) = "'" Then SinCounts = SinCounts + 1
If Mid(StrSql,i,1) = """" Then DouConnts = DouCounts + 1
Next
If (SinCounts Mod 2) <> 0 Or (DouCounts Mod 2) <> 0 Or Instr(StrSql,";") > 0 Then
Call Class_Terminate()
Response.Write("SQL syntax error!")
Response.End()
End If
Select Case Flag
Case "R","r":
If Instr(StrSql,"delete") > 0 Or Instr(StrSql,"update") Or Instr(StrSql,"drop") > 0 Or Instr(StrSql,"insert") > 0 Then
Class_Terminate()
Response.Write("Insufficient permissions, no permission to perform write operations")
Response.End()
End If
Case "W","w":
If Instr(StrSql,"delete") > 0 Or Instr(StrSql,"drop") > 0 Or Instr(StrSql,"select") > 0 Then
Class_Terminate()
Response.Write("Insufficient permissions, no permission to perform delete operation")
Response.End()
End If
Case "D","d":
Case Else:
Response.Write("Function CheckSql flag error!")
End Select
End Sub
Sub Class_Terminate
If Not IsEmpty(FriendConn) Then
FriendConn.Close
Set FriendConn = Nothing
CatchError()
End If
End Sub
End Class
%>