Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Retrieving Return Value from Stored Procedure using SqlDataSource

Posted on 2006-07-18
10
672 Views
Last Modified: 2009-12-16
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
Comment
Question by:equentin
  • 5
  • 5
10 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17130065
Hi equentin,

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

Apresto
0
 
LVL 23

Expert Comment

by:apresto
ID: 17130075
apologies:  its @resultCode
0
 

Author Comment

by:equentin
ID: 17130084
Ok, thanks - but how do I code that in my event handler?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 23

Expert Comment

by:apresto
ID: 17130129
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
 
LVL 23

Expert Comment

by:apresto
ID: 17130150
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
 

Author Comment

by:equentin
ID: 17130616
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
 

Author Comment

by:equentin
ID: 17132112
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
 

Author Comment

by:equentin
ID: 17132440
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
 
LVL 23

Accepted Solution

by:
apresto earned 500 total points
ID: 17134257
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
 

Author Comment

by:equentin
ID: 17138639
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question