| When building an sql statement that contains text fields it usually happens that one or more fields ends up containing a single or double quotation mark. This can cause the statement to fail. For example, consider: strSQL="Select CustName,CustID From CustTable Where CustName = '" & strName & "'" If the strName variable contains plain text like "Acme" then the statement passed to the database engine reads "...CustName = 'Acme'" and it works fine. If the customer name is "John's Market" the database engine sees "...CustName = 'John's Market' " and has no idea how to handle the stuff after the closing quote it sees after "John". There are three ways around this: 1. Translate all single quotes to some other character and/or disallow them 2. Use double quotes instead of single quotes. The chance of a text field containing double quotes is usually pretty slim and they can be changed or disallowed if necessary 3. Double the single quotes in the field -- the data base engine should correctly recognize doubled quotes as indicating a single quote in the data and store it as such. The following function takes a text field and checks it for quotes. If no single quotes are found it returns it surrounded by single quotes. If any single quotes are found but no double quotes it surrounds it with double quotes. If both characters exist it doubles the single quotes and uses single quotes around it: Function QuotedText (ByVal strText As String) As String Dim strOut As String Dim x As Integer If InStr(strText, "'") = 0 Then ' no single quote found -- use them strOut = "'" & strText & "'" ElseIf InStr(strText, Chr$(34)) = 0 Then ' single but no double -- use doubles strOut = Chr$(34) & strText & Chr$(34) Else ' double-up single quotes x = InStr(strText, "'") Do While x strOut = Left$(strText, x) & "'" & Mid$(strText, x + 1) strText = strOut x = InStr(x + 2, strText, "'") Loop strOut = "'" & strText & "'" End If ' the DB engine also dislikes pipe characters so I lose them... x=Instr(strText,"|") Do While x>0 Mid$(strOut,x,1)="!" x=Instr(strOut,"|") Loop QuotedText = strOut End Function To use it, do something like: strSQL="Select CustName,CustID From CustTable Where CustName = " & QuotedText(strName) strName output Acme 'Acme' John's Market "John's Market" Jay's "Bistro" 'Jay''s "Bistro"' |
Dealing With Quotes in Text Fields |
Freelance ASP PHP web development | Web developer India Web development India| Prayagasoft - web designer India, Ecommerce developer india, Ecommerce design