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

Posted on 2009-02-12
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


End With

Open in new window

Question by:bham3dman
    LVL 12

    Accepted Solution

    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

            ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
            SelectCommand="SELECT * FROM Employees"
            InsertCommandType = "StoredProcedure"
            OnInserted ="On_Inserted"
    '''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 
        -- 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
            INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)
            SELECT @EmployeeID= @@IDENTITY
    '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

    Open in new window

    LVL 5

    Author Closing Comment

    Sorry for the delayed response.  Thanks for the assistance.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now