Escaping international (unicode) characters in string

Y'all:

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:

String str = "This is a\u1245 test.";

in Oracle's SQL dialect, it appears that I can accomplish the same thing:

INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a\1245 test.");

I've googled and researched through the MSDN, and haven't discovered a
similar construct in SQL Server. I am already aware of the UNISTR()
function, and the NCHAR() function, but those aren't going to work well if
there are more than a few international characters embedded within a
string.

Does anyone have a better suggestion?

Thanks muchly!
GRB

--
---------------------
Greg R. Broderick usenet200705@.blackholio.dyndns.org
A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:
>
String str = "This is a\u1245 test.";

SELECT @.str = 'This is a' + nchar(1245) + ' test'

Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:

SELECT @.str = 'This is a' + nchar(0x1245) + ' test'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.sewrote in
news:Xns993F9F38ABD05Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

>I am needing some way, in the SQL Server dialect of SQL, to escape
>unicode code points that are embedded within an nvarchar string in a
>SQL script, e.g. in Java I can do:
>>
>String str = "This is a\u1245 test.";

>
SELECT @.str = 'This is a' + nchar(1245) + ' test'
>
Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:
>
SELECT @.str = 'This is a' + nchar(0x1245) + ' test'

When there are more than one or two non-US-ASCII characters in the string,
this quickly becomes impractically unwieldy, thus my comment in my original
posting:

-- quote --

I am already aware of the UNISTR() function, and the NCHAR() function, but
those aren't going to work well if there are more than a few international
characters embedded within a string.

-- quote --

Thanks anyway, though. :-)

--
---------------------
Greg R. Broderick usenet200705@.blackholio.dyndns.org
A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------|||Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

When there are more than one or two non-US-ASCII characters in the
string, this quickly becomes impractically unwieldy, thus my comment in
my original posting:

If the are in sequence, you could do:

convert(nvarchar, 0x34123512...)

although this is certainly not too funny as you have twitch the bytes
around.

Another solution to use something like Microsoft Visual Keyboard, and
simply put the actual characters there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Escape the '\' character

I noticed that the following character needs escaping with another \ but
only if it is not followed by another character
What is the rule
Are there any other character with that need escaping except ' (single
quote) and wildcard characters when used with LIKE
Thank you,
SamuelA lesser known fact about the Transact-SQL parser is that a backslash ('')
is a continuation character (like the C programming language). When a
backslash is found at the end of a line in a literal string, the backslash
and line terminator characters are ignored. Specifying the additional
backslash isn't technically an escape, it's just another character in the
literal string. For example
SELECT 'test\
ing'
-- result is 'testing'
SELECT 'test\\
ing'
-- result is 'test\ing'
BTW, I first learned of this issue when helping a user who was obfuscating
data. The backslash and newline characters were getting dropped when the
algorithm introduced a backslash at the end of a line. This is yet one
more reason that one should always use parameteritized SQL statements.
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>I noticed that the following character needs escaping with another \ but
>only if it is not followed by another character
> What is the rule
> Are there any other character with that need escaping except ' (single
> quote) and wildcard characters when used with LIKE
> Thank you,
> Samuel
>|||very interesting indeed,
thank you
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>|||So you never need to escape a letter except the single quote and except
wildcard character when using LIKE
What I still don't understand why if I type "A\" & VBCR & "B" I get
A
B
And if I type ' ' as the last character in the line in a multi line Textbox
it will NOT ignore it and I will get
A\
B
Why is that?
Thanks,
Samuel Shulman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>|||> And if I type ' ' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
I would expect that behavior if you using a parameterized SQL Statement.
However, if the SQL statement string is constructed like the example below,
you should get 'AB':
strSql = "INSERT INTO MyTable VALUES('" & _
Request("textBoxValue") & _
"')")
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> So you never need to escape a letter except the single quote and except
> wildcard character when using LIKE
> What I still don't understand why if I type "A\" & VBCR & "B" I get
> A
> B
> And if I type ' ' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
> Why is that?
> Thanks,
> Samuel Shulman
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>|||Can you please define what parameterized statement
Does is matter how the variable is assigned the value?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
> I would expect that behavior if you using a parameterized SQL Statement.
> However, if the SQL statement string is constructed like the example
> below, you should get 'AB':
> strSql = "INSERT INTO MyTable VALUES('" & _
> Request("textBoxValue") & _
> "')")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||> Can you please define what parameterized statement
A parameterized statement contains parameter markers instead of literal
values. Actual parameter values are substituted for parameter markers at
query execution time. Parameterized statements have several advantages,
such as improved security, no need for special quote handling and execution
plan reuse.
Below is an ADO example. ADO.NET has a slightly different object model but
the basic principle is the same and you can use named parameter markers with
the ADO.NET SqlClient provider.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "INSERT INTO MyTable VALUES(?)"
Set textBoxParameter = command.CreateParameter( _
"@.textBoxParameter", adVarchar, adParamInput, 50,
Request("textBoxValue"))
command.Parameters.Append textBoxParameter
Set Rs = command.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OEqmw%23MgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Can you please define what parameterized statement
> Does is matter how the variable is assigned the value?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>sql

