I have this strange case. I use a complex stored procedure that inserts in the main table, then stores the inserted IDENTITY in a temp variable an calls other stored procedures to insert in some children tables with the main table IDENTITY as a foreign key,
then the main stored procedure returns the main table IDENTITY as an output parameter.
CREATE PROCEDURE SpInsertTblmain(
@mainparam varchar(50),
@childparam1 varchar(40),
@idtblmain int OUTPUT
)
AS
DECLARE @idtblmaintemp INT
INSERT INTO tblmain (column1) VALUES (@mainparam)
SET @idtblmaintemp = @@IDENTITY
EXEC SpInsertTblchild1 @idtblmaintemp, @childparam1
SET @idtblmain = @idtblmaintemp
GO
When I test the functionality of the stored procedure in TSQL, it works fine if I put the OUTPUT clause aside the output parameter
If I don't put the "OUTPUT" clause, I get Null as the value of @newIdMain, which is fine. I also know there is no need for the temp variable... but it's there just in case
But, the problem comes when I try to use this stored procedure as an InsertMethod of an ObjectDataSource based on a TableAdapter from a DataSet in a xsd file. Althoug I've put the @newIdMain as an Output parameter for the ObjectDataSource and for the DataSet storedprocedure call, the commandstring of the storedprocedure call does not include the "output" clause so I always get a null value on the output parameter which is bad, because I use that parameter for many other things.
My question here is what do I have to change in the TableAdapter to ensure the "output" clause is set or otherwise how can I override the SQL command execution so I can add the "output" clause programatically.
I should say that, it worked fine until I put the
EXEC SpInsertTblchild1
inside the main stored procedure
I hope there is a solution that does not imply building the Insert clause as parametrized string, because my real stored procedure has more than 40 parameters
Thank you in advance for your attention and your help
As far as I see, I think you should define the full names of the parameters in the ObjectDataSource. Something looks like this below
by setting Direction=Output
_wakamohle
Member
6 Points
6 Posts
DataSet always returning null output parameter
Dec 11, 2012 05:40 PM|LINK
Hello Asp team,
I have this strange case. I use a complex stored procedure that inserts in the main table, then stores the inserted IDENTITY in a temp variable an calls other stored procedures to insert in some children tables with the main table IDENTITY as a foreign key, then the main stored procedure returns the main table IDENTITY as an output parameter.
When I test the functionality of the stored procedure in TSQL, it works fine if I put the OUTPUT clause aside the output parameter
If I don't put the "OUTPUT" clause, I get Null as the value of @newIdMain, which is fine. I also know there is no need for the temp variable... but it's there just in case
But, the problem comes when I try to use this stored procedure as an InsertMethod of an ObjectDataSource based on a TableAdapter from a DataSet in a xsd file. Althoug I've put the @newIdMain as an Output parameter for the ObjectDataSource and for the DataSet storedprocedure call, the commandstring of the storedprocedure call does not include the "output" clause so I always get a null value on the output parameter which is bad, because I use that parameter for many other things.
My question here is what do I have to change in the TableAdapter to ensure the "output" clause is set or otherwise how can I override the SQL command execution so I can add the "output" clause programatically.
I should say that, it worked fine until I put the
inside the main stored procedure
I hope there is a solution that does not imply building the Insert clause as parametrized string, because my real stored procedure has more than 40 parameters
Thank you in advance for your attention and your help
Have a nice day
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: DataSet always returning null output parameter
Dec 12, 2012 12:06 PM|LINK
Hello,
As far as I see, I think you should define the full names of the parameters in the ObjectDataSource. Something looks like this below by setting Direction=Output
<%@ Register TagPrefix="aspSample" Namespace="Samples.AspNet.CS" Assembly="Samples.AspNet.CS" %> <%@ Page language="c#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title>ObjectDataSource - C# Example</title> </head> <body> <form id="Form1" method="post" runat="server"> <asp:gridview id="GridView1" runat="server" datasourceid="ObjectDataSource1" /> <asp:objectdatasource id="ObjectDataSource1" runat="server" selectmethod="GetAllEmployees" typename="Samples.AspNet.CS.EmployeeLogic"> <selectparameter> <asp:Parameter Name="……" Direction="Output"/> </selectparameter> <asp:objectdatasource> </form> </body> </html>