Problem with single quotes in SQL statement

[Elmo]

Hi all!

I am not very proud to ask this but here is my problem:

string code = "\'13\'"

The string code will have to contain following info:
'51','52','63','other'...
to get certain info from the database. When the querry is parsed these
values will be looked up:
....
compCode in (@code)
....

Can someone tell me why these values are not found in the DB? I figured
out that maybe he puts his own quotes so I tried the following:

"51" -->works (but only for one value?)
" \' 51\' " --does not work
"'51'" --does not work

Does anyone have some ideas how I can look up the right values? THX!

DateTime startDate = new DateTime(2006,12,14,0,0,0);
DateTime endDate = new DateTime(2006,12,15,23,59,59);
string campaignCode = "O850";
string code = "\'13\'"; //,\'51\'
string language = "NL";
string startdate = Convert.ToString(startDate.Year + "-" +
startDate.Month + "-" + startDate.Day);
string enddate = Convert.ToString(endDate.Year + "-" +
endDate.Month + "-" + endDate.Day);
// define connection
sqlConn = new SqlConnection(CONNECTION);

// define query

sqlQuery = "select count(*) as Aantal, sum(talktime) as TalkTime, "
+ "sum(updatetime) as UpdateTime, sum(talktime + updatetime) as
HandleTime "
+ "from [REPORTSERVERRPT].dbo.SOMETHING"
+ "where calldate @startdate and"
+ " calldate < @enddate and "
+ "compCode in (@code) and "
+ "sleutel in ("
+ "select up.sleutel "
+ "from [REPORTSERVER-RPT].dbo.TEST as up "
+ "where up.CampaignName = @campaign and "
+ "up.lang = @language)";

[Mark Rae]

"Elmo" <bv***@concentra.bewrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...

Does anyone have some ideas how I can look up the right values? THX!

SELECT * FROM Table WHERE Field IN ('51','52','63')

[Elmo]

That is absolutly not the problem (but thx though ;-))
Maybe following output from sql profiler will help you guys :

Values of my parameters:
INPUT IS :

string code = "13" + "'" + "," +"'" + " 51";

OUPUT IS :
@startDate = '2006-12-14', @endDate = '2006-12-15', @campaign = 'O850',
@language = 'NL', @code = '13'','' 51'

So without asking he puts an extra quote in the string for each single
quote i use

Mark Rae schreef:

"Elmo" <bv***@concentra.bewrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...

Does anyone have some ideas how I can look up the right values? THX!

SELECT * FROM Table WHERE Field IN ('51','52','63')

[Peter Bradley]

Ah! I see what you're trying to do. You're trying to do a macro
substitution. You can't do that.

The IN operator is expecting a number of comma delimited parameters (sort
of). You're passing one parameter to it, so it treats it as the first item
in the list - not as a list of many items. I haven't explained that too
well; but I hope you see what I'm getting at.

I'm afraid you'll just have to type in the individual values.

Of course, if you can retrieve the values from a table, you can do a
subselect inside the IN operator's brackets; which could save you some
typing, I suppose.

HTH
Peter

"Elmo" <bv***@concentra.bewrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...

Hi all!

I am not very proud to ask this but here is my problem:

string code = "\'13\'"

The string code will have to contain following info:
'51','52','63','other'...
to get certain info from the database. When the querry is parsed these
values will be looked up:
...
compCode in (@code)
...

Can someone tell me why these values are not found in the DB? I figured
out that maybe he puts his own quotes so I tried the following:

"51" -->works (but only for one value?)
" \' 51\' " --does not work
"'51'" --does not work

Does anyone have some ideas how I can look up the right values? THX!

DateTime startDate = new DateTime(2006,12,14,0,0,0);
DateTime endDate = new DateTime(2006,12,15,23,59,59);
string campaignCode = "O850";
string code = "\'13\'"; //,\'51\'
string language = "NL";
string startdate = Convert.ToString(startDate.Year + "-" +
startDate.Month + "-" + startDate.Day);
string enddate = Convert.ToString(endDate.Year + "-" +
endDate.Month + "-" + endDate.Day);
// define connection
sqlConn = new SqlConnection(CONNECTION);

// define query

sqlQuery = "select count(*) as Aantal, sum(talktime) as TalkTime, "
+ "sum(updatetime) as UpdateTime, sum(talktime + updatetime) as
HandleTime "
+ "from [REPORTSERVERRPT].dbo.SOMETHING"
+ "where calldate @startdate and"
+ " calldate < @enddate and "
+ "compCode in (@code) and "
+ "sleutel in ("
+ "select up.sleutel "
+ "from [REPORTSERVER-RPT].dbo.TEST as up "
+ "where up.CampaignName = @campaign and "
+ "up.lang = @language)";

[Mark Rae]

"Elmo" <bv***@concentra.bewrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

So without asking he puts an extra quote in the string for each single
quote i use

Ah right - yes, that is standard ADO.NET behaviour for string parameters
because a single quote in T-SQL is the string variable delimter. When T-SQL
encounters a single quote, it interprets it as the beginning of a string
variable containing everything which follows until the next single quote is
reached. To get round the obvious problem of what to do with a string
variable need to contain a single quote, one single quote is converted into
two single quotes.

[Elmo]

Peter,

Yup, you understand the problems I'm facing... I also thought of this
extra table you described, but after carefull consideration we
(programmers team) decided against that because these values will vary
(depending on the campaign).

Quote -

so it treats it as the first item

