Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2005, 05:51 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default Problem with single quotes '

My SQL DoCmd hiccupped when ever I get a single quote. How do I make sure no single quotes get into name fields, as well as all control characters, and potentially damaging SQL code.

Thanks,
Marc Noon

Marc Noon

Coding is a game. Live it, love it, break it, run it.
__________________
Marc Noon

Coding is a game. Live it, love it, break it, run it.
 
Old March 15th, 2005, 05:57 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Marc,
I've had this problem before. The way I get around it is:
Dim strquote as string
strquote = """"
inside your sql statement wrap this around your text field. It will then allow single quotes within the text field.

i.e. field ComputerName Marc's Computer

sql = "insert into xxx (Computername) select " & strquote & computername & strquote & " as expr1;"

Hope this helps!

Kevin


dartcoach
 
Old March 15th, 2005, 06:41 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Marc,

Did that work for you?

Kevin

dartcoach
 
Old March 15th, 2005, 08:27 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default

I Don't under stand how it works. Is this a trick? Or is there a reason why this method is used. I have had that exact problem with control characters in sql for computer name because some admin named their computer and included a dash "-". I just don't understand how it works. I guess I need to press the little magic I believe button?

Marc Noon

Coding is a game. Live it, love it, break it, run it.
 
Old March 15th, 2005, 09:08 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Marc,
When you're inserting a text value that has special characters, those characters could mean something to VB or Access.

i.e. a dash - is also a minus sign.
     a tic mark ' encloses text

so when you have a text field that has 1 or more of these characters, it can cause havok with your sql unless the complete text field is enclosed in double quotes. There is probably a better way to do it than the way I've done it, but mine works for me. I just make sure that everytime I'm inserting a text field, I enclose it with the double quote.

Hope this helps!

Kevin

dartcoach
 
Old March 15th, 2005, 11:08 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default

Yeah I understand that... but what you showed me is not double quotes... its quadruple quotes.

Marc Noon

Coding is a game. Live it, love it, break it, run it.
 
Old March 15th, 2005, 11:36 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Marc,
Your right, sorry about that. I can't remember if I found an example somewhere or I found it by trial and error. I thinks its because when you code strquote = """" it's actual value is "". Then that brackets your text within a single double quote. I think I'm confusing myself now! I just know that it makes it easy and it works!

Kevin

dartcoach
 
Old March 16th, 2005, 03:44 PM
Authorized User
 
Join Date: Jan 2005
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to mnoon Send a message via MSN to mnoon Send a message via Yahoo to mnoon
Default

I think you were right to begin with...

http://www.accessmonster.com/Uwe/For...String-problem

Marc Noon

Coding is a game. Live it, love it, break it, run it.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Please explain single and double quotes rsgracey BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 4 June 8th, 2006 07:20 PM
displaying single quotes and double quotes ren_123 Classic ASP Databases 2 February 22nd, 2006 02:17 PM
Single Quotes in MySQL fields Dave Brown Beginning PHP 1 June 10th, 2005 06:49 AM
Problem with " ' " (single quotes) Varg_88 Classic ASP Databases 2 September 3rd, 2004 08:50 AM
Double Quotes and Single Quotes Problem phungleon Classic ASP Basics 7 May 27th, 2004 01:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.