ExecuteNonQuery in SQL 2005

I have an ASP.Net page that runs the following command which is giving me
the following SQL error:
The statement has been terminated.
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The code in the page is as follows:
strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
Request.Form("cboFileTypeID") & ", 0"
cmd = New OleDbCommand(strSQL, conFileData)
lngRows = cmd.ExecuteNonQuery()
It is failing on the last statement and I don't know why. Also, below is
the stored proc code. Can anyone help? Thanks
David
CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
(
@.FileNumber int,
@.FileTypeID int,
@.Rows int output
)
AS
/* SET NOCOUNT ON */
INSERT INTO FileTasks
(FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
AssignedID)
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
RETURN @.RowsTry executing the stored procedure directly from query analyzer and see if
you get the same error?
"David" <dlchase@.lifetimeinc.com> wrote in message
news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have an ASP.Net page that runs the following command which is giving me
> the following SQL error:
> The statement has been terminated.
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The code in the page is as follows:
> strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
> Request.Form("cboFileTypeID") & ", 0"
> cmd = New OleDbCommand(strSQL, conFileData)
> lngRows = cmd.ExecuteNonQuery()
> It is failing on the last statement and I don't know why. Also, below is
> the stored proc code. Can anyone help? Thanks
> David
>
> CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
> (
> @.FileNumber int,
> @.FileTypeID int,
> @.Rows int output
> )
> AS
> /* SET NOCOUNT ON */
> INSERT INTO FileTasks
> (FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
> AssignedID)
> SELECT @.FileNumber, TaskTitle,
> DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
> CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
> FROM FileTaskDefaults
> WHERE FileTypeID = @.FileTypeID
> RETURN @.Rows
>|||Yes, same error.
David
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
> Try executing the stored procedure directly from query analyzer and see if
> you get the same error?
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> subquery
> expression.
>|||Is FileTaskDefaults a view?
Are there any triggers on FileTasks?
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
> Yes, same error.
> David
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
>|||It doesn't look to me that your output parameter is ever assigned to
any value. Don't you need something like this
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
-- assign row count to @.Rows
SELECT @.Rows = @.@.Rowcount|||> RETURN @.Rows
Where does this value get populated? Do you want to use a RETURN, OUTPUT,
or both? I suggest sticking to output parameters for this kind of data, and
not using RETURN. RETURN is meant to return a status code (e.g.
success/failure), not data. This is one of the reasons they're limited to
INTeger datatypes.
A|||That was it! I removed the trigger and it worked. Thank you.
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23%236acDUbGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Is FileTaskDefaults a view?
> Are there any triggers on FileTasks?
> David
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||> That was it! I removed the trigger and it worked. Thank you.
I don't see how that is possible, unless either
(a) you didn't post all of the stored procedure code in your original post,
or
(b) you eliminated the error, but you aren't actually verifying that the
stored procedure is correctly returning the rowcount.
Anyway, it sounds like your trigger was written expecting only single-row
row modifications. You should re-visit that logic instead of just throwing
the trigger away, especially if the trigger is not yours and you are not
sure what it was doing.|||David (dlchase@.lifetimeinc.com) writes:
> That was it! I removed the trigger and it worked. Thank you.
And the trigger did not serve any purpose? Yeah, maybe it was just an
old relic, but I get nervous when I hear things like this. Just because
you did not get any error message, does not mean that it worked. If you
removed a trigger that performed some important task to maintain database
integrity, I would not call that working...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hopefully the OP removed to trigger temporarily just to confirm that was the
issue and is rewriting the trigger to correct the subquery.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B6D62F991A4Yazorman@.127.0.0.1...
> David (dlchase@.lifetimeinc.com) writes:
> And the trigger did not serve any purpose? Yeah, maybe it was just an
> old relic, but I get nervous when I hear things like this. Just because
> you did not get any error message, does not mean that it worked. If you
> removed a trigger that performed some important task to maintain database
> integrity, I would not call that working...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx