This function is used in conjunction with the Replace
function to ensure strings used in SQL queries do not contain illegal
characters. Here single quotes and pipes are replaced by their Ascii equivalent.
Also a Boolean flag is passed to the function when pass-through queries
are used. Add the following code to a FileHandling.bas
standard module to compile a File Utility function set:
Public Function SQLText(strText As String, blnSqlPassThru As Boolean)_
As String
'*****************************************************************
'<DESC> Replaces single quotes and pipes with legal characters
' </DESC>
'<RETURN> String:
' Replacement Text</RETURN>
'<ACCESS> Public</ACCESS>
'<ARGS> strText:
' Text to be checked/amended
' blnSqlPassThru:
' Pass Thru Flag</ARGS>
'<USAGE> strSQL = "SELECT * FROM Employees WHERE employee_name ='" _
' & SQLText(txtEmployeeName, False) & "'"
'
'*****************************************************************
Dim txt As String
If blnSqlPassThru Then
txt = Replace(strText , "'", "''")
Else
txt = Replace(strText , "'", "' & chr(39) & '")
txt = Replace(txt, "|", "' & chr(124) & '")
End If
SQLText = "'" & txt & "'"
End Function
|
