Solves SQL-Assignment Troubles
Reader Robert Berrios sends in this tip that will help developers get
their syntax straight when they are attempting to assign SQL statements to a string. You'll encounter Robert's problem any time that you're
using the traditional way of doing this and encounter some data value that has unexpected or unusual punctuation.
When I first started using VB, one of my most burdensome tasks was assigning SQL statements to a string, particularly assigning statements
that passed string values to SQL. I never got was it right the first time. It's especially difficult when you encounter values such as
"O'Conner". I eliminated the problem when I discovered a function
called PadQ.
Here's the old fashioned way:
Dim sSql As String
sSql = "Select lastname, firstname, ssn, homeadd, state, zip, tel,hiredate "
sSql = sSql & "Where lastname = '" & sLastName & "'"
sSql = sSql & "And firstname = '" & sFirstName & "'"
sSql = sSql & "And ssn = '" & sSSN & "'"
sSql = sSql & "And homeadd = '" & sHomeAdd & "'"
sSql = sSql & "And state = '" & sState & "'"
sSql = sSql & "And zip = '" & sZip & "'"
sSql = sSql & "And tel = '" & sTel & "'"
sSql = sSql & "And hiredate = '" & sHireDate & "'"
Here's the new way with PadQ:
Dim sSql As String
sSql = "Select lastname, firstname, ssn, homeadd, state, zip, tel,
hiredate"
sSql = sSql & " Where lastname = " & PadQ(sLastName)
sSql = sSql & " And firstname = " & PadQ(sFirstName)
sSql = sSql & " And ssn = " & PadQ(sSSN)
sSql = sSql & " And homeadd = " & PadQ(sHomeAdd)
sSql = sSql & " And state = " & PadQ(sState)
sSql = sSql & " And zip = " & PadQ(sZip)
sSql = sSql & " And tel = " & PadQ(sTel)
sSql = sSql & " And hiredate = " & PadQ(sHireDate)
Public Function PadQ(ByVal sStringPassed As String) As String
Dim sNewString As String
Dim iPos As Integer
On Error GoTo PadQ_Error
sNewString = Trim$(sStringPassed)
iPos = InStr(1, sNewString, chr(39))
Do While iPos > 0
sNewString = Mid$(sNewString, 1, iPos) & chr(39) &
Mid$(sNewString, iPos + 1)
iPos = InStr(iPos + 2, sNewString, chr(39))
Loop
PadQ = chr(39) & sNewString & chr(39)
Exit Function
PadQ_Error:
'Error code.....................
PadQ = ""
End Function
If you use PadQ you won't need to remember where those quotes belong when you're passing string types to SQL. So it cuts down on your
development time, especially when you have an "enormous" SQL statement stretching from here to Wisconsin. And it handles names with quote
marks in them, such as O'Hara.
