Retrieving Return Value from Stored Procedure using SqlDataSource

Hi equentin,

you can call it with the @ReturnCode parameter in the command object

Apresto

apologies:  its @resultCode

Ok, thanks - but how do I code that in my event handler?

what code do you have at the moment?

you could just add it as a parameter, at a guess .Paramaters.Add("@resultcode", SqlDbType.varchar....

but we'll work on your code

actually, apologies if i confuse you but i think you can just assign the value of ExecuteNonquery to a variable and it should assign the returned value to that variable.  Eitehr way post your code and we'll go from there

There isn't any code to post, because I haven't written it yet.  Sorry if my question isn't clear, let me try again.

I have a SqlDataSource object bound to a DetailsView which I want to use to insert data to my DB using a stored procedure.  This works fine, the data goes into the database, but my SP returns a reuturn code which I want to access in .NET and I don't know how.  I'm assuming in the event handler for the data source in the Inserted event I can access the return value.

Okay, I'm getting a bit closer, but still not quite there.  I've got the following for my sqlDataSource:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
        InsertCommand="spInsertNews" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource1_Inserted" SelectCommand="SELECT * FROM [News] WHERE ([pk_NewsID] = @pk_NewsID)">
        <InsertParameters>
            <asp:Parameter Name="NewsDate" Type="DateTime" />
            <asp:Parameter Name="Headline" Type="String" />
            <asp:Parameter Name="Summary" Type="String" />
            <asp:Parameter Name="Active" Type="Boolean" />
            <asp:Parameter Name="Archive" Type="Boolean" />
            <asp:Parameter Name="NewsCopy" Type="String" />
            <asp:Parameter Direction="Output" Name="myID" Type="Int32" Size="4" />            
        </InsertParameters>
        <SelectParameters>
            <asp:QueryStringParameter DefaultValue="0" Name="pk_NewsID" QueryStringField="id"
                Type="Int32" />
        </SelectParameters>        
    </asp:SqlDataSource>

Then my stored procedure returns the ID of the inserted row.  I've got the following in the sqlDataSource insert event:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

        Response.Write("Record Inserted: " & Server.HtmlEncode(e.Command.Parameters("@pk_NewsID").Value) & "<br/>")

End Sub

But I get the following error:
Conversion from type 'DBNull' to type 'String' is not valid.

Which would seem to appear that my referenced return value is NULL rather than containing the id....

Sorry, should be:

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

        Response.Write("Record Inserted: " & Server.HtmlEncode(e.Command.Parameters("@myID").Value) & "<br/>")

End Sub

Have finally worked out what it is, I needed to change my output parameter in the <insertparameters>

<asp:Parameter Direction="ReturnValue" Name="myID" Type="Int32" Size="4" DefaultValue="0" />

I've marked your answer as accepted.  Feel free to help with my other sqlDataSource question I've just asked though! ;-)