Last post Dec 12, 2012 08:06 AM by Decker Dong - MSFT
Dec 11, 2012 01:40 PM|_wakamohle|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.
CREATE PROCEDURE SpInsertTblmain(
@idtblmain int OUTPUT
DECLARE @idtblmaintemp INT
INSERT INTO tblmain (column1) VALUES (@mainparam)
SET @idtblmaintemp = @@IDENTITY
EXEC SpInsertTblchild1 @idtblmaintemp, @childparam1
SET @idtblmain = @idtblmaintemp
When I test the functionality of the stored procedure in TSQL, it works fine if I put the OUTPUT clause aside the output parameter
DECLARE @newIdMain INT; EXEC SpInsertTblmain 'main text', 'child text', @newIdMain OUTPUT; SELECT @newIdMain;
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
Dec 12, 2012 08:06 AM|Decker Dong - MSFT|LINK
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"
<html xmlns="http://www.w3.org/1999/xhtml" >
<title>ObjectDataSource - C# Example</title>
<form id="Form1" method="post" runat="server">
<asp:Parameter Name="……" Direction="Output"/> </selectparameter>