update语句在SSMS中有效，但在代码中不起作用

Question

我已经用头撞墙两天了。此update语句在ssms中运行时有效

update dbo.DEMOGRAPHICS 
set ETHNICITY = (select distinct(ethnicity) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = 'P000084716' and ACADEMIC_SESSION != '')
, GENDER = (select distinct(GENDER) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = 'P000084716' and ACADEMIC_SESSION != '')
, MARITAL_STATUS = (select distinct(MARITAL_STATUS) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = 'P000084716' and ACADEMIC_SESSION != '')
, RELIGION = (select distinct(RELIGION) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = 'P000084716' and ACADEMIC_SESSION != '')
, VETERAN = (select distinct(VETERAN) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID =  'P000084716' and ACADEMIC_SESSION != '')
, CITIZENSHIP = (select distinct(CITIZENSHIP) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = 'P000084716'and ACADEMIC_SESSION != '')
,RETIRED = (select distinct(RETIRED) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '000084716' and ACADEMIC_SESSION != '')
, LEGAL_RESIDENCE = (select distinct(LEGAL_RESIDENCE) from dbo.DEMOGRAPHICS where   PEOPLE_CODE_ID = 'P000084716' and ACADEMIC_SESSION != '')
where PEOPLE_CODE_ID = 'P000084716'  and ACADEMIC_SESSION = ''

但是在代码中进行更新不会

string updDemoRecords = @"update dbo.DEMOGRAPHICS 
set ETHNICITY = (select distinct(ethnicity) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
, GENDER = (select distinct(GENDER) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
, MARITAL_STATUS = (select distinct(MARITAL_STATUS) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
, RELIGION = (select distinct(RELIGION) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
, VETERAN = (select distinct(VETERAN) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
, CITIZENSHIP = (select distinct(CITIZENSHIP) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}'and ACADEMIC_SESSION != '')
,RETIRED = (select distinct(RETIRED) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
, LEGAL_RESIDENCE = (select distinct(LEGAL_RESIDENCE) from dbo.DEMOGRAPHICS where PEOPLE_CODE_ID = '{0}' and ACADEMIC_SESSION != '')
where PEOPLE_CODE_ID = '{0}'  and ACADEMIC_SESSION = ''";
updDemoRecords = string.Format(updDemoRecords, peopleOrgCodeID);
pcCon = new SqlConnection(pcConnString);
SqlCommand doUpdDemoRecords = new SqlCommand(updDemoRecords, pcCon);
pcCon.Open();
doUpdDemoRecords.ExecuteNonQuery();
pcCon.Close();

在存储的proc中也尝试过，它也不会更新相关的行。它是sql server 2008和c#.net。

Answer 1

问题出现在我看来，你使用的是一个实际的数字(peopleOrgCodeID) -这将不会有前导的0es，因为你在SSMS版本中使用(使用字符串)。

因此，如果peopleOrgCodeId是一个值为84716的整数(或long)，您的WHERE子句将以如下形式结束：

where PEOPLE_CODE_ID = '84716'

而不是：

where PEOPLE_CODE_ID = '000084716'

可以通过使用composite formatting string或传入带有前导零的字符串值来解决此问题。

我会提到SQL注入是一个可能的问题，但是如果peopleOrgCodeID确实是一个整数，那么在这种情况下是安全的。但是，我仍然会使用参数化查询，而不是string.Format。