利用JSP的思想来做ASP

  1   
  2’On Error Resume Next   
  3Class ConnEx   
  4public ConnEx   
  5public DBpath ’---------数据库路径   
  6public DBtype ’---------数据库类型 1(Access) 2(SqlServer) 3(可扩充)   
  7public ConnMethod ’--------连接方式 （DSN，非DSN）   
  8public User   
  9public Pass   
 10Sub Class_initialize   
 11End Sub 
 12
 13Sub Init()   
 14ConnStr = "Driver={Microsoft Access Driver (*.mdb)};dbq="&Server.MapPath("Date.mdb")   
 15Set ConnEx = Server.Createobject("ADODB.CONNECTION")   
 16ConnEx.Open ConnStr   
 17CatchError("Class_Terminate")   
 18End Sub 
 19
 20Sub CatchError( Str )   
 21If Err Then   
 22Err.Clear   
 23Class_Terminate()   
 24Response.Write("捕捉到错误,程序结束!在"&Str&"处")   
 25Response.End()   
 26End If   
 27End Sub 
 28
 29’******************************************   
 30’*通过SQL语句来查找记录是否存在,容易出错   
 31’****************************************** 
 32
 33Function HasRecordBySql( Sql )   
 34Call CheckSql(Sql,"R")   
 35Dim Rs,HasR   
 36Set Rs = ConnEx.Execute( Sql )   
 37CatchError("HasReordSql")   
 38If Not (Rs.eof Or Rs.bof) Then   
 39HasR = False   
 40Else   
 41HasR = True   
 42End If   
 43Rs.Close   
 44Set Rs = Nothing   
 45HasRecordBySql = HasR   
 46End Function 
 47
 48’***************************************   
 49’*通过ID来查找记录是否存在   
 50’*************************************** 
 51
 52Function HasRecordById( StrTableName , IntID )   
 53’CheckValue( IntID , 1 )   
 54Dim Rs,HasR   
 55Sql = "Select top 1 * from "&StrTableName&" Where Id = "&IntID   
 56Call CheckSql(Sql,"R")   
 57Set Rs = ConnEx.Execute(Sql)   
 58CatchError("HasRecordByID")   
 59If Not (Rs.eof Or Rs.bof) Then   
 60HasR = False   
 61Else   
 62HasR = True   
 63End If   
 64Rs.close   
 65Set Rs = Nothing   
 66HasRecordById = HasR   
 67End Function 
 68
 69’**********************************************   
 70’*通过SQL语句取得记录集   
 71’**********************************************   
 72Function GetRsBySql( Sql )   
 73Call CheckSql(Sql,"R")   
 74Dim Rs   
 75Set Rs = Server.CreateObject("Adodb.RecordSet")   
 76Rs.Open Sql,ConnEx,1,1   
 77Set GetRsBySql = Rs   
 78End Function 
 79
 80’*********************************************   
 81’*取得某个字段的值   
 82’*********************************************   
 83Function GetValueBySql( Sql )   
 84Call CheckSql(Sql,"R")   
 85Dim Rs,ReturnValue   
 86Set Rs = ConnEx.Execute(Sql)   
 87CatchError("GetValueBySql")   
 88If Not( Rs.Eof Or Rs.Bof ) Then   
 89ReturnValue = Rs(0)   
 90Else   
 91ReturnValue = "没有记录"   
 92End If   
 93Rs.Close   
 94Set Rs = Nothing   
 95GetValueBySql = ReturnValue   
 96End Function 
 97
 98’============================Update,Insert==================== 
 99
100’*********************************************   
101’*利用SQL修改数据   
102’*********************************************   
103Function UpdateBySql( Sql )   
104Call CheckSql(Sql,"w")   
105ConnEx.Execute(Sql)   
106CatchError("UpdateBySql")   
107UpdateBySql = True   
108End Function 
109
110’********************************************   
111’*利用SQL语句插入数据   
112’********************************************   
113Function InsertBySql(Sql)   
114Call CheckSql(Sql,"w")   
115ConnEx.Execute(Sql)   
116CatchError("InsertBySql")   
117InsertBySql = True   
118End Function 
119
120’=====================Delete===================== 
121
122’********************************************   
123’*通过SQL语句删除   
124’********************************************   
125Function DeleteBySql( Sql )   
126Call CheckSql(Sql,"D")   
127ConnEx.Execute(Sql)   
128CatchError("DeleteBySql")   
129DeleteBySql = True   
130End Function 
131
132’********************************************   
133’*检查SQL语句权限，根据标志Flag 来检测语句拥有的权限   
134’********************************************   
135Sub CheckSql( Sql , Flag )   
136Dim StrSql,SinCounts,DouCounts,i   
137StrSql = Lcase(Sql)   
138SinCounts = 0   
139DouCounts = 0   
140For i = 1 to Len(StrSql)   
141If Mid(StrSql,i,1) = "’" Then SinCounts = SinCounts + 1   
142If Mid(StrSql,i,1) = """" Then DouConnts = DouCounts + 1   
143Next 
144
145If (SinCounts Mod 2) <> 0 Or (DouCounts Mod 2) <> 0 Or Instr(StrSql,";") > 0 Then   
146Call Class_Terminate()   
147Response.Write("SQL语法错误!")   
148Response.End()   
149End If   
150Select Case Flag   
151Case "R","r":   
152If Instr(StrSql,"delete") > 0 Or Instr(StrSql,"update") Or Instr(StrSql,"drop") > 0 Or Instr(StrSql,"insert") > 0 Then   
153Class_Terminate()   
154Response.Write("权限不足,没有执行写操作的权限")   
155Response.End()   
156End If   
157Case "W","w":   
158If Instr(StrSql,"delete") > 0 Or Instr(StrSql,"drop") > 0 Or Instr(StrSql,"select") > 0 Then   
159Class_Terminate()   
160Response.Write("权限不足，没有执行删除操作的权限")   
161Response.End()   
162End If   
163Case "D","d":   
164Case Else:   
165Response.Write("函数CheckSql标志错误!")   
166End Select   
167End Sub 
168
169Sub Class_Terminate   
170If Not IsEmpty(FriendConn) Then   
171FriendConn.Close   
172Set FriendConn = Nothing   
173CatchError()   
174End If   
175End Sub   
176End Class