Re: Help with SQL

From: Bob Duncan <bduncan_at_ksg.harvard.edu>
Date: Fri, 31 Mar 2000 06:38:33 GMT
Message-ID: <JJXE4.76$lY5.3267@news2.randori.com>

Not exactly sure what "BIND VARIABLES" - but its probably Oracle specific. In my world, we have to run against multiple backends so that kind of stuff is out. I wrote a function a while ago to handle this. You just pass in your string value when
building your SQL statement and this will clean it up.

Public Function UnQuote(ByVal OldQuote As String) As String

    '* DESCRIPTION: This function will take care of embedded quotes within a string.

    '* Embedded quotes will cause problems when trying to run a SQL statement.

    '*

    '* Declare variables.
    Dim i As Integer, j As Integer

    '* Process each character looking for a quote.     i = InStr(OldQuote, "'")
    If i Then

        Do
            OldQuote = Left$(OldQuote, i - 1) & "'" & Mid$(OldQuote, i)
            j = i + 2
            i = InStr(j, OldQuote, "'")
        Loop Until i = 0

    End If
    UnQuote = OldQuote
End Function

Sundeep Kumargoswami <skumargo_at_osf1.gmu.edu> wrote in message news:8c04ak$pio_at_portal.gmu.edu...
> Hi,
> I am trying to execute a SQL statement against an oracle database
> the field (customer_name) I am querying on contains records with
> embedded single quotes (') for example, Craig's data exchange. When
> the query hits one of these records it fails with improperly quoted
> string error. The front-end is VB and the connection is made using ODBC
>
> Do While Not .EOF
>
> mySql = "SELECT * FROM all_customer_acct WHERE " & _
> "customer_name = '" & UCase(!customer_nm) & "'"
>
> frmValidator.Text1.Text = UCase(!customer_nm)
> frmValidator.Refresh
>
> outString = UCase(!customer_nm) & "|"
>
> How can I change the Sql Statment so it will not fail in the above
> situation.
>
> Thanks,
> Sundeep
>
> --
> -----------------------------------
> Sundeep Goswami
> skumargo_at_gmu.edu
> goswamis_at_hotmail.com
>
Received on Fri Mar 31 2000 - 00:38:33 CST