• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

SQL Query - Update Syntax issue with Apostrophe

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
holemania
Asked:
holemania
2 Solutions
 
Nathan RileyFounderCommented:
us 2 '.  Like if it is

'This is Bob's House'

should be

'This is Bob''s House'
0
 
rockiroadsCommented:
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
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
rockiroadsCommented:
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
 
holemaniaAuthor Commented:
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
 
holemaniaAuthor Commented:
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
 
holemaniaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now