We help IT Professionals succeed at work.

How do I retrieve a ReturnValue from a stored procedure using a SQLDataSource control?

Medium Priority
450 Views
Last Modified: 2013-11-26
Greetings Experts,

I'm using a stored procedure that returns a record ID upon inserting new record data.  What is the easiest way to retrieve the ReturnValue from the stored procedure using a SQLDataSource control?

I've found the following code for inserting values (which seems easy enough) but I can't figure out how to retrieve the ReturnValue and store this value in a variable.  

Thanks for your help!
With sqldatasource1
                .InsertParameters("@Products").DefaultValue = Product
                .InsertParameters("@Manufacturer").DefaultValue = Manufacturer
                .InsertParameters("@Description").DefaultValue = Description
                .Insert()
End With

Open in new window

Comment
Watch Question

Principal Software Engineer
CERTIFIED EXPERT
Commented:
hi ..

you need to add an output parameter and then collection returned value from that parameter in On_inserted event

below is the code Sample



'''SQL DATASOURCE WITH EVENTS AND OUTPUR PARAMENTER
 
  <asp:SqlDataSource
        id="SqlDataSource2"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
        SelectCommand="SELECT * FROM Employees"
        InsertCommandType = "StoredProcedure"
        InsertCommand="SP_InsertEmployee"        
        OnInserting="On_Inserting"
        OnInserted ="On_Inserted"
       
      </asp:SqlDataSource>
 
 
'''in codebehind ..
 
 
Sub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
 
    Dim PkValue As SqlParameter  
    PkValue  = New SqlParameter("@EmployeeID", SqlDbType.Int)
    PkValue .Direction    = ParameterDirection.Output     
 
    e.Command.Parameters.Add(PkValue )
 
 End Sub 
 
'''collect in the event below
 
 Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
    Dim command As DbCommand 
    command = e.Command
 
    ' The label displays the primary key of the recently inserted row.
    Label1.Text = command.Parameters("@EmployeeID").Value.ToString()    
 
     End Sub 
 
 
 
 
''YOUR SP MAY LOOK LIKE
 
    -- the primary key of the row that was inserted in an OUT parameter.
     CREATE PROCEDURE SP_InsertEmployee" 
        @FirstName nvarchar(10), 
        @LastName nvarchar(20) , 
        @Title nvarchar(30), 
        @Notes nvarchar(200), 
        @EmployeeID int OUTPUT
      AS
        INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)
        SELECT @EmployeeID= @@IDENTITY
           
      GO
 
 
'HERE EmployeeID is primary key of the table with identity set to true .. so with every insertion a new value is generated and returned 
 
 
this is just a sample.. I have not add all parameters. just the way you can do this
 
thanks

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Sorry for the delayed response.  Thanks for the assistance.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.