System.Data.SqlClient.SqlException: Subquery returned more than 1 value.

[Rod]

I have a client site where the code below has been working happily for at
least four months. The site is using SQL Server 7. The code is ASP.NET

Last week an error appeared related to the following SQL statement.

INSERT INTO OrderItems (ClientID, ProductID, OrderHeaderID, Quantity,
Dispatched, BackOrdered) SELECT ClientID, ProductID, 1371 AS OrderHeaderID,
Quantity, Dispatched, BackOrdered FROM Basket WHERE RequisitionID = 1369

The error message is:

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...

There appears to be nothing wrong with the SQL Statement, indeed if run in
Query Analyser it executes correctly. I cannot see how the error message
relates to the code at all. Nor do I understand why the code worked for
four months and has now decided to fail.

The context in which the code is called is the procedure below where the SQL
statement is passed to the parameter strSQL.

Private Sub executeSQL(strSQL as String)
Dim conn As New System.Data.SqlClient.SqlConnection(CONN_STRING)
Dim cmd As New System.Data.SqlClient.SqlCommand(strSQL, conn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Sub

Any help would be greatly appreciated.

Rod.

 

[E. Rodriguez]

Rod:

Are you expecting multiple rows returned from that inner most sql statement?

 

[Bruce Barker]

the posted statement has no subquery, check to see if a trigger exists on
the table, which might throw the error, otherwise, you do not have the
correct failing sql cmd text.

-- bruce (sqlwork.com)

 

[Rod]

Thanks for the response.

Yes multiple rows are returned from the innermost Select statement, but the
statement works when run on the SQL Server via Query Analyser and inserts
multiple rows in the target table. It has worked in the existing code for
months.

I would expect the error I am getting if the statement read something like

Insert Into <table> (<field list>) Select <data fields> From <othertable>
Where <key> = (Select <keyvalue> from <yetanothertable> Where <condition
returns multiple values>)


Rod

 

[Rod]

Bruce,

I'm not sure what you mean by 'you do not have the correct failing sql cmd
text.' but you might be onto something with the trigger idea. Someone else
has been employed to do additional work on the system since I completed my
contract and has been creating triggers on the tables.

They may have done something to cause the problem. It's always good to be
able to blame something someone else has done. (I jest (a little)).

Rod

 

[Rod]

Bruce,

It was a trigger. Thanks for the suggestion.

Rod.

 

[mrf]

Rod,How did you solve this problem after finding out that it was caused by a trigger?