the following query throwing error. plz help me & tell me what is the error.
this query returing following result:
string sqlcmd = "UPDATE branch SET brac_subj = " + query_data + " WHERE (braid = "+dd_branch+")";
UPDATE branch SET brac_subj = "'11' , '12' , '13' , '14' , '15' , '16' , '17' , '18' , '19' , '20' , '21'" WHERE (braid = 1)
how can i store the string in following format:
'11' , '12' , '13' , '14' , '15' , '16' , '17' , '18' , '19' , '20' , '21'
The problem you are running into Enigma is called Escaping. Since single quotes is a restricted character, and defines the start of a string in SQL, you can't use it as-is inside that same string.
In addition to that, it looks like you are under the impression that SQL Server uses doubles quotes to define a string, and it does not.
This syntax will not work:
UPDATE branch SET brac_subj = "'11' , '12' , '13'"
This syntax will work:
UPDATE branch SET brac_subj = '''11'' , ''12'' , ''13'''
The trick is using your programming code to add those extra apostrophe's around the values, thus the answers that everyone else is suggesting.
Also, an easy way to test this syntax is a short select statement:
SELECT "'11' , '12' , '13'"
vs
SELECT '''11'' , ''12'' , ''13'''
If you want it as a string. This should work:
query_data="'"+query_data.Replace("'","''")+"'";
If you want them as this "11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21". Then do it like this:
query_data="'"+query_data.Replace("'","")+"'";
replace each apostrophe with two and it should work if you want "duncan o'toole" you use 'duncan o''toole' in sql query
Try this if this is the kind of data you want to insert i.e. without single quotes
UPDATE branch SET brac_subj = '11,12,13' WHERE (braid = 1)
If you want to insert single quote as well replace the single quote with double quotes
UPDATE branch SET brac_subj = '"11","12","13"' WHERE (braid = 1)
The easiest way to do it is to use:
SET QUOTED_IDENTIFIER OFF
UPDATE branch SET brac_subj = "'11' , '12' , '13'"
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Database Identifiers.
