How to handle data with embedded quotes 
Author Message
 How to handle data with embedded quotes

strSQL = "Insert into student (ssn, name) values ('" & ssn & "','" &
strName & "')"

This statement works but crashes when on the 228th record I'm processing
has a single quote mark (') embedded in the name field.

Using SQL Server Query Analyzer the following statement is acceptable:

Insert into student (ssn, name) values ('999999999',"harris's")

So SQL will accepted double quotes (") instead of single quotes (') for
defining a string.  But I can not find a way to format a VB statement to
handle this.

I assume VB has a way to specifying data with single and double quotes,
but I can't find it with MSDN disk or SQL Server Books Online.

Any suggestions?



Fri, 21 Sep 2001 03:00:00 GMT  
 How to handle data with embedded quotes
Jim,

Use the VB6 Replace function to replace one single quote with two single
quotes and so on

strSQL = "Insert into student (ssn, name) values ('" & Replace(ssn, "'",
"''") & "','" & Replace(strName, "'", "''") & "')"

--
HTH
VB-Joker

PLEASE post ALL replies to newsgroup!


Quote:
> strSQL = "Insert into student (ssn, name) values ('" & ssn & "','" &
> strName & "')"

> This statement works but crashes when on the 228th record I'm processing
> has a single quote mark (') embedded in the name field.

> Using SQL Server Query Analyzer the following statement is acceptable:

> Insert into student (ssn, name) values ('999999999',"harris's")

> So SQL will accepted double quotes (") instead of single quotes (') for
> defining a string.  But I can not find a way to format a VB statement to
> handle this.

> I assume VB has a way to specifying data with single and double quotes,
> but I can't find it with MSDN disk or SQL Server Books Online.

> Any suggestions?



Fri, 21 Sep 2001 03:00:00 GMT  
 How to handle data with embedded quotes
Thanks a million!  You don't know how much I was struggling with this.
I'm trying to teach myself VB and SQL Server 7.0 at the same time.  I'm
an old dBASE programmer, so things are much different.

Where did you learn the Replace trick?  I've got bunches of books and
never saw this trick.  I could use a whole book devoted to formatting
SQL commands in VB.  Can you recommend a good book for this topic?

Now old "Cordero de Jesu's" is in my database.  I have never seen an
apostrophe in a person's name before.

Quote:

> Jim,

> Use the VB6 Replace function to replace one single quote with two single
> quotes and so on

> strSQL = "Insert into student (ssn, name) values ('" & Replace(ssn, "'",
> "''") & "','" & Replace(strName, "'", "''") & "')"

> --
> HTH
> VB-Joker

> PLEASE post ALL replies to newsgroup!



> > strSQL = "Insert into student (ssn, name) values ('" & ssn & "','" &
> > strName & "')"

> > This statement works but crashes when on the 228th record I'm processing
> > has a single quote mark (') embedded in the name field.

> > Using SQL Server Query Analyzer the following statement is acceptable:

> > Insert into student (ssn, name) values ('999999999',"harris's")

> > So SQL will accepted double quotes (") instead of single quotes (') for
> > defining a string.  But I can not find a way to format a VB statement to
> > handle this.

> > I assume VB has a way to specifying data with single and double quotes,
> > but I can't find it with MSDN disk or SQL Server Books Online.

> > Any suggestions?



Sat, 22 Sep 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. embedded quotes in data

2. embedded single quotes

3. Wish List: Embedded Quotes in VB.Net

4. Embedding quotes in string var ?

5. Embedding Double Quotes in a String -- How?

6. Help embedding quotes in strings

7. Need to embed single quote in SQL string

8. How to embed double quote in a string?

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

10. Embedded quotes in HTML

11. ASP vbscript - embedded quotes syntax question

12. Single Quote (Apostrophe) in Embedded Query?

 

 
Powered by phpBB® Forum Software