By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,558 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Nov 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Rod:

Are you expecting multiple rows returned from that inner most sql statement?
"Rod" <1g******@newsgroup.nospam> wrote in message
news:Ok****************@TK2MSFTNGP10.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.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.

Nov 19 '05 #2

P: n/a
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******@newsgroup.nospam> wrote in message
news:Ok****************@TK2MSFTNGP10.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.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.

Nov 19 '05 #3

P: n/a
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

"E. Rodriguez" <bl*@geemail.com> wrote in message
news:e0**************@TK2MSFTNGP09.phx.gbl...
Rod:

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

"Rod" <1g******@newsgroup.nospam> wrote in message
news:Ok****************@TK2MSFTNGP10.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.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.


Nov 19 '05 #4

P: n/a
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.com> wrote in message
news:uF*************@TK2MSFTNGP12.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******@newsgroup.nospam> wrote in message
news:Ok****************@TK2MSFTNGP10.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.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.


Nov 19 '05 #5

P: n/a
Rod
Bruce,

It was a trigger. Thanks for the suggestion.

Rod.
"Bruce Barker" <br******************@safeco.com> wrote in message
news:uF*************@TK2MSFTNGP12.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******@newsgroup.nospam> wrote in message
news:Ok****************@TK2MSFTNGP10.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.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.


Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Browse more ASP.NET Questions on Bytes