locked
DataSet always returning null output parameter RRS feed

  • Question

  • User-584754365 posted

    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(
    
    @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 

    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 

    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

    Have a nice day

    Tuesday, December 11, 2012 1:40 PM

Answers

  • User3866881 posted

    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>
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 12, 2012 8:06 AM