asked on

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?

Nathan Riley

us 2 '. Like if it is

'This is Bob's House'

should be

'This is Bob''s House'

rockiroads

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

ASKER CERTIFIED SOLUTION

Sreedhar Vengala

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

holemania

ASKER

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.

holemania

ASKER

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 & "')"

holemania

ASKER

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.