Escape the '\' character

I noticed that the following character needs escaping with another \ but
only if it is not followed by another character
What is the rule
Are there any other character with that need escaping except ' (single
quote) and wildcard characters when used with LIKE
Thank you,
SamuelA lesser known fact about the Transact-SQL parser is that a backslash ('\')
is a continuation character (like the C programming language). When a
backslash is found at the end of a line in a literal string, the backslash
and line terminator characters are ignored. Specifying the additional
backslash isn't technically an escape, it's just another character in the
literal string. For example
SELECT 'test\
ing'
-- result is 'testing'
SELECT 'test\\
ing'
-- result is 'test\ing'
BTW, I first learned of this issue when helping a user who was obfuscating
data. The backslash and newline characters were getting dropped when the
algorithm introduced a backslash at the end of a line. This is yet one
more reason that one should always use parameteritized SQL statements.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>I noticed that the following character needs escaping with another \ but
>only if it is not followed by another character
> What is the rule
> Are there any other character with that need escaping except ' (single
> quote) and wildcard characters when used with LIKE
> Thank you,
> Samuel
>|||very interesting indeed,
thank you
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('\')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>|||So you never need to escape a letter except the single quote and except
wildcard character when using LIKE
What I still don't understand why if I type "A\" & VBCR & "B" I get
A
B
And if I type ' \' as the last character in the line in a multi line Textbox
it will NOT ignore it and I will get
A\
B
Why is that?
Thanks,
Samuel Shulman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('\')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>|||> And if I type ' \' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
I would expect that behavior if you using a parameterized SQL Statement.
However, if the SQL statement string is constructed like the example below,
you should get 'AB':
strSql = "INSERT INTO MyTable VALUES('" & _
Request("textBoxValue") & _
"')")
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> So you never need to escape a letter except the single quote and except
> wildcard character when using LIKE
> What I still don't understand why if I type "A\" & VBCR & "B" I get
> A
> B
> And if I type ' \' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
> Why is that?
> Thanks,
> Samuel Shulman
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language). When
>>a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>|||Can you please define what parameterized statement
Does is matter how the variable is assigned the value?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
> I would expect that behavior if you using a parameterized SQL Statement.
> However, if the SQL statement string is constructed like the example
> below, you should get 'AB':
> strSql = "INSERT INTO MyTable VALUES('" & _
> Request("textBoxValue") & _
> "')")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>> So you never need to escape a letter except the single quote and except
>> wildcard character when using LIKE
>> What I still don't understand why if I type "A\" & VBCR & "B" I get
>> A
>> B
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> Why is that?
>> Thanks,
>> Samuel Shulman
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language).
>>When a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>>
>|||> Can you please define what parameterized statement
A parameterized statement contains parameter markers instead of literal
values. Actual parameter values are substituted for parameter markers at
query execution time. Parameterized statements have several advantages,
such as improved security, no need for special quote handling and execution
plan reuse.
Below is an ADO example. ADO.NET has a slightly different object model but
the basic principle is the same and you can use named parameter markers with
the ADO.NET SqlClient provider.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "INSERT INTO MyTable VALUES(?)"
Set textBoxParameter = command.CreateParameter( _
"@.textBoxParameter", adVarchar, adParamInput, 50,
Request("textBoxValue"))
command.Parameters.Append textBoxParameter
Set Rs = command.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OEqmw%23MgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Can you please define what parameterized statement
> Does is matter how the variable is assigned the value?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> I would expect that behavior if you using a parameterized SQL Statement.
>> However, if the SQL statement string is constructed like the example
>> below, you should get 'AB':
>> strSql = "INSERT INTO MyTable VALUES('" & _
>> Request("textBoxValue") & _
>> "')")
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>> So you never need to escape a letter except the single quote and except
>> wildcard character when using LIKE
>> What I still don't understand why if I type "A\" & VBCR & "B" I get
>> A
>> B
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> Why is that?
>> Thanks,
>> Samuel Shulman
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language).
>>When a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \
>>but only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>>
>>
>

