SQL语句中单引号的问题 [英] Problem with single quotes in SQL statement
问题描述
大家好！
我不是很自豪地问这个，但这是我的问题：
字符串代码=" \\ \\''13 \''"
字符串代码必须包含以下信息：
''51''，''52' '，''63''，''other''...
从数据库中获取某些信息。当解析querry时，将查询这些
值：
....
compCode in（@code）
....
有人能告诉我为什么在数据库中找不到这些值吗？我认为
out也许他自己引用了所以我尝试了以下内容：
" 51" - >有效（但仅适用于一个值？）
" \'''51''''' - 不工作
"'''51''" - 不工作
有没有人有一些想法，我怎么能找到正确的价值观？ 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）;
//定义连接
sqlConn = new SqlConnection（CONNECTION ）;
//定义查询
sqlQuery =" select count（*）as Aantal，sum（talktime）as TalkTime，" ;
+" sum（updatetime）为UpdateTime，sum（talktime + updatetime）为
HandleTime"
+" from [ REPORTSERVERRPT] .dbo.SOMETHING"
+"其中calldate @startdate和"
+" calldate< @enddate和"
+" compCode in（@code）和"
+" sleutel in（"
+ select up.sleutel
+" from [REPORTSERVER-RPT] .dbo.TEST as up
+where up.CampaignName = @广告系列和
+up.lang = @language）" ;;
"毛毛" < bv *** @ concentra.bewrote in message
news：11 ********************** @ n67g2000cwd.googlegr oups .com ...
有没有人有一些想法如何查找正确的值？谢谢！
SELECT * FROM表WHERE字段IN（'''51''，''52''，''63''）
这绝对不是问题（但是通过;-)）
也许以下来自sql profiler的输出会帮助你们：
我的参数值：
INPUT IS：
string code =" 13" +"''" +， + QUOT;" + 51"
OUPUT是：
@startDate =''2006-12-14''，@ endDate =''2006-12-15' '，@ campaign ='''O850''，
@language =''NL''，@ code ='''13'''''''''''''''''
所以没有要求他在每个单独的字符串中添加额外的报价
报价我使用
Mark Rae schreef：< br>
" Elmo" < bv *** @ concentra.bewrote in message
news：11 ********************** @ n67g2000cwd.googlegr oups .com ...
有没有人有一些想法如何查找正确的值？谢谢！
SELECT * FROM表WHERE字段IN（''51''，''52''，''63''）
啊！我明白你要做什么了。你正在尝试做一个宏取代
。你不能这样做。
IN运算符期待一些逗号分隔的参数（排序
）。你正在传递一个参数，所以它将它视为列表中的第一项
- 而不是许多项目的列表。我也没有解释过这个问题。但是我希望你能看到我的目标。
我恐怕你只需输入个人值。
当然，如果你可以从表中检索值，你可以在IN运算符的括号内做一个
子选择;我想这可以为你节省一些
打字。
HTH
彼得
" Elmo" < bv *** @ concentra.bewrote in message
news：11 ********************** @ n67g2000cwd.googlegr oups .com ...
大家好！
我不是很自豪地问这个但是这是我的问题：
字符串代码=" \''13 \''"
字符串代码必须包含以下信息：
''51''，'''''''''''''''other''...
从数据库中获取某些信息。当解析querry时，将查找这些
值：
...
compCode in（@code）
...
有人能告诉我为什么在数据库中找不到这些值吗？我认为
out也许他自己引用了所以我尝试了以下内容：
" 51" - >有效（但仅适用于一个值？）
" \'''51''''' - 不工作
"'''51''" - 不工作
有没有人有一些想法，我怎么能找到正确的价值观？ 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）;
//定义连接
sqlConn = new SqlConnection（CONNECTION ）;
//定义查询
sqlQuery =" select count（*）as Aantal，sum（talktime）as TalkTime，" ;
+" sum（updatetime）为UpdateTime，sum（talktime + updatetime）为
HandleTime"
+" from [ REPORTSERVERRPT] .dbo.SOMETHING"
+"其中calldate @startdate和"
+" calldate< @enddate和"
+" compCode in（@code）和"
+" sleutel in（"
+ select up.sleutel
+" from [REPORTSERVER-RPT] .dbo.TEST as up
+where up.CampaignName = @广告系列和
+up.lang = @language）" ;;
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)";
解决方案"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'')
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'')
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)";
这篇关于SQL语句中单引号的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
