sql子查询上的C＃异常 [英] C# exception on sql subqueries

问题描述

代码

Code

SqlConnection con = new SqlConnection(@"server=ZAIN\SQLEXPRESS;database=Project;user id=sa;pwd=abdeen");
                SqlDataAdapter com = new SqlDataAdapter("execute UpdateShopDetails '" + txtName.Text + "','" + txtAddress.Text + "','" + txtPhoneNo.Text + "'", con);
                DataSet ds = new DataSet();
                con.Open();
                com.Fill(ds);
                con.Close();

存储过程

Stored Procedure

create proc UpdateShopDetails
@Name char(50) , @Address varchar(100), @PhoneNo varchar(20)
as
Update Shops
set Name=@Name
where Sid=(select Sid from Shops where Name=@Name)
Update ContactInfo
set Sid=(select Sid from Shops Where Name= @Name)
where Sid=(select Sid from Shops Where Name= @Name)
Update AddressInfo
set Contactid=(select Contactid from ContactInfo where Sid=(select Sid from Shops where Name=@Name)),Address=@Address
where Contactid=(select Contactid from ContactInfo where Sid=(select Sid from Shops where Name=@Name))
Update PhoneInfo
set Contactid=(select Contactid from ContactInfo where Sid=(select Sid from Shops where Name=@Name)),PhoneNo=@PhoneNo
where Contactid=(select Contactid from ContactInfo where Sid=(select Sid from Shops where Name=@Name))

错误

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

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

推荐答案

Your getting this error because your subquery is returning more than one value and you are using "=" operator which is anticipating single value.  One to way solve the problem is to use "TOP 1" in your subquery.
For Example
set Sid=(select Top 1 Sid from Shops Where Name= @Name)

首先，我是否可以建议您每次执行时只读取Sid和ContactId，并将它们存储在变量中？然后确保它只返回一个值：

First off, can I suggest that you only read the Sid and ContactId once per execution, and store them in a variable? Then ensure it returns only a single value:

DECLARE @SID INT
Set @SID = (SELECT TOP 1 Sid FROM MyTable WHERE Name=@Name)

然后使用每个更新中的变量。

Then use the variable in each of your updates.

这篇关于sql子查询上的C＃异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！