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

More on Generating SQL Queries

This tip provides more help for generating SQL queries.

More on Generating SQL Queries

Our tip last week on assigning SQL statements in a VB program generated a volley of responses. Most readers said they had a better way to handle the problem, and proceeded to show us with voluminous code snippets. Some of these solutions included using the VB 6 Replace function, while some used their own code. One of our correspondents just said they had a solution to a problem the original tip left unresolved.

Here's that tip, from Joe Scharf.

Dates in SQL Statements
Joe Scharf

The following code solves the problems for dates in SQL statements. I note that Robert Berrios's solution using PadQ may fail for dates in different locales. This function is used in identical fashion to PadQ and it will work for all locales.

' =============================================================
' SQLDateLiteral
' This routine returns a date in a format suitable for a
' SQL statement.
' =============================================================
Public Function SQLDateLiteral(ByVal dtm As Date) As String
SQLDateLiteral = "#" & Format$(dtm, "mm/dd/yyyy") & "#"
End Function

Reader Charles Wood was perhaps the most succinct of the correspondents who commented on our PadQ tip. He uses the Replace function, but rather than cite advantages and disadvantages, rather than discourse on the utility of the original tip and the efficiency of the original code, Charles simply said...
Fix SQLQuotes
Charles Wood

Here's a simpler way...

Public Function FixSQLQuotes(ByVal sStringPassed As String) As String
On error go to Data_Error
sStringPassed=replace(sStringPassed,"''", "<~~>") 'Save all ''

sStringPassed=replace(sStringPassed,"'", "''") 'Convert
Single Tick to Double
sStringPassed=replace(sStringPassed,"<~~>","''") 'Restore

FixSQLQuotes = chr(39) & sStringPassed & chr(39)
Exit Function
Data_Error:
'Error code.....................
FixSQLQuotes = sStringPassed
End Function
Almost as succinct, however, was Michael Adams. He makes no claims for simplicity nor for efficiency, although these are certainly implied in the language with which he gently informs us of his improvement on PadQ tip. He's one of several readers who suggest using the Replace function. But Mike goes us one better, offering that his solution can be incorporated into PadQ to simplify that function.

Here's what he wrote...

Using the Replace Function
Michael Adams

Of course you could just use the replace function.

Dim sSql As String
sSql = "Select lastname, firstname, ssn, homeadd, state, zip, tel,
hiredate"
sSql = sSql & " Where lastname = " & Replace(sLastName,"'","''") & "'"
sSql = sSql & " And firstname = " & Replace(sFirstName,"'","''") & "'"
sSql = sSql & " And ssn = " & Replace(sSSN,"'","''") & "'"
sSql = sSql & " And homeadd = " & Replace(sHomeAdd,"'","''") & "'"
sSql = sSql & " And state = " & Replace(sState,"'","''") & "'"
sSql = sSql & " And zip = " & Replace(sZip,"'","''") & "'"
sSql = sSql & " And tel = " & Replace(sTel,"'","''") & "'"
sSql = sSql & " And hiredate = " & Replace(sHireDate,"'","''") & "'"

Or do it in PadQ() for easier reading.

Public Function PadQ(ByVal sStringPassed As String) As String
PadQ = "'" & Replace(sStringPassed,"'","''") & "'"
end function

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)


Jeff Schnarel writes in somewhat less than flattering tones about PadQ. His point is that the code in PadQ isn't as clean as he'd like, and that it fails to take advantage of VB's internal string-handling capabilities.

STR2FLD:
Jeff Schnarel
Many times I've seen programmers create loops and other algorithms to parse strings to prepare them for insertion into a VB String being used for an SQL statement. I'm always surprised that no one wants to take advantage of VB's internal String Manipulation capabilities. They're faster and leave cleaner code.

Place this in your Utility Code Module:
Public Enum utFieldSuffix
utQuotesComma = 1
utQuotesOnly = 2
End Enum
Public Function Str2Field( _
x_InString As String, _
Optional AddQuote As utFieldSuffix = 1) As String
Str2Field = Replace(x_InString, "'", "''") 'All the works done here in one call - fast and clean!

If AddQuote = 2 Then
Str2Field = " '" & Str2Field & "', "
Else
Str2Field = " '" & Str2Field & "' "
End If
End Function

Reader Barry Ortlip is another fan of the VB6 Replace function. His tip provides information on this very handy function that make the SQL assignment problem go away with less effort and angst than the method used in PadQ. Here's what Barry had to say.

Using VB6's Replace Function
Barry Ortlip

I just thought I'd point out that with VB 6, there is a built-in function that simplifies/replaces the PADQ function in the original message.

Where I work, we have about 3 or 4 different versions of PADQ. Various developers wrote them at different times, because they (and I) did not know about any of the other versions out there in our code (Our app spans about 100k lines of code). Anyway, back to my point: With VB 6, there is a Replace function that can be used in place of PADQ. I do not recall the exact syntax [VB's terrific ide interface has me spoilt :) ]. However, it goes something like:

function Replace (byref sStringToChange as String, _
byref sFindString as String, _
byref sReplaceString as String) as Stringg

Editor's Note: For more information about the Replace function, including syntax, click over to http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vafctreplace.htm

Every occurance of sFindString in sStringToChange is replaced with sReplaceString.
So the Replace function can take the place of PADQ by calling Replace(STRING, "'", "''").

This takes the complexity out of the PADQ function, which, while rather simple, is an order of magnitude more complex than the equivalent Replace function and prone to errors.

I have been replacing our homegrown versions of PADQ as I come across them during code maintenance. I simply take any PADQ functions that I find, comment out all of the body code, and replace the body code with
Replace(sStringToChange, "'", "''").
Performance tests (my own non-scientific using the Now() function) indicate that the Replace() function is about 4X faster than the PADQ code, which may or not matter depending on the app. The speed improvement was not noticeable in our app.

Thanks to all our contributors. We will be sending each of them a denim shirt as a token of our appreciation. If you have a better way of building a SQL query that includes quotations or other off-the-wall characters, send it along. We'll be happy to take a look at it.

Dig Deeper on Win Development Resources

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

TheServerSide.com

Close