Escape special characters in query?

How can I escape the character " in the query designer?
This is my MDX query:
="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt fakturert
timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT Salgspris]}
on 0,
non empty filter([Konsern].members,
len([Konsern].currentmember.Properties("Caption") > 0)) on 1
from [Prosjekt Komplett]
where ([Transaksjonstype].[Alle transaksjonstyper].[" &
Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
My problem is that the work Caption needs a " on each side. My query works
in the MDX Sample Application, but it fails if I use the " in the Report
Designer - and it fails if I don't use them...
Without the "'s:
An error has occured during report processing.
Query execution failed for data set PB_AxpCmpWMB_Project1'
Formula error - syntax error - token is not valid:
"filter([Konsern].members,
len([Konsern].currentmember.Properties(Caption)^)^ > 0)"
With the "'s in Report Designer Data tab:
x:\wiersholm\Fakturarapport konsern.rdl The expression for the query
'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant must
contain exactly one character.
How can I use my query with "s?
All help appreciated!
Kaisa M. LindahlI found out. You escape " with an additional ".
And then I had to change my query again, as the Report Designer didn't like
me using (len).
="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt fakturert
timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT Salgspris]}
on 0,
non empty filter([Konsern].members,
([Konsern].currentmember.Properties(""Caption"") > """")) on 1
from [Prosjekt Komplett]
where ([Transaksjonstype].[Alle transaksjonstyper].[" &
Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
Kaisa
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:eylH2R6$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> How can I escape the character " in the query designer?
> This is my MDX query:
> ="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
> HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt
fakturert
> timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT
Salgspris]}
> on 0,
> non empty filter([Konsern].members,
> len([Konsern].currentmember.Properties("Caption") > 0)) on 1
> from [Prosjekt Komplett]
> where ([Transaksjonstype].[Alle transaksjonstyper].[" &
> Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
> My problem is that the work Caption needs a " on each side. My query works
> in the MDX Sample Application, but it fails if I use the " in the Report
> Designer - and it fails if I don't use them...
> Without the "'s:
> An error has occured during report processing.
> Query execution failed for data set PB_AxpCmpWMB_Project1'
> Formula error - syntax error - token is not valid:
> "filter([Konsern].members,
> len([Konsern].currentmember.Properties(Caption)^)^ > 0)"
> With the "'s in Report Designer Data tab:
> x:\wiersholm\Fakturarapport konsern.rdl The expression for the query
> 'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant
must
> contain exactly one character.
> How can I use my query with "s?
> All help appreciated!
> Kaisa M. Lindahl
>

escape single qoute

Is there a way to escape single quotes ' in a sql statement that uses a sql data source that is automated? I know you can do it with a string manip and replacing them with double single quotes. I am just looking for a simple way.

Thanks

Adam

If you are using parameters, you don't have to worry about single quotes in strings because SQL Server preserves the input data as it is.

If you aren't using parameters, you'll have to use two single quotes.

|||

You need to use two single quotes.

Escape Sequences

Is there an escape sequence available for the SQL Analyzer that will not
mung the `'` (apostrophe)
If I add it as a parameter in my code, it works, but I am curious to know
all the same.I'm not sure what you mean by "that will not mung the `'` (apostrophe)", but
if you pass a string which includes a single quote, you need to escape that
single quote with a single quote. I.e., double each single quote before
passing the string to SQL Server.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:%23ex1p5wvDHA.2444@.TK2MSFTNGP12.phx.gbl...
> Is there an escape sequence available for the SQL Analyzer that will not
> mung the `'` (apostrophe)
> If I add it as a parameter in my code, it works, but I am curious to know
> all the same.
>|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OMO5w8wvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> I'm not sure what you mean by "that will not mung the `'` (apostrophe)",
but
> if you pass a string which includes a single quote, you need to escape
that
> single quote with a single quote. I.e., double each single quote before
> passing the string to SQL Server.
That is exactly what I was looking for.
Thanks.

Escape sequence

Hi,
I have a question with this query -
SELECT * FROM table1 WHERE column1 = 'T_C_%';

This query returns rows where column1 = "T_Care", "T_CRP" etc etc, whereas I was expecting only rows where column1 = "T_C_Tail", "T_C_Head"

However, when I use an escape character(/), the results are more in the lines of the expected results.

Can somebody explain this?
cheers/- PradeepIn a LIKE expression, any underscore (_) matches any one character, and any percent sign (%) matches any group of characters. You can also use brackets ([]) to define sets of characters.

See the BOL description of LIKE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp) for more details.

-PatP|||Thanks Pat, really appreciate your quick response.
-Pradeepsql

escape clause causes error in lookup modified sql statement

I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:

select * from dbo.typecustomer where ? like '%'+type_subtype +'%'
ESCAPE '_'

Is this is a bug? Any help will be greatly appreciated.

Thanks

Akin
The escape clause shouldn't be affecting the parameter usage if the SQL works without it. The lookup doesn't parse the SQL, so I suspect the problem is with the provider. Essentially, the lookup asks the provider to prepare the command and then to derive parameter information. The provider is failing in one of the above steps (probably the prepare step). I would first try this with the latest provider (i.e. use snac instead of sqloledb in the connection manager for the lookup) and if the problem persists, ask on the snac forum if command preparation followed by parameter derivation is problematic with your specific command.

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.
Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
%_*
%_*
John
"Sathyaish" wrote:

> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>
|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegr oups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
--
%_*
%_*
John
"Sathyaish" wrote:
> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegroups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.

escape characters

Hi,

I need to insert this command into a table, but I can't because.

insert XXX

set column1 = isnull(Title,'') +

case when (case when title is null then 1 else 0 end) = 1 then '' else ' ' end

+ isnull(Last_name,'') +

case when (case when First_name is null then 1 else 0 end) = 1 then '' else ' ' end

+ isnull(First_name,'') +

case when (case when Middle_initial is null then 1 else 0 end)= 1 then '' else ' ' +

isnull(Middle_initial,'') END

I don't know how to use escape sequence, I need insert just once

thanks

hi

i really dont understnd your problem.

does this is what you want

set column1 = isnull(Title,'') +

case when title is null then '' else ' ' end

+ isnull(Last_name,'') +

case when First_name is null then '' else ' ' end

+ isnull(First_name,'') +

case when Middle_initial is null then '' else ' ' end +

isnull(Middle_initial,'') END

OR

if u are facing the quote problem then check this out

SET QUOTED_IDENTIFIER ON

SELECT '"hi"'

SET QUOTED_IDENTIFIER OFF

SELECT """hi"""

SELECT "'hi'"

this might give you the hint of escape sequence.

Regards,

Thanks.

Gurpreet S. Gill

|||

hi,

please give us sample source table

and the output

thanks,

joey

|||

Alessandro,

You could do something like this instead:

update XXX
set Column1 = replace(replace(Title + ' ' + Last_Name + ' ' + First_Name + ' ' + Middle_initial, '@.@.', '@.'), '@.@.', '@.')

NOTE: Use Spaces instead of @. symbols.

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
--
%_*
%_*
John
"Sathyaish" wrote:

> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegroups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.sql

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Replied in Micorsoft.public.sqlserver.server please do not multi-post.

http://www.aspfaq.com/etiquette.asp?id=5003
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006

John
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988847.238366.259770@.g47g2000cwa.googlegr oups.com...
> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.

escape character if the field name has ? in it

HI All,
I'm using SQL server 2000 and SQL Server JDBC driver to connect to it.
I have a field name which has a ? in it. For eg field?
In the query analyzer when i execute the following query
select field? from qtable. It executes successfully. In the Java code
I tried the same like ResultSet rs = stmt.executeQuery("Select field? from
qtable");
The following exceptions was thrown [Microsoft][SQLServer 2000 Driv
er for JDBC][SQLServer]Invalid column name 'field@.P1'.
Any inputs on escape sequence will be highly appreciated.
Thanks,
Prabhu.This is some substitution done by the JDBC driver. I suggest you post this
to a JDBC group. Or, better, don't use "strange" characters in identifiers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Prabhu" <prabhu_mpp@.lycos.com> wrote in message
news:C5537E35-83B0-4F74-880C-EBC51A95F8D2@.microsoft.com...
> HI All,
> I'm using SQL server 2000 and SQL Server JDBC driver to connect to it.
> I have a field name which has a ? in it. For eg field?
> In the query analyzer when i execute the following query
> select field? from qtable. It executes successfully. In the Java code
> I tried the same like ResultSet rs = stmt.executeQuery("Select field?
from qtable");
> The following exceptions was thrown [Microsoft][SQLServer 2000 Driver for

JDBC][SQLServer]Invalid column name 'field@.P1'.
> Any inputs on escape sequence will be highly appreciated.
> Thanks,
> Prabhu.|||Don't do Java anymore so can't try it, but have you tried something like
"select [field?] from qtable"?
"Prabhu" <prabhu_mpp@.lycos.com> wrote in message
news:C5537E35-83B0-4F74-880C-EBC51A95F8D2@.microsoft.com...
> HI All,
> I'm using SQL server 2000 and SQL Server JDBC driver to connect to it.
> I have a field name which has a ? in it. For eg field?
> In the query analyzer when i execute the following query
> select field? from qtable. It executes successfully. In the Java code
> I tried the same like ResultSet rs = stmt.executeQuery("Select field?
from qtable");
> The following exceptions was thrown [Microsoft][SQLServer 2000 Driver for

JDBC][SQLServer]Invalid column name 'field@.P1'.
> Any inputs on escape sequence will be highly appreciated.
> Thanks,
> Prabhu.|||Try enclosing the field name in square braces ie
select [field?] from qtable
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Prabhu" <prabhu_mpp@.lycos.com> wrote in message
news:C5537E35-83B0-4F74-880C-EBC51A95F8D2@.microsoft.com...
> HI All,
> I'm using SQL server 2000 and SQL Server JDBC driver to connect to it.
> I have a field name which has a ? in it. For eg field?
> In the query analyzer when i execute the following query
> select field? from qtable. It executes successfully. In the Java code
> I tried the same like ResultSet rs = stmt.executeQuery("Select field?
from qtable");
> The following exceptions was thrown [Microsoft][SQLServer 2000 Driver for

JDBC][SQLServer]Invalid column name 'field@.P1'.
> Any inputs on escape sequence will be highly appreciated.
> Thanks,
> Prabhu.|||Thanks alot for the responses, but the suggestion given did not solve the is
sue.Any other thoughts pls?
Seems to be JDBC related issue.
Thanks,
Prabhu

escape character for '\' in SQL server 2000

Hello Experts,

We have a SQL server which we have with the name 'SELDSQL533\PRD3', on another server i am trying to create a synonym for a particular database and table inside it. but i get a below error for '\'

Is there any escape character to be used to overcome this problem

CREATE SYNONYM Project

FOR SELDSQL533\PRD3.DMS_copy.project

--

Error: Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '\'.

-

/chandresh

use the following query...

CREATE SYNONYM Project

FOR [SELDSQL533\PRD3].DMS_copy.dbo.project

|||

Use square brackets [ ] around any name that contains 'unacceptable' characters.

CREATE SYNONYM Project

FOR [SELDSQL533\PRD3].DMS_copy.project

Escape character for /SET option of dtexec

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""\Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

You don't show the command but from the error it looks like

/set "\Package.Variables[User::Delim].Properties[Value];^;"

instead try

/set "\Package.Variables[User::Delim].Properties[Value]";"^;"

This puts the semicolon separator outside the quotes and may allow DTExec to process the argument.

HTH,

Matt

|||

Matt,

I am afraid that didn't work either. Any other ideas of how to pass a string containing semi-colons to dtexec using the command line?

This is the output:
H:\>dtexec /SET "\Package.Variables\[User::TheVariable].Properties[Value]";"^;"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Argument ""\Package.Variables\[User::TheVariable].Properties[Value];^;"" for opt
ion "set" is not valid.

H:\>

Regards,
Lars

|||

Sorry I should have been more explicit in my posting. You should try:

dtexec /SET "\"\Package.Variables\[User::TheVariable].Properties[Value]\";\"^;\""

The command interpreter strips quotes so the command I showed previously was what DTExec would work with but you have to escape the quotes so that they get to the DTExec parser. The above shows the actual command line with all the escaping.

HTH,

Matt

|||

The following turned out to be the proper syntax:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\"^;\"

Regards,
Lars

|||

The above did not work when the string contained spaces. The following has yet not failed though:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\""; space"\"

Regards,
Lars

Escape character for /SET option of dtexec

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""\Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

You don't show the command but from the error it looks like

/set "\Package.Variables[User::Delim].Properties[Value];^;"

instead try

/set "\Package.Variables[User::Delim].Properties[Value]";"^;"

This puts the semicolon separator outside the quotes and may allow DTExec to process the argument.

HTH,

Matt

|||

Matt,

I am afraid that didn't work either. Any other ideas of how to pass a string containing semi-colons to dtexec using the command line?

This is the output:
H:\>dtexec /SET "\Package.Variables\[User::TheVariable].Properties[Value]";"^;"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Argument ""\Package.Variables\[User::TheVariable].Properties[Value];^;"" for opt
ion "set" is not valid.

H:\>

Regards,
Lars

|||

Sorry I should have been more explicit in my posting. You should try:

dtexec /SET "\"\Package.Variables\[User::TheVariable].Properties[Value]\";\"^;\""

The command interpreter strips quotes so the command I showed previously was what DTExec would work with but you have to escape the quotes so that they get to the DTExec parser. The above shows the actual command line with all the escaping.

HTH,

Matt

|||

The following turned out to be the proper syntax:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\"^;\"

Regards,
Lars

|||

The above did not work when the string contained spaces. The following has yet not failed though:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\""; space"\"

Regards,
Lars

sql

Escape Character

Does anyone know if there is an escape sequnce you can use in Report
Definition Language? Some data I'm including inside of a tag contains the
'>' character, and it appears to be confusing things. I'd like to retain that
character by escaping it.
Thanks.Have you installed SP1? I can include the character without trouble...
"Darin" wrote:
> Does anyone know if there is an escape sequnce you can use in Report
> Definition Language? Some data I'm including inside of a tag contains the
> '>' character, and it appears to be confusing things. I'd like to retain that
> character by escaping it.
> Thanks.|||Ok, I typed the wrong character. It is the '<' character, not the '>'
character.
Yes I have SP1 installed.
"Mary Bray [SQL Server MVP]" wrote:
> Have you installed SP1? I can include the character without trouble...
> "Darin" wrote:
> > Does anyone know if there is an escape sequnce you can use in Report
> > Definition Language? Some data I'm including inside of a tag contains the
> > '>' character, and it appears to be confusing things. I'd like to retain that
> > character by escaping it.
> >
> > Thanks.

Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do?

Use paramitrimized queries. Then you never have to worry about format's or

SQL Injection.
It's olso better for the preformance, because you don't need

to have to concatenate a string for example:

string query

= "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" +

"txtName.Text + "\"";

No escape characters needed, you doesn't

have to think about using a " or not etc.

Parameters are like

placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date

variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1

WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate",

SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new

SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query,

dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add(

pMyDate );

// Execute the query and get results.
SqlDataReader reader =

dbCommand.ExecuteReader();

try
{
// Walkthrough

results.
while(reader.Read())
{
// TODO: Do something with

the data.
}
}
finally
{
// Close

reader.
reader.Close();
}
}
}
finally
{
// Close

connection.
dbConn.Close();
}

Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do?

Use paramitrimized queries. Then you never have to worry about format's or

SQL Injection.
It's olso better for the preformance, because you don't need

to have to concatenate a string for example:

string query

= "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" +

"txtName.Text + "\"";

No escape characters needed, you doesn't

have to think about using a " or not etc.

Parameters are like

placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date

variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1

WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate",

SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new

SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query,

dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add(

pMyDate );

// Execute the query and get results.
SqlDataReader reader =

dbCommand.ExecuteReader();

try
{
// Walkthrough

results.
while(reader.Read())
{
// TODO: Do something with

the data.
}
}
finally
{
// Close

reader.
reader.Close();
}
}
}
finally
{
// Close

connection.
dbConn.Close();
}