'子查询返回的值超过1。这是不允许的'错误 [英] 'Subquery returned more than 1 value. This is not permitted' error

问题描述

我正在尝试在C中执行插入查询时收到此错误代码。



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

语句已终止。 '



我试图将用户输入的值插入表中，然后从该表的主键返回ID，然后将值插入到不同的表中并插入将返回的ID转换为该表中的外键。



这是我按下提交值按钮时的C＃代码：

I am currently getting this error code when trying to execute an insert query in C#

System.Data.SqlClient.SqlException: 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.'

I am trying to insert user entered values into a table, then return the ID from the primary key of that table, then insert values into a different table and insert the returned ID into the foreign key into this table.

Here is my C# code from when the button to submit the values is pressed:

protected void btnSubmit_Click(object sender, EventArgs e)
        {
        string query = "insert into Stock_Take(Username, StockDate) OUTPUT INSERTED.StockTakeIDNew values(@Username, GetDate()); Insert into Stock_Take_Item(ItemID, BarQuantity, StorageQuantity, StockTakeIDNew) values(@ID, @BAR, @STORAGE, (Select StockTakeIDNew from Stock_Take))";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(query, con);
        con.Open();

        foreach (GridViewRow row in gvStockTake.Rows)
        {
            Label ID = row.FindControl("itemId") as Label;
            TextBox BAR = row.FindControl("txtBar") as TextBox;
            TextBox STORAGE = row.FindControl("txtStorage") as TextBox;
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID.Text;
            cmd.Parameters.Add("@BAR", SqlDbType.Int).Value = BAR.Text;
            cmd.Parameters.Add("@STORAGE", SqlDbType.Int).Value = STORAGE.Text;

        }
        cmd.Parameters.AddWithValue("@Username", Session["username"]);
        cmd.ExecuteNonQuery(); //query execution
        con.Close();
        Response.Write("Successfully inserted stock take items.");
    }

我尝试过：



我想也许我应该将查询拆分为两个，并在SQL语句的后半部分有一个query1。因此，首先执行查询的前半部分，然后在'foreach'部分中添加参数后再执行后半部分？



我还是C＃和SQL的新手，所以任何建议都完全赞赏!!

What I have tried:

I think that perhaps I should split the query into two, and have a query1 for the second half of the SQL statement. Therefore executing the first first half of the query first, and then the second half once i had added the parameters in the 'foreach' section?

I'm still new to C# and SQL so any recommendations are fully appreciated!!

推荐答案

阅读我对你上一个问题的评论：如何使主键值与另一个表中的外键相同？ [ ^ ] - 它告诉你该怎么做。

代替乱用OUTPUT，使用SCOPE_IDENTITY获取FOREIGN KEY值：

Read what I said to your last question: How can I make the primary key value the same the foreign key in a different table?[^] - it tells you what to do.
Instead of messing around with OUTPUT, use the SCOPE_IDENTITY to get the FOREIGN KEY value:

INSERT INTO Stock_Take (Username, StockDate) VALUES (@Username, GetDate()); DECLARE @STID INT; SET @STID = SCOPE_IDENTITY(); INSERT INTO Stock_Take_Item(ItemID, BarQuantity, StorageQuantity, StockTakeIDNew) values(@ID, @BAR, @STORAGE, @STID)

这篇关于'子查询返回的值超过1。这是不允许的'错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！