SQL_Boolean Function:
Prepare booleans for SQL query.
Description:
Prepare booleans for SQL according to database type (MSAccess, MSSQL, MySQL, Oracle), set a default value if expression is null.
This is useful to input the correct boolean in the SQL statement, -1 for MSAccess means True either 1 for MSSQL and MySQL and Oracle.
Syntax:
boolean = SQL_Boolean(expression, defaultvalue, databasetype)
Details:
Arguments:
expression:
"yes", "on", "true", "True", "-1", "1" for True
"no", "off", "false", "False", "0" for False
databasetype:
MSAccess
MSSQL
MySQL
Oracle
Example:
<%
dim a
a = SQL_Boolean("on", 0, "MSAccess")
'--- returns -1
a = SQL_Boolean("yes", 0, "MySQL")
'--- returns 1
a = SQL_Boolean("yes", 0, "MSSQL")
'--- returns 1
a = SQL_Boolean("yes", 0, "Oracle")
'--- returns 1
%>
ASP Source Code:
<%
Public Function SQL_boolean(ByVal abExpression, ByRef abDefault, ByRef DbType)
'--- Declare Database Constants
Dim lbTrue, lbFalse
If LCASE(dbType) = "msaccess" Then
lbTRUE = -1 '--- (-1 = MSAccess)
Else
lbTRUE = 1 '--- (1 = SQL)
End if
lbFALSE = 0
Dim lbResult '--- Result to be passed back
'--- Prepare for any errors that may occur
On Error Resume Next
'--- If expression not provided
If abExpression = "" Then
'--- Set expression to default value
abExpression = abDefault
End If '--- abExpression = ""
'--- Attempt to convert expression
lbResult = CBool(abExpression)
'--- If Err Occured
If Err Then
'--- Clear the error
Err.Clear
'--- Determine action based on Expression
Select Case LCase(abExpression)
'--- True expressions
Case "yes", "on", "true", "True", "-1", "1"
lbResult = True
'--- False expressions
Case "no", "off", "false", "False", "0"
lbResult = False
'--- Unknown expression
Case Else
lbResult = abDefault
End Select '--- LCase(abExpression)
End If ' Err
'--- If result is True
If lbResult Then
'--- Return True
SQL_boolean = lbTRUE
'--- Else Result is false
Else
'--- Return False
SQL_boolean = lbFALSE
End If '--- lbResult
End Function '--- SQL_boolean
%>
|
