|
P: n/a
|
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)";
| |
Share this Question
|
P: n/a
|
"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') | |
|
P: n/a
|
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')
| |
|
P: n/a
|
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)";
| |
|
P: n/a
|
"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. | |
|
P: n/a
|
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). | |
|
P: n/a
|
"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... :-) | |
|
P: n/a
|
''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)";
| |
|
P: n/a
|
"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... | |
|
P: n/a
|
"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..." | |
|
P: n/a
|
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..."
| |
|
P: n/a
|
<gi*********@gmail.comwrote in message
news:11*********************@l12g2000cwl.googlegro ups.com...
Try this...
I like it! Thanks very much. | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 1555
- replies: 11
- date asked: Dec 15 '06
|
