Dbl Quotes vs Apostrophe in SQL 2

[SiberBob]

I have found a number of archived threads on using Double Quotes to alleviate the problem created when doing an SQL with data that contains an apostrophe ' in the data.

I can't understand the syntax of using the Double quotes... Can someone give me an example of how the contents of my sql variable should look when I use the Double Quotes method?

IE: if I have LastName "O'Sullivan" and FirstName "Brian",

How would that look if I were to do a

Select * WHERE LastName LIKE "'" & strLastName & "' and '" & FirstName "'"

Should I be using:

Select * WHERE LastName LIKE """" & strLastName & """" & " and '" & FirstName "'"

So I can understand this better would someone please demonstrate to me what the actual value of the SQL statement should be with the variables plugged into it...

 

[PHV]

The safest way:
strSQL = "Select * WHERE LastName LIKE [tt]'" & Replace(strLastName, "'", "''") & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

I agree that PHV's is the safest, it's proven to be full proof for me so far, ever since he showed it to me/us
a long time ago.

just for the record, your 2nd example is quite reliable,
but can be rewritten as,

Select * WHERE LastName LIKE """" & strLastName & """ and """ & FirstName """"

I sometimes use this,

Select * WHERE LastName LIKE " & chr(34) & strLastName & chr(34) & " and " & chr(34) & FirstName & chr(34)

 

[SiberBob]

So you guys are telling me that substituting 2 apostrophe's in lieu of a single apostrophe when it occurs within a string which it to be used as part of an SQL Statement will solve my problem?

Why would [red]LIKE 'O''Sullivan'[/red] work?

 

[ArkM]

SQL string literal -- ASCII text framed by single quotes ('). Within a literal, a single quote is represented by 2 single quotes ('').
From

http://www.firstsql.com/tutor3.htm#literal

 

[SiberBob]

PHV, Zion7, & ArkM:

Thanks to all of you for your help... My problem with other posts was the font used in Zion 7's reply and most other posts is too hard to differentiate between the quotes and the apostrophes and double apostrophes and such. PHV's was a different font and much clearer for me to understand. Why can't we make this entire forum in Courier New?

For anyone else that is having similar problems, here is what my sql statement looks like with the values plugged in...

strSqlStatement = "INSERT INTO Schedule (ScheduleDate, Troop, Zone, Badge, LastName, FirstName, ScheduleType, ScheduleTime) Values (#04/30/06#,'D','08','687','O''SULLIVAN','BRIAN','L','    ')"

and here is the code I used to generate that statement:

strSQLStatement = "INSERT INTO Schedule (ScheduleDate, Troop, Zone, Badge, LastName, FirstName, ScheduleType, ScheduleTime) Values (#" & _
                    strScheduleDate & "#,'" & strTroop & "','" & strZone & "','" & strBadge & "','" & strLastName & "','" & strFirstName & "','" & strScheduleType & "','" & strScheduleTime & "')"

The combined input from all of you was helpful. Thanks!