Utilities for Creating Valid SQL Statements
Even though you may have data access routines neatly encapsulated as functions/sub-routines, you still need help generating valid SQL statements. For this purpose, there are a few data massaging routines I use. For example, a select statement that returns values from a customer's table for a specific customer:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_NAME = 'ALPHAGRAPHICS'

You normally create this SQL statement by concatenating more than one string. This is because the variable value above (Alphagraphics) is usually in a string variable, and you need to create the entire SQL string before calling your data access function. If the name within a string variable is called strName, your code would look like this:

StrSQL = "SELECT * FROM CUSTOMERS " & _
"WHERE CUSTOMER_NAME = '" & _
strName & _
"'"

Notice how carefully we have constructed the SQL statement by adding a single quote around the name value. The result is a valid SQL statement that will work with most databases including Access, SQL Server, Oracle, etc. This will work fine for almost all names, except those that themselves contain a single quote in them. For example, if the name were "Alphagraphics," the above code would result in the VALID SQL Statement:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_NAME = 'ALPHAGRAPHICS'

And if the name were "O'Brien's Machine Shop," the above code would result in an INVALID SQL statement:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_NAME = 'O'Brien's Machine Shop'

To prevent this, make sure that any single quote (') within the name variable is replaced by two consecutive single quotes (''). This results in a VALID SQL statement:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_NAME = 'O''Brien''s Machine Shop'

To automatically convert every string into a valid SQL-specific string value neatly surrounded by single quotes, I use a function called "CheckString." No surprise here, it checks to make sure the string value is suitable for use in a SQL statement. Here is the code:

FUNCTION CheckString (byval s, byval strDelimiter)
	' -- takes care of quotes in strings send to SQL Server
	' -- also handles Null values
	if Trim(s) = ""  Then
		CheckString = " Null" & strDelimiter
	Else
	    CheckString="'" & Replace(s,"'","''") & "'" & strDelimiter
	end if 
END FUNCTION

As you can see, this function first checks to make sure there is a valid value within the string "s." If it is blank, it returns a Null value. If not, it returns the value enclosed in single quotes and also makes sure that all single quotes within the string are replaced by two consecutive single quotes. It does this by using the VBScript "Replace" function.

StrSQL = "SELECT * FROM CUSTOMERS " & _
"WHERE CUSTOMER_NAME = " & _
CheckString(strName ,"")

The CheckString function accepts a second parameter (strDelimiter) that will come in handy when you are building a SQL statement with a comma-separated list of values. For instance, if you are calling the stored procedure "uspAddNew" that required four parameters (name, address, city, and state), the actual SQL Statement to execute would be of the format:

UspAddNew 'name value', 'address value', 'city value', 'state value'

Assuming we had the individual parameter values within variables strName, strAddress, strCity, and strState respectively, we could use our CheckString function to build a comma-separated list:

StrSQL = "uspAddNew " & _
CheckString(strName ,",") & _
CheckString(strAddress ,",") & _
CheckString(strCity ,",") & _
CheckString(strState ,"")

Notice that we pass a comma as the "strDelimiter" value for all but the last call to CheckString. This results in a VALID SQL statement that calls a stored procedure.

Like the CheckString function, I also use other data massaging functions for dates, numbers, and bit values. I have included the code for these routines in the download for this article. They are called CheckDate, CheckNumber, and CheckBit.

Some Finishing Touches
To put all of these routines together I place all this code within a single ASP page called "IncADORoutines.asp" and then "include" it in any other ASP page that needs these routines. To include this page in another, use the "#include" statement as follows:

<!--#include file="IncADORoutines.asp" -->

Since this file is a "library" of routines, I modify my include statement to refer to the correct directory and place it with other "library" routine files within a /lib directory on my Web sites.

I certainly hope you find these routines useful. You can download the completed "IncAdoRoutines.asp" pages here.

Rama Ramachandran is Vice President of Technology with Imperium Solutions and is a Microsoft Certified Solution Developer and Site Builder. He has extensive experience with building database systems and has co-authored several books including Professional Visual InterDev 6 Programming and Professional Data Access (Wrox). Rama also teaches Visual Basic and Web Development at Fairfield University and University of Connecticut. Reach Rama at rama@imperium.com.

The GetRecordSet Alternative

Introduction	Utilities for Creating Valid SQL Statements
The GetRecordSet Alternative