Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query - Update Syntax issue with Apostrophe

Posted on 2009-04-09
7
Medium Priority
?
500 Views
Last Modified: 2012-05-06
I am creating a small application for taking notes etc.  However, in my textbox when I use any Apostrophe, the program is giving me error.  

Incorrect syntax near 's'.  Unclosed quotation mark after the character string ')'.  

So any way to get around this?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24111135
us 2 '.  Like if it is

'This is Bob's House'

should be

'This is Bob''s House'
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24111203
how are you creating this sql?
if manually i.e. in code (not bounded forms) then wrap that field with double quotes

eg

dim sSql as string

sSql = "update mytable set myfield = " & chr$(34) & myvalue & chr$(34)

chr$(34) is basically double quotes that u wrap your variable in
0
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 750 total points
ID: 24111295
OK, so you're wanting your values to include the surrounding apostrophes in
the values stored on the database.  IN that case, you need to use *three*
apostrophes.  This is because you want to leave a single apostrophe to tell
the parser that the enclosed value is a string, but also include double
apostrophes to tell the parser to generate a single apostrophe after parsing
the SQL.  I.e.

Insert into table values('''fields''', '''morefields''');

Now this may cause problems, depending on how good the SQL parser you are
using is.  For instance, if it always generates a single apostrophe from the
first double apostrophe it finds, you may end up back where you were with
the two apostrophe example you gave.

There is another, simpler alternative of course.  Use double quotes instead
of apostrophes in your strings.  I.e.

Insert into table values('"fields"', '"morefields"');

It all depends on why you want the apostrophes to be stored in the fields
with the values.  If you're going to use them in processing later when the
data is read, then you can easily do a replace of the double quotes with
apostrophes after the values are read from the database.  Off my head, I
can't think why you would want to enclose values in apostrophes like this,
as the data type of the field should already know they are strings, so
storing them with the data seems a bit redundant.
0
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 750 total points
ID: 24111632
Sorry, I thought I was in the msaccess zone, lol

but with sql, to wrap a string with single quotes, wrap it in double quotes. It sill applies except you would use   "  (shift 2)
0
 

Author Comment

by:holemania
ID: 24111707
Below is how my query looks like.  My richtextbox, I want the user to type in notes and sometimes when they using apostrophe in the note, it throw the error.

My snytax:
Dim strInsert As String = "INSERT INTO NOTE VALUES(" & strNoteID & ", " & txtID.TEXT & ", '" & rtbNote.TEXT & "')"


This part of the syntax '" & rtbNote.TEXT & "' is what is giving me the error.
0
 

Author Comment

by:holemania
ID: 24111853
Okay this seems fix the issue.

Dim strNote as String = Replace(rtbNote.Text, "'", "''")

Dim strInsert As String = "INSERT INTO NOTE VALUES(" & strNoteID & ", " & txtID.TEXT & ", '" & strNote & "')"
0
 

Author Closing Comment

by:holemania
ID: 31568720
Thanks for the help.  Not quite what I was looking for me steer me in the right direction.

Basically replacing ' with double '' seems to fix the issue.
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question