How do I capture inputoutput parameter value after a Formview Insert template runs stored procedure in .NET 2.0?

Question

I have a formview that executes a insert stored procedure through a table adapter as normally done. Except when the insert fails a output parameter on the sp called error_msg is set to "Insert failed because XXXX is required". how do I capture this value and print it on the screen for the user? I can't seem to find locate it.

Answer 1

Some details are missing, like if you are using an ObjectDataSource, which database your are using, etc.

Well here is a sample to retrive output parameter for SQL Server.

bind a datasource to your FormView

<asp:ObjectDataSource ID="myDataSource" runat="server" 
       InsertCommand="myProcedure" 
       InsertCommandType="StoredProcedure"
       Inserted="myDataSource_Inserted">
  <InsertParameters>
    <asp:Parameter Name="returnValue" Type="String" Direction="InputOuput" />
    <asp:Parameter Name="myField1" Type="Int32" />
    <asp:Parameter Name="myField2" Type="String" />
  </InsertParameters>
</asp:ObjectDataSource>

and create in SQL Server a procedure

CREATE PROCEDURE myProcedure 
(
   @myField1 int,
   @myField2 varchar(50),
   @returnValue varchar(100) OUT 
)

AS

BEGIN

   INSERT INTO myTable VALUES (@myField1, @myField2)

   IF @@ERROR <> 0 
      SET @returnValue='Insert failed because XXXX is required'
END

Object datasource also has a event method you can use to retrieve parameter information

protected void myDataSource_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
    string returnValue = Convert.ToString(e.ReturnValue);
    //your code here...
}