Problem solve Get help with specific problems with your technologies, process and projects.

PADQ solves SQL-Assignment troubles

This tip introduces PADQ, which helps solve SQL-Assignment troubles.

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!

Dig Deeper on SQL Server and .NET development

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

TheServerSide.com

Close