Products

Software

Services

Support

Contact Us

Function to Parse Strings for SQL Statements (VB ASP ASP.NET)

Logoff Screen Saver

Printing Problems

Identifying Memory

Windows Update Problems

Links to Manufacturers

Source Code

The apostrophe ( ' ) sometimes referred to as a single quote can cause SQL statements to fail when posting records to dBase and Paradox tables. If someone's name entered in a text box was Bill O'Rielly, trying to post the text directly to a database using a SQL statement will fail. The reason for the failure is the apostrophe is also used to denote the beginning and end of text in a SQL statement. Adding an additional apostrophe ( '' ) to the name corrects the problem, (i.e. Bill O''Rielly). Since we can not expect the user to know they should enter double quotes instead of the correct spelling of their name in a text field, the best way to fix it is to search the text and add the second apostrophe. The function below is one way of accomplishing this task.

function StrParse(strToFix as String)
 ' If the string is empty, there is no point in searching it
 ' just return the unmodified string.
 if Len(strToFix) < 1 then
  StrParse = strToFix
  exit function
 end if 
 ' If the apostrophe is not in the string return the unmodified
 ' string, otherwise replace all instances of the apostrophe
 ' with a double apostrophe. Then return the modified string.
 if InStr(1, strToFix, "'", 0) = 0 then 
  StrParse = strToFix
 else
  StrParse = Replace$(strToFix, "'", "''")
 end if
end function

This is an example of how I call this function when I am building a SQL statement.

strSQL = "INSERT INTO yourdatabase.db "
strSQL = strSQL & "(Name, Telephone) " 
strSQL = strSQL & "VALUES ('" & StrParse(TextBox1.Text) & "', " 
strSQL = strSQL & "'" & TextBox2.Text & "')"

Note the extra space before the double quotes at the end of each line except the last. They are required so the string for the SQL statement is properly formed.