Need to embed single quote in SQL string 
Author Message
 Need to embed single quote in SQL string

I need to create a SQL select statement that includes an embedded single quote
like this:

  Dim field_text$ As String
  field_text$ = "I don't know"
  mySQL = "select * from myData where field1 = '" + field_text$ + "'"

I get an error in the SQL statement when I run this. can anybody point me in
the right direction?

Thanks,
Steve



Sat, 23 Aug 1997 08:32:14 GMT  
 Need to embed single quote in SQL string

Quote:

>I need to create a SQL select statement that includes an embedded single quote
>like this:
>  Dim field_text$ As String
>  field_text$ = "I don't know"
>  mySQL = "select * from myData where field1 = '" + field_text$ + "'"
>I get an error in the SQL statement when I run this. can anybody point me in
>the right direction?

Oh yeah...I remember the problems I used to have with Irish names.  Whee.
To get the server to interpret it as a literal single quote rather than
the end of a string, you have to double it up--so "don't" would become
"don''t".  You'll need to write code to insert the extra quote into any
string where this might apply.
--



Mon, 25 Aug 1997 01:19:46 GMT  
 Need to embed single quote in SQL string

Quote:


>>I need to create a SQL select statement that includes an embedded single quote
>>like this:

>>  Dim field_text$ As String
>>  field_text$ = "I don't know"
>>  mySQL = "select * from myData where field1 = '" + field_text$ + "'"

>>I get an error in the SQL statement when I run this. can anybody point me in
>>the right direction?

You could also substitute Chr$(34) for double-quotes and Chr$(I've forgotten!)
for single quotes.

The previous answer works - so if it ain't broke, don't fix it!

Darren Aitcheson (soon to be MCP - I hope!),
Kainos Software Ltd.,
Belfast, N.Ireland.



Sun, 31 Aug 1997 03:14:14 GMT  
 Need to embed single quote in SQL string

Quote:



> >>I need to create a SQL select statement that includes an embedded single quote
> >>like this:

> >>  Dim field_text$ As String
> >>  field_text$ = "I don't know"
> >>  mySQL = "select * from myData where field1 = '" + field_text$ + "'"

> >>I get an error in the SQL statement when I run this. can anybody point me in
> >>the right direction?

> You could also substitute Chr$(34) for double-quotes and Chr$(I've forgotten!)
> for single quotes.

> The previous answer works - so if it ain't broke, don't fix it!

> Darren Aitcheson (soon to be MCP - I hope!),
> Kainos Software Ltd.,
> Belfast, N.Ireland.

Here is a function that accepts a string with any number of
single quote marks and returns the string with the single quote
marks appropriately doubled up for SQL.

Function DataBase_CheckQuote (TextIn As String) As String
   Dim TextOut As String
   Dim Position As Integer
   Position = InStr(1, TextIn, "'")
   If Position = 0 Then
      TextOut = TextIn
   ElseIf Position = Len(TextIn) Then
      TextOut = TextOut & "'"
   Else
      TextOut = Mid$(TextIn, 1, Position) & "'" & DataBase_CheckQuote(Right$(TextIn, Len(TextIn) - Position))
   End If
   DataBase_CheckQuote = TextOut
End Function

Change your code as follows.

Dim field_text$ As String
field_text$ = "I don't know"
mySQL = "select * from myData where field1 = '" + Database_CheckQuote(field_text$) + "'"

and your SQL statement will work.

Good Luck
 Kyp



Mon, 01 Sep 1997 01:35:28 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. How to embed both single and double quotes in strings

2. Single quotes in SQL strings

3. single quotes in SQL Query Strings

4. SQL Server, Strings, and Single Quotes

5. Single quotes in SQL string?

6. Single Quotes in SQL string

7. Single/Double quote for string field in SQL

8. Handling Apostrophies or Single Quotes in a String/SQL

9. Single/Double quote for string field in SQL

10. How to include double quote and single quote in SQL statement

11. Single Quotes converted to double Single Quotes

12. embedded single quotes

 

 
Powered by phpBB® Forum Software