The dreaded apostrophe & sql

[Meyer1228]

You all helped me with function keys last week. Thanks so much; your advice
was right on.

My question now - does anyone have a function that I can use to test my sql
statements for the apostrophe and quote and do a search/replace type thing.
I'd like something to put in my basFunctions module instead of replacing
every time I do a sql command in my program code. Thanks!

[arko]

Changing apostophe and quote into what?

Let's say change all a's into b's:

sql$=replace(sq l$,"a","b")

Cheers
Arjen
"Meyer1228" <ka***@ecsnj.co m> schreef in bericht
news:HQ******** **********@nwrd dc02.gnilink.ne t...

You all helped me with function keys last week. Thanks so much; your advice was right on.

My question now - does anyone have a function that I can use to test my sql statements for the apostrophe and quote and do a search/replace type thing. I'd like something to put in my basFunctions module instead of replacing
every time I do a sql command in my program code. Thanks!

[alltel news]

Try this:

Public Function FixImbeddedQuot es(strInput As String, Optional strQuoteChar
As String = "'") As String
On Error GoTo ErrorHandler
Dim lsWork As String

FixImbeddedQuot es = strInput

lsWork = strInput
If Len(lsWork) > 0 Then
Do While InStr(lsWork, strQuoteChar) > 0
lsWork = Replace(lsWork, strQuoteChar, Chr$(&HFF))
Loop

Do While InStr(lsWork, Chr$(&HFF)) > 0
lsWork = Replace(lsWork, Chr$(&HFF), strQuoteChar &
strQuoteChar)
Loop

End If

FixImbeddedQuot es = lsWork

FixImbeddedQuot es_Done:
On Error Resume Next
Exit Function

ErrorHandler:
Dim lngErrNum As Long: Dim strErrDesc As String: lngErrNum = Err.Number:
strErrDesc = Err.Description
MsgBox "Err#" & Err.Number & vbCrLf & vbCrLf & "Desc: " &
Err.Description , vbExclamation, "FixImbeddedQuo tes"
Resume FixImbeddedQuot es_Done
Resume ' for debug
End Function

[The Grim Reaper]

My standard "MakeSQLStr ing" function has been the same for many years...
(although recently upgraded to .NET and can probably be improved on... lol)

Public Function MakeSQLString(p StringToBeSQLd As String) As String
' Define apostrophe from good ol' ASCII number
Dim vApostrophe As Char = Convert.ToChar( 39)
' Double up any apostrophes
Dim vOutput As String = pStringToBeSQLd .Replace(vApost rophe, vApostrophe &
vApostrophe)
' Add apostrophes to each end of the string
Return vApostrophe & vOutput & vApostrophe
End Function

Usage;

Dim vName As String
Dim vSQL As String
vName = "Grim"
vSQL = "SELECT * FROM myTable WHERE myWhereClause=" & MakeSQLString(v Name) &
";"
..... <query DB>
_______________ _______________ ___
The Grim Reaper

"Meyer1228" <ka***@ecsnj.co m> wrote in message
news:HQ******** **********@nwrd dc02.gnilink.ne t...

You all helped me with function keys last week. Thanks so much; your advice was right on.

My question now - does anyone have a function that I can use to test my sql statements for the apostrophe and quote and do a search/replace type thing. I'd like something to put in my basFunctions module instead of replacing
every time I do a sql command in my program code. Thanks!

[The Grim Reaper]

My apologies!!! Too busy watching TV, I didn't realise I was in the VB
group, not the .NET one I'm usually in!!
Here's a "translation".. .

Public Function MakeSQLString(p StringToBeSQLd As String) As String
' Define apostrophe from good ol' ASCII number
Dim vApostrophe As String = Chr(39)
' Double up any apostrophes
Dim vOutput As String = Replace(pString ToBeSQLd, vApostrophe, vApostrophe
& vApostrophe)
' Add apostrophes to each end of the string
MakeSQLString = vApostrophe & vOutput & vApostrophe
End Function

Usage;

Dim vName As String
Dim vSQL As String
vName = "Grim"
vSQL = "SELECT * FROM myTable WHERE myWhereClause=" & MakeSQLString(v Name) &
";"
..... <query DB>
_______________ _______________ ___
The Grim Reaper
"The Grim Reaper" <gr*********@bt openworld.com> wrote in message
news:ca******** **@titan.btinte rnet.com...

My standard "MakeSQLStr ing" function has been the same for many years...
(although recently upgraded to .NET and can probably be improved on... lol)
Public Function MakeSQLString(p StringToBeSQLd As String) As String
' Define apostrophe from good ol' ASCII number
Dim vApostrophe As Char = Convert.ToChar( 39)
' Double up any apostrophes
Dim vOutput As String = pStringToBeSQLd .Replace(vApost rophe, vApostrophe & vApostrophe)
' Add apostrophes to each end of the string
Return vApostrophe & vOutput & vApostrophe
End Function

Usage;

Dim vName As String
Dim vSQL As String
vName = "Grim"
vSQL = "SELECT * FROM myTable WHERE myWhereClause=" & MakeSQLString(v Name) & ";"
.... <query DB>
_______________ _______________ ___
The Grim Reaper

"Meyer1228" <ka***@ecsnj.co m> wrote in message
news:HQ******** **********@nwrd dc02.gnilink.ne t...

You all helped me with function keys last week. Thanks so much; your

advice

was right on.

My question now - does anyone have a function that I can use to test my

sql

statements for the apostrophe and quote and do a search/replace type

thing.

I'd like something to put in my basFunctions module instead of replacing
every time I do a sql command in my program code. Thanks!