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: 425
  • Last Modified:

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

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

0
bham3dman
Asked:
bham3dman
1 Solution
 
Munawar HussainPrincipal Software EngineerCommented:
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

0
 
bham3dmanAuthor Commented:
Sorry for the delayed response.  Thanks for the assistance.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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