SQL query problem

[sunil goyalG]

hi all, i run a query which update a field , is failed when it contains single or double quote.Otherwise it is running successfully. sample query is following update table1 set name='abcd'efg' please suggest how can i solve this problem ?

[nishkarsh_k]

sunil goyalG wrote:

update table1 set name='abcd'efg'

try this update table1 set name='abcd\'efg' ' and \ are excape characters so one has to put "\" before them to send as proper query

[Rajesh Anuhya]

Perfect Query update table1 set name='abcd''efg'

Rajesh B --> A Poor Workman Blames His Tools <--

[sunil goyalG]

This solution is not works for me. query is failed. I am using SQL Server Express 2005

[Dave Kreskowiak]

THe problem is that you are using string concantenation (newString = "SomeString" & "SomeOtherString") to build your SQL statement. Don't. Use parameterized queries instead and this problem is solved for you. Read this[^] article for why you should not be doing what you're doing and how to use parameters.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008

[bigsagal]

Any time you are updating a column which may contain a single quote, you must scan and replace the single quote with two single quotes. If your column contains 5 single quotes, your column will contain 10 single quotes after the scan and replace. During the update, two successive single quotes will be converted back to a single quote. For example, if your column contains 'abc'd''ef' before the scan and replace, your column will contain 'abc''d''''ef' after the scan and replace. After the update, your column in the database will contain your original column value namely 'abc'd''ef'.

bigsagal, confused as all get out!

[Nanda_MR]

use the instant of ' Asc char. It will work i also tried in my system it is working properly . Ananda