in the list - not as a list of many items. I haven't explained that too
well; but I hope you see what I'm getting at.

I'm afraid you'll just have to type in the individual values.

/Quote

Typing the individual values is the best option but I can't allow users
to change sql statements... and we explicitly decided AGAINST dynamic
SQL :(. I also considered parsing every code (51,60,...) individually
but that is not gonna work because null values can occur (not to
mention the performance issues).

[Mark Rae]

"Elmo" <bv***@concentra.bewrote in message
news:11*********************@t46g2000cwa.googlegro ups.com...

Yup, you understand the problems I'm facing... I also thought of this
extra table you described, but after carefull consideration we
(programmers team) decided against that because these values will vary
(depending on the campaign).

Quote -

so it treats it as the first item

>in the list - not as a list of many items. I haven't explained that too
well; but I hope you see what I'm getting at.

I'm afraid you'll just have to type in the individual values.

/Quote

Typing the individual values is the best option but I can't allow users
to change sql statements... and we explicitly decided AGAINST dynamic
SQL :(. I also considered parsing every code (51,60,...) individually
but that is not gonna work because null values can occur (not to
mention the performance issues).

Apologies - I now understand your problem too...

You can get round the problem of creating dynamic SQL client-side, but
you'll have to use a stored procedure instead and create dynamic SQL in
that. Here's an example from AdventureWorks...

CREATE PROCEDURE Person.TestProc
@pstrCriteria varchar(100) = NULL
AS

DECLARE @strSQL nvarchar(4000)

SET @strSQL =
'
SELECT
*
FROM
Person.CountryRegion
WHERE
CountryRegionCode IN (' + REPLACE(@pstrCriteria, '¬', '''') + ')'
EXEC sp_executesql @strSQL

Then, run the following SQL:
Person.TestProc '¬AD¬,¬AE¬,¬AF¬'

The trick is to use a very uncommon character (¬, in this case) to pretend
to be the text delimiter...

I fully appreciate that this looks like a complete hacky kludge - and it
is! - but it works, it's extremely fast, it gets round the problem of
client-side SQL Injection, and I haven't found a neater way so far... :-)

[Kevin Spencer]

''51'',''52'',''63'',''other''...

(doubled single quotes - Transact-SQL considers them escaped)
should work.

--
HTH,

Kevin Spencer
Microsoft MVP
Bit Player
http://unclechutney.blogspot.com

Expect the unaccepted.

"Elmo" <bv***@concentra.bewrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...

Hi all!

I am not very proud to ask this but here is my problem:

string code = "\'13\'"

The string code will have to contain following info:
'51','52','63','other'...
to get certain info from the database. When the querry is parsed these
values will be looked up:
...
compCode in (@code)
...

Can someone tell me why these values are not found in the DB? I figured
out that maybe he puts his own quotes so I tried the following:

"51" -->works (but only for one value?)
" \' 51\' " --does not work
"'51'" --does not work

Does anyone have some ideas how I can look up the right values? THX!

DateTime startDate = new DateTime(2006,12,14,0,0,0);
DateTime endDate = new DateTime(2006,12,15,23,59,59);
string campaignCode = "O850";
string code = "\'13\'"; //,\'51\'
string language = "NL";
string startdate = Convert.ToString(startDate.Year + "-" +
startDate.Month + "-" + startDate.Day);
string enddate = Convert.ToString(endDate.Year + "-" +
endDate.Month + "-" + endDate.Day);
// define connection
sqlConn = new SqlConnection(CONNECTION);

// define query

sqlQuery = "select count(*) as Aantal, sum(talktime) as TalkTime, "
+ "sum(updatetime) as UpdateTime, sum(talktime + updatetime) as
HandleTime "
+ "from [REPORTSERVERRPT].dbo.SOMETHING"
+ "where calldate @startdate and"
+ " calldate < @enddate and "
+ "compCode in (@code) and "
+ "sleutel in ("
+ "select up.sleutel "
+ "from [REPORTSERVER-RPT].dbo.TEST as up "
+ "where up.CampaignName = @campaign and "
+ "up.lang = @language)";

[Mark Rae]

"Kevin Spencer" <sp**@uce.govwrote in message
news:u6**************@TK2MSFTNGP03.phx.gbl...

''51'',''52'',''63'',''other''...

(doubled single quotes - Transact-SQL considers them escaped)
should work.

Should, but shouldn't... at least, not in the context that the OP wishes to
use them...

[Mark Rae]

"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:uQ*************@TK2MSFTNGP02.phx.gbl...

Should, but shouldn't...

Sorry, I meant to say "Should, but doesn't..."

[gineshkumar]

Try this...

SET @strCriteria = '51,52,63,other' --input string
--------------------------------
Procedure

if (Left(@strCriteria,1) <',')
set @strCriteria = ',' + @strCriteria

if (Right(@strCriteria,1 ) < ',')
set @strCriteria = @strCriteria + ','
SELECT
*
FROM
Person.CountryRegion
WHERE
charindex( ','+ CountryRegionCode + ',',@strCriteria) <0
EXEC sp_executesql @strSQL
Mark Rae wrote:

"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:uQ*************@TK2MSFTNGP02.phx.gbl...

Should, but shouldn't...

Sorry, I meant to say "Should, but doesn't..."

[Mark Rae]

<gi*********@gmail.comwrote in message
news:11*********************@l12g2000cwl.googlegro ups.com...

Try this...

I like it! Thanks very much.