System.Data.Sql Client.SqlExcep tion: 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.Sql Client.SqlExcep tion: 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(strS QL as String)
Dim conn As New System.Data.Sql Client.SqlConne ction(CONN_STRI NG)
Dim cmd As New System.Data.Sql Client.SqlComma nd(strSQL, conn)
cmd.Connection. Open()
cmd.ExecuteNonQ uery()
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?
"Rod" <1g******@newsg roup.nospam> wrote in message
news:Ok******** ********@TK2MSF TNGP10.phx.gbl. ..

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.Sql Client.SqlExcep tion: 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(strS QL as String)
Dim conn As New System.Data.Sql Client.SqlConne ction(CONN_STRI NG)
Dim cmd As New System.Data.Sql Client.SqlComma nd(strSQL, conn)
cmd.Connection. Open()
cmd.ExecuteNonQ uery()
conn.Close()
End Sub

Any help would be greatly appreciated.

Rod.

[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" <1g******@newsg roup.nospam> wrote in message
news:Ok******** ********@TK2MSF TNGP10.phx.gbl. ..

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.Sql Client.SqlExcep tion: 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(strS QL as String)
Dim conn As New System.Data.Sql Client.SqlConne ction(CONN_STRI NG)
Dim cmd As New System.Data.Sql Client.SqlComma nd(strSQL, conn)
cmd.Connection. Open()
cmd.ExecuteNonQ uery()
conn.Close()
End Sub

Any help would be greatly appreciated.

Rod.

[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 <yetanothertabl e> Where <condition
returns multiple values>)
Rod

"E. Rodriguez" <bl*@geemail.co m> wrote in message
news:e0******** ******@TK2MSFTN GP09.phx.gbl...

Rod:

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

"Rod" <1g******@newsg roup.nospam> wrote in message
news:Ok******** ********@TK2MSF TNGP10.phx.gbl. ..

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.Sql Client.SqlExcep tion: 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(strS QL as String)
Dim conn As New System.Data.Sql Client.SqlConne ction(CONN_STRI NG)
Dim cmd As New System.Data.Sql Client.SqlComma nd(strSQL, conn)
cmd.Connection. Open()
cmd.ExecuteNonQ uery()
conn.Close()
End Sub

Any help would be greatly appreciated.

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

"Bruce Barker" <br************ ******@safeco.c om> wrote in message
news:uF******** *****@TK2MSFTNG P12.phx.gbl...

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" <1g******@newsg roup.nospam> wrote in message
news:Ok******** ********@TK2MSF TNGP10.phx.gbl. ..

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.Sql Client.SqlExcep tion: 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(strS QL as String)
Dim conn As New System.Data.Sql Client.SqlConne ction(CONN_STRI NG)
Dim cmd As New System.Data.Sql Client.SqlComma nd(strSQL, conn)
cmd.Connection. Open()
cmd.ExecuteNonQ uery()
conn.Close()
End Sub

Any help would be greatly appreciated.

Rod.

[Rod]

Bruce,

It was a trigger. Thanks for the suggestion.

Rod.
"Bruce Barker" <br************ ******@safeco.c om> wrote in message
news:uF******** *****@TK2MSFTNG P12.phx.gbl...

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" <1g******@newsg roup.nospam> wrote in message
news:Ok******** ********@TK2MSF TNGP10.phx.gbl. ..

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.Sql Client.SqlExcep tion: 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(strS QL as String)
Dim conn As New System.Data.Sql Client.SqlConne ction(CONN_STRI NG)
Dim cmd As New System.Data.Sql Client.SqlComma nd(strSQL, conn)
cmd.Connection. Open()
cmd.ExecuteNonQ uery()
conn.Close()
End Sub

Any help would be greatly appreciated.

Rod.