The dreaded apostrophe & sql

Re: The dreaded apostrophe & sql

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" <[email protected] m> schreef in bericht
news:HQhzc.1863 2$H65.6357@nwrd dc02.gnilink.ne t...[color=blue]
> You all helped me with function keys last week. Thanks so much; your[/color]
advice[color=blue]
> was right on.
>
> My question now - does anyone have a function that I can use to test my[/color]
sql[color=blue]
> statements for the apostrophe and quote and do a search/replace type[/color]
thing.[color=blue]
> 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!
>
>
>[/color]

Re: The dreaded apostrophe &amp; sql

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

Re: The dreaded apostrophe &amp; sql

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" <[email protected] m> wrote in message
news:HQhzc.1863 2$H65.6357@nwrd dc02.gnilink.ne t...[color=blue]
> You all helped me with function keys last week. Thanks so much; your[/color]
advice[color=blue]
> was right on.
>
> My question now - does anyone have a function that I can use to test my[/color]
sql[color=blue]
> statements for the apostrophe and quote and do a search/replace type[/color]
thing.[color=blue]
> 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!
>
>
>[/color]

Re: The dreaded apostrophe &amp; sql

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" <grim_reaper@bt openworld.com> wrote in message
news:canjjf$jlj [email protected] rnet.com...[color=blue]
> My standard "MakeSQLStr ing" function has been the same for many years...
> (although recently upgraded to .NET and can probably be improved on...[/color]
lol)[color=blue]
>
> 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[/color]
&[color=blue]
> 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)[/color]
&[color=blue]
> ";"
> .... <query DB>
> _______________ _______________ ___
> The Grim Reaper
>
> "Meyer1228" <[email protected] m> wrote in message
> news:HQhzc.1863 2$H65.6357@nwrd dc02.gnilink.ne t...[color=green]
> > You all helped me with function keys last week. Thanks so much; your[/color]
> advice[color=green]
> > was right on.
> >
> > My question now - does anyone have a function that I can use to test my[/color]
> sql[color=green]
> > statements for the apostrophe and quote and do a search/replace type[/color]
> thing.[color=green]
> > 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!
> >
> >
> >[/color]
>
>[/color]