The following tutorial is taken from the internet when I started working with my Library System using MS Access.
I googled this topic to find the author but I can't find him.
This tutorial will handle a text field that contain a value with apostrophe, single or double quotation.
I added it here so other programmer will benefit from this code. You can use this in visual basic or MS Access program.
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"'
Hellow Jomar, how are you? I hope, you are fine. I saw your Billing System. I like very much. Your coding sytle very unique than others. Designing also beautiful and natural.
I am begginer in Visual Basic 6.0. I need source code of School fee billing system and Quiz Contest software in Visual Basic 6.0. Please help me. I am Deepak Dhingal from Nepal.
See you soon. Take Care Bye.
My e-mail: [email protected]