Handling Apostrophe(') in SQL Statement 1

[sonper]

Hi guys!

I would like to know how to handle apostrophe in a SQL statement. I'm getting an error when I process the statement below:

tmpfullname = "O'CONNOR, MARLYN VALLE"

strSQL = "Select FullName from BRGY Where Fullname = '" & tmpfullname & "'"

set rsconn = New ADODB.Recordset
rsconn.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText

If tmpfullname has no apostrophe in it, no error is given.

I'm using VB6 and SQL Server 7.

Thanks in advance.

 

[JBats]

hi sonper,

Try this simple function i made, this will fix apostrophe in an expression.

tmpfullname = "O'CONNOR, MARLYN VALLE"

strSQL = "Select FullName from BRGY Where Fullname = " & fixsqlstring(tmpfullname)



Public Function FixSQLString(ByVal strName As Variant) As Variant
Dim strLine As String, strTemp As String
Dim strSLine As String, strStemp As String
Dim DblQuote As Integer, SngQuote As Integer, LetterIdx As Integer

If Trim(strName) = "" Then
Exit Function
End If

strTemp = strName
strLine = ""
DblQuote = InStr(1, strTemp, Chr(34))
SngQuote = InStr(strTemp, Chr(39))

If DblQuote = 0 And SngQuote = 0 Then
FixSQLString = "'" & Trim(strName) & "'"
Exit Function
End If

LetterIdx = 0
While DblQuote > 0
strLine = strLine & Left$(strTemp, DblQuote) & Chr(34)
strTemp = Mid$(strTemp, DblQuote + 1, Len(strTemp) - 1)
DblQuote = InStr(CStr(strTemp), Chr(34))
LetterIdx = LetterIdx + 1
Wend


strStemp = strName

LetterIdx = 1
SngQuote = InStr(strStemp, Chr(39))

If SngQuote = 0 Then
FixSQLString = "'" & Trim(strStemp) & "'"
Exit Function
End If

While SngQuote > 0
strSLine = strSLine & Left$(strStemp, SngQuote) & Chr(39)
strStemp = Mid$(strStemp, SngQuote + 1, Len(strStemp) - 1)
SngQuote = InStr(CStr(strStemp), Chr(39))
Wend

If SngQuote = 0 And strSLine <> &quot;&quot; Then
strSLine = strSLine & strStemp
End If

FixSQLString = &quot;'&quot; & strSLine & &quot;'&quot;

End Function

JBats
Good is not better if not than best...

 

[sonper]

JBats,

Thanks for the code! It really solved my problem.
Regards. God bless!

Percy

 

[vb5prgrmr]

Then there is always the intrinsic replace function...
[tt]
strSQL = &quot;Select FullName from BRGY Where Fullname = '&quot; & Replace(tmpfullname, &quot;'&quot;, &quot;''&quot & &quot;'&quot;
[/tt]

 

[CCLINT]

And you can always use a Command and Parameter object to do this and not have to worry about it at all...
Thread222-549287
Notice what I mentioned in my last post in that thread...

There is an FAQ on why and how to do this.

 

[zemp]

That would be faq709-1526.

Take Care,

zemp

&quot;Show me someone with both feet on the ground and I will show you someone who can't put their pants on.&quot;

 

[sonper]

Thanks guys! You're all great!