subquery returned more than 1 value

Question

0.00/5 (No votes)

See more:

I have a products and orders table. I want to get the selling price from products table and multiply it to cases in the orders table. this is what I have done

SqlConnection cn2 = new SqlConnection("Server=Malinao-PC;Database=Thesis_Malinao;User ID=sa;Password=karlo;Trusted_Connection=False;");
                    cn2.Open();

                    SqlCommand cmdAdd = new SqlCommand();
                    cmdAdd.Connection = cn2;
                    cmdAdd.CommandType = CommandType.Text;
                    cmdAdd.CommandText = "INSERT INTO Orders (ProductCode, Cases, Sender, msgID, ProductID) VALUES ('" + str4 + "' , '" + str5 + "' , '" + senderx + "' , " + mID + " , " + str6 + ")";
                    cmdAdd.ExecuteNonQuery();

           
                    SqlCommand cmdCompute = new SqlCommand();
                    cmdCompute.Connection = cn2;
                    cmdCompute.CommandType = CommandType.Text;
                    cmdCompute.CommandText = "UPDATE Orders SET TotalPrice = ((SELECT SellingPrice FROM Products where ProductID = " + str6 + ") * (SELECT Cases FROM Orders WHERE ProductID = " + str6 + ")) INNER JOIN Orders ON Orders.ProductID = Products.ProductID WHERE ProductID = " + str6 + " AND TotalPrice = '0'";
                    cmdCompute.ExecuteNonQuery();
                    cn2.Close();

But it keeps returning this error.

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

Posted 20-Feb-14 8:15am

BeastMode10

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-02-20T08:26:00

Your second query involves two subqueries: one from Products and one from Orders which you are trying to multiply together. If either of those queries returns more than one value, then the multiplication cannot be done, as you would for example be trying to multiply 6 by (2, 4, 6, 8) and return it as a single value - which makes no sense.

I'm not sure what the actual structure of your data is, or how it is interrelated, but think you want to start by looking at each side of the multiply operator and working out how to just get one value each...

Maciej Los · Answer 2 · 2014-02-20T09:25:00

Solution 1 by OriginalGriff is very good and i would add some extra information...

Have you ever heard about SQL Injection[^]? Rather than in-code queries, please, use stored procedures[^].

For further information, please see:
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
How to: Execute a Stored Procedure that Returns Rows[^]
How to: Execute a Stored Procedure that Returns No Value[^]

By The Way: the reason of trouble is this part of SQL command:

SQL

TotalPrice = '0'

If TotalPrice is numeric column, remove ['] around zero.

To update field with select statement, use this:

SQL

UPDATE t1 SET t1.Field = t2.OtherField
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Key = t2.Key
WHERE t2.AnotherField = t1.SomeField

elix545 · Answer 3 · 2014-02-20T09:46:00

The problem can be on this SubQuery:

SQL

SELECT  Cases
FROM    Orders
WHERE   ProductID = " + str6 + "

Because you have more than "Cases" values for ProductID by Orders.

If you summarizes all case values with the next subquery, you only will have a value by Product and Order:

SQL

SELECT		SubX.Cases
FROM		(			
				SELECT		SubO.ProductID, SUM(SubO.Cases) as 	Cases
				FROM		Orders as SubO
				WHERE		SubO.ProductID = " + str6 + "
				GROUP BY	SubO.ProductID
			) as SubX

The final Query could be:

SQL

UPDATE  Orders
SET     TotalPrice =    (       SELECT      SellingPrice
                                FROM        Products
                                where ProductID = " + str6 + "  )
                                *   (   SELECT      SubX.Cases
                                        FROM        (
                                                        SELECT      SubO.ProductID, SUM(SubO.Cases) as  Cases
                                                        FROM        Orders as SubO
                                                        WHERE       SubO.ProductID = " + str6 + "
                                                        GROUP BY    SubO.ProductID
                                                    ) as SubX
                                    )
WHERE   ProductID = " + str6 + "
        AND TotalPrice = '0'

subquery returned more than 1 value

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0