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

I have a client site where the code below has been working happily for at
least four months. Last week an error appeared related to the following SQL
statement.

The site is using SQL Server 7. The code is ASP.NET

An example of the SQL statement is:

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

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

There was no way for you to know it, but this is a classic asp newsgroup.
While you may be lucky enough to find a dotnet-savvy person here who can
answer your question, you can eliminate the luck factor by posting your
question to a group where those dotnet-savvy people hang out. I suggest
microsoft.public.dotnet.framework.aspnet.

However, I'll take a stab at it (I've just started using .Net)


Rod wrote:
> I have a client site where the code below has been working happily
> for at least four months. Last week an error appeared related to the
> following SQL statement.
>
> The site is using SQL Server 7. The code is ASP.NET
>
> An example of the SQL statement is:
>
> 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 not 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
>

Well, you're right. This error seems to be totally unrelated to this
statement. There is no subquery here.

Is the error reproducible? Try using SQL Profiler to trace what's actually
happening.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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

Try modifying yor Sub:
> 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)
response.write strSQL
response.end
> cmd.Connection.Open()
> cmd.ExecuteNonQuery()
> conn.Close()
> End Sub

Copy the result and run it in your database.

"Rod" <1gandalf@newsgroup.nospam> wrote in message
news:%23RRkgMIXFHA.3584@TK2MSFTNGP14.phx.gbl...
>I have a client site where the code below has been working happily for at
> least four months. Last week an error appeared related to the following
> SQL
> statement.
>
> The site is using SQL Server 7. The code is ASP.NET
>
> An example of the SQL statement is:
>
> 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 not 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.
>
>

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

Thanks Bob.

I noticed after I posted that the group was .asp not aspnet, but I
appreciate your willingness to have a look at my problem. I'm much happier
using asp but one has to move with the times, or at least try to.

Rod.



"Bob Barrows [MVP]" wrote in message
news:uki9PWIXFHA.160@TK2MSFTNGP15.phx.gbl...
> There was no way for you to know it, but this is a classic asp newsgroup.
> While you may be lucky enough to find a dotnet-savvy person here who can
> answer your question, you can eliminate the luck factor by posting your
> question to a group where those dotnet-savvy people hang out. I suggest
> microsoft.public.dotnet.framework.aspnet.
>
> However, I'll take a stab at it (I've just started using .Net)
>
>
> Rod wrote:
> > I have a client site where the code below has been working happily
> > for at least four months. Last week an error appeared related to the
> > following SQL statement.
> >
> > The site is using SQL Server 7. The code is ASP.NET
> >
> > An example of the SQL statement is:
> >
> > 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 not 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
> >
>
> Well, you're right. This error seems to be totally unrelated to this
> statement. There is no subquery here.
>
> Is the error reproducible? Try using SQL Profiler to trace what's actually
> happening.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

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

Thanks for the response Raymond.

I had done that which is where the example statement came from.

Rod.


"Raymond D'Anjou" wrote in message
news:uKSDUZIXFHA.1404@TK2MSFTNGP09.phx.gbl...
> Try modifying yor Sub:
> > 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)
> response.write strSQL
> response.end
> > cmd.Connection.Open()
> > cmd.ExecuteNonQuery()
> > conn.Close()
> > End Sub
>
> Copy the result and run it in your database.
>
> "Rod" <1gandalf@newsgroup.nospam> wrote in message
> news:%23RRkgMIXFHA.3584@TK2MSFTNGP14.phx.gbl...
> >I have a client site where the code below has been working happily for at
> > least four months. Last week an error appeared related to the following
> > SQL
> > statement.
> >
> > The site is using SQL Server 7. The code is ASP.NET
> >
> > An example of the SQL statement is:
> >
> > 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 not 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.
> >
> >
>
>

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

Are "OrderItems" and "Basket" all tables or views? Also, I suggest you may
trace the command with SQL profiler to see the exact command sent to SQL by
your ASP.NET app, and try to execute in Query Analyzer.

Luke