在进行数据库的查询时会经常遇到这样的情况
例如想在一个用户数据库中查询他的用户名和他的密码但恰好该用户使用的名字和密码中有特殊的字符例如单引号|号双引号或者连字符&
例如他的名字是test密码是A|&
这时当你执行以下的查询语句时肯定会报错
SQL = SELECT * FROM SecurityLevel WHERE UID= & UserID &
SQL = SQL & AND PWD= & Password &
因为你的SQL将会是这样
SELECT * FROM SecurityLevel WHERE UID=test AND PWD=A|&
在SQL中|为分割字段用的显然会出错了现在提供下面的几个函数 专门用来处理这些头疼的东西
Function ReplaceStr (TextIn ByVal SearchStr As String _
ByVal Replacement As String _
ByVal CompMode As Integer)
Dim WorkText As String Pointer As Integer
If IsNull(TextIn) Then
ReplaceStr = Null
Else
WorkText = TextIn
Pointer = InStr( WorkText SearchStr CompMode)
Do While Pointer >
WorkText = Left(WorkText Pointer ) & Replacement & _
Mid(WorkText Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement) WorkText SearchStr CompMode)
Loop
ReplaceStr = WorkText
End If
End Function
Function SQLFixup(TextIn)
SQLFixup = ReplaceStr(TextIn )
End Function
Function JetSQLFixup(TextIn)
Dim Temp
Temp = ReplaceStr(TextIn )
JetSQLFixup = ReplaceStr(Temp | & chr() & )
End Function
Function FindFirstFixup(TextIn)
Dim Temp
Temp = ReplaceStr(TextIn & chr() & )
FindFirstFixup = ReplaceStr(Temp | & chr() & )
End Function
有了上面几个函数后当你在执行一个sql前请先使用
SQL = SELECT * FROM SecurityLevel WHERE UID= & SQLFixup(UserID) &
SQL = SQL & AND PWD= & SQLFixup(Password) &
