尝试更新数据库时SqlExeption [英] SqlExeption when trying to update database

问题描述

您好，



当我想更新我的数据库时，我收到以下SqlExeption：



子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。



这是我在C＃文件中的代码：

Hello,

When I want to update my database I recieve following SqlExeption:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here is my code in the C# file:

DbParameters parameters = new DbParameters();
           parameters.Add("apName", apName, DbType.String);
           parameters.Add("apDescription", apDescription, DbType.String);
           parameters.Add("apID", apId.ToString(), DbType.Int32);
           parameters.Add("apBusinessCritical", apBusinessCritical.ToString(), DbType.Boolean);
           parameters.Add("apApplicationSupport", ApplicationSupport.ToString(), DbType.Int32);
           parameters.Add("apApplicationSupport2", ApplicationSupport2.ToString(), DbType.Int32);
           parameters.Add("apApplicationSupport3", ApplicationSupport3.ToString(), DbType.Int32);
           parameters.Add("apRemark", "", DbType.String);
           parameters.Add("ApplManagerPersonalNo", ApplManagerPersonalNo.ToString(), DbType.Int32);
           parameters.Add("ApplOwnerPersonalNo", ApplOwnerPersonalNo.ToString(), DbType.Int32);
           parameters.Add("apConsulting", apConsulting, DbType.String);
           parameters.Add("apOperation", apOperation, DbType.String);
           parameters.Add("apSLAIntern", apSLAIntern, DbType.String);
           parameters.Add("apSLAExtern", apSLAExtern, DbType.String);
           parameters.Add("apPlattform", apPlattform, DbType.String);
           parameters.Add("apDesign", apDesign, DbType.String);
           parameters.Add("apDevImp", apDevImp, DbType.String);
           parameters.Add("apTraining", apTraining.ToString(), DbType.Boolean);
           parameters.Add("apLieferant", apLieferant, DbType.String);
           parameters.Add("apBetriebszeit", apBetriebszeit, DbType.String);
           parameters.Add("apKPI", apKPI.ToString(), DbType.Boolean);

           this._dbAppOverview.Open("sp_UpdateApplication", parameters);

这是我的存储过程：

And here is my Stored Procedure:

USE [Application_Overview]
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateApplication]    Script Date: 02/12/2013 10:17:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Leonard Krähenbühl
-- Create date: 02.03.2011
-- Used In:		ApplicationOverview.applDetailView.aspx
-- =============================================
ALTER PROCEDURE [dbo].[sp_UpdateApplication]
	
	@apID					int				= 0	,
	@apName					nvarchar(50)		,
	@apDescription			nvarchar(4000)		,
	@apBusinessCritical		bit					,
	@apApplicationSupport	int					,
	@apApplicationSupport2	int					,
	@apApplicationSupport3	int					,
	@ApplManagerPersonalNo  nvarchar(10)		,
	@apRemark				nvarchar(4000)		,
	@ApplOwnerPersonalNo	nvarchar(10)		,
	@apConsulting			nvarchar(4000)		,
	@apOperation			nvarchar(4000)		,
	@apSLAIntern			nvarchar(4000)		,
	@apSLAExtern			nvarchar(4000)		,
	@apPlattform			nvarchar(25)		,	
	@apDesign				nvarchar(4000)		,
	@apDevImp				nvarchar(4000)		,
	@apTraining				bit					,
	@apLieferant			nvarchar(4000)		,
	@apBetriebszeit			nvarchar(4000)		,
	@apKPI					bit					

AS
BEGIN
	DECLARE @usIDOwner int
	DECLARE @usIDManager int

	SET @usIDOwner = (SELECT usID FROM tblUser WHERE usPersonalNo = CAST(@ApplOwnerPersonalNo AS int))
	
	IF isnull(@usIDOwner, 0) = 0 OR @usIDOwner = 0
	BEGIN
		INSERT INTO dbo.tblUser(usPersonalNo)
		VALUES		(@ApplOwnerPersonalNo)
		 
		SET @usIDOwner = (SELECT usID FROM tblUser WHERE usPersonalNo = CAST(@ApplOwnerPersonalNo AS int))
		
	END

	SET @usIDManager = (SELECT usID FROM tblUser WHERE usPersonalNo = CAST(@ApplManagerPersonalNo AS int))

	IF isnull(@usIDManager, 0) = 0 OR @usIDManager = 0
	BEGIN
		INSERT INTO dbo.tblUser(usPersonalNo)
		VALUES		(@ApplManagerPersonalNo)

		SET @usIDManager = (SELECT usID FROM tblUser WHERE usPersonalNo = CAST(@ApplManagerPersonalNo AS int))
	END

	UPDATE	dbo.tblApplications 
	SET		apName = @apName, apDescription = @apDescription, apBusinessCritical = @apBusinessCritical,
			apApplicationSupport = @apApplicationSupport, apAppSupport2 = @apApplicationSupport2, apAppSupport3 = @apApplicationSupport3,
			apApplManagerUsID = @usIDManager, apSLAIntern = @apSLAIntern, apSLAExtern = @apSLAExtern,
			apOwnerUsID = @usIDOwner, apRemark = @apRemark, apConsulting = @apConsulting, apOperation = @apOperation,
			apPlattform = @apPlattform, apDesign = @apDesign, apDevImp = @apDevImp, apTraining = @apTraining,
			apLieferant = @apLieferant, apBetriebszeit = @apBetriebszeit, apKPI = @apKPI
	WHERE	(apID = @apID)
END

有人知道错误的来源吗？



感谢您的支持。

Does somebody know where the error is coming from?

Thanks for the support.

推荐答案

当你使用子查询时，它应该返回一个值来进行赋值。

when you use subquery it should return a single value for assignment.

SELECT usID FROM tblUser

更新这些块

update these chunks with

SELECT top 1 usID FROM tblUser

这篇关于尝试更新数据库时SqlExeption的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！