Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

Retrieving Return Value from Stored Procedure using SqlDataSource

I've got a sqlDataSource that's running a stored procedure on the INSERT event.  How do I get to the return value that's coming back from my DB?  I'm assuming it's in the SqlDataSource1_Inserted event, but am not sure of the syntax.

Regards,
eq
0
equentin
Asked:
equentin
  • 5
  • 5
1 Solution
 
aprestoCommented:
Hi equentin,

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

Apresto
0
 
aprestoCommented:
apologies:  its @resultCode
0
 
equentinAuthor Commented:
Ok, thanks - but how do I code that in my event handler?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
aprestoCommented:
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
0
 
aprestoCommented:
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
0
 
equentinAuthor Commented:
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.
0
 
equentinAuthor Commented:
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....
0
 
equentinAuthor Commented:
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
0
 
aprestoCommented:
hi there, apologies for the delay:

try this:

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

i just added .ToString()

the return is null for som ereason, have you tested the stored proc in query analyser?
0
 
equentinAuthor Commented:
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! ;-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now