ARCHIVE: TIPS & TRICKS
PADQ solves SQL-Assignment troubles
Robert Berrios
09.26.2000
Rating: -4.00- (out of 5)
PADQ 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.
Robert, thanks for the great tip for our readers, and to show our
appreciation, we're sending you a free searchVB.com denim shirt. Enjoy!
|
|
|
|
|
|
