I have a very large SQL statement (Insert - in this case) that negotiates
variables into strings for use within the SQL. My problem is, I use the
single quote to force the negotiation of the variables and sometimes the
variable contents include a single quote (like in a last name -
O'Leary...). How can I get around this???

I am using VB5.0 Enterprise on a Pentuim 200, accessing  MS SQL 6.50.201

TIA...
----------------
Brian G. Burke - Senior Programmer Analyst
COBOL - PowerBuilder - VB 5.0
http://www.*-*-*.com/
----------------------------------------------------------------------------
-------------------------------------------
Programmers are to blame for world hunger, homelessness, and the depletion
of our rain forests...FYI

 I am using VB 5.0 going to Oracle.  Your problem is with embeded quotes
in a string.  When the database server tries to resolve the SQL
statement it sees the embeded qoute as the end of the expression.  You
have to escape the embedded quotes.  

 I dont know the exact syntax in SQL Server, but this is how you do it
in Oracle.

insert into DummyTable
       (DummyColumn)
values ('O''Leary')

Good Luck

Stephen Polito

1)      Use double-quotes
Example: Insert into table1 values ("O'Leary", ...
                                    ^       ^

2)      Use two single-quotes
Example: Insert into table1 values ('O''Leary', ...
                                      ^^

2)      Write a function to replace the occurrence of single quotes into
        two single-quotes

Example: var1 = Replace("O'Leary", "'", "''")
var1 returns --> O''Leary

I may give you the codes for this function, quite an easy one, :-)

Public Function Replace(strIn As String, strOccur As String, strReplWith
As String) As String
    Dim nPos As Integer
    Dim strTemp As String
    Dim strTemp2 As String

    strTemp = strIn
    nPos = 1
    nPos = InStr(nPos, strTemp, strOccur)
    Do While nPos > 0
        strTemp = Mid(strTemp, 1, nPos - 1) & strReplWith _
            & Mid(strTemp, nPos + 1)
        nPos = InStr(nPos + 2, strTemp, strOccur)
    Loop
    Replace = strTemp
End Function

Hope it solves your problem, :-)

you can fool VB by substituting Chr$(34) for the double quote. VB will
translate CHR$(34) into a double quote and you will be able to execute your
statement.

ex.:  SQL="Insert Into Main (lastname) Values(" & Chr$(34) & txtLastName &
Chr$(34) & ")"

Hope that helps
Paul Ross

Here is a function that I use to get around this problem.  It converts the
single quote (or apostrophe) to a double apostrophe, which will be
translated correctly.

'***************************************************************
'* The primary purpose of this function is to take care of names
'* that use the apostrophe, like O'Malley.  If the name does
'* have one, then make it double.
'***************************************************************
Function LookForApostrophes(ByVal TheName$) As String
  Dim i%
  ReDim parts_of_name(1) As String
  Dim num_parts%
  Dim the_name$

  num_parts% = 1
  the_name$ = TheName$
  i% = InStr(the_name$, "'")
  If i% <> 0 Then
    num_parts% = num_parts% + 1
    ReDim Preserve parts_of_name(num_parts%)
    parts_of_name(num_parts% - 1) = Left$(the_name$, i%) & "'"
    parts_of_name(num_parts%) = Mid$(the_name$, i% + 1)
    the_name$ = parts_of_name(num_parts%)

    i% = InStr(the_name$, "'")
    Do Until i% = 0
      num_parts% = num_parts% + 1
      ReDim Preserve parts_of_name(num_parts%)
      parts_of_name(num_parts% - 1) = Left$(the_name$, i%) & "'"
      parts_of_name(num_parts%) = Mid$(the_name$, i% + 1)
      the_name$ = parts_of_name(num_parts%)
      i% = InStr(the_name$, "'")
    Loop

    the_name$ = ""
    For i% = 1 To num_parts%
      the_name$ = the_name$ & parts_of_name(i%)
    Next i%
  End If 'i% <> 0

  LookForApostrophes = the_name$
End Function

If anyone has a better solution, I would be interested in knowing about it.
Thanx,
--
Lance King
BTNA USSEC Atlanta