Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Stored Procedures and Output Parameters with the SqlDataControl

Database

Using Stored Procedures and Output Parameters with the SqlDataControl

by  bigfoot  Posted    (Edited  )

Hello All
I was asked to put together a quick and dirty data input page. It took me 10 minutes to wire it up and an hour to find the solution, so I am passing it on.

The SQLDataControl is a pretty neat component, but you have to know it's little problems. One I found was it's inability to auto generate an output parameter.
It needed to be coded by hand.

I wanted to use a DetailsView, connected to a SqlDataSource that would call a Stored Procedure to insert a record and retrieve the Identity of the last inserted record.
After researching the subject, I decided to use IDENT_CURRENT('tablename') and not @@Identity.

It seems if your table contains Triggers, @@Identity could return the wrong value.
Reference:
http://msdn.microsoft.com/en-us/library/ms175098.aspx

I created a simple table for generating customer names, with 3 fields.
FName, LName and ID as an Identity column of type int.

Code: 
CREATE TABLE [dbo].[Customers] (
	[Customer_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[FName] [varchar] (50),
	[LName] [varchar] (50)
)

Then a Stored Procedure to add the data:
Code: 
CREATE PROCEDURE AddCustomer
(
@FName varchar(50),
@LName varchar(50),
@CID int OUTPUT
) AS

SET NOCOUNT ON

INSERT INTO Customers
	  (FName, LName) 
VALUES (@FName, @LName)


SELECT @CID = IDENT_CURRENT('Customers')
GO
Note the OUTPUT Parameter called @CID. This holds the Identity value for the inserted record.

It was easy to check to be sure this worked using Query manager.
Code: 
declare @ret int
Exec AddCustomer 'Steve', 'Smith', @ret output
print @ret


Now to get it to work in an ASP.NET screen using the SqlDataSource. This took a bit to figure out but here is the code.
Code: 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CustomerDatabaseConnectionString %>"
        SelectCommand="Select FName, LName from Complaints" InsertCommandType="StoredProcedure">

        InsertCommand="AddCustomer"
        <InsertParameters>
          <asp:Parameter Name="FName" Type="String" />
          <asp:Parameter Name="LName" Type="String" />
          <asp:Parameter Direction="Output" Name="CID" Type="Int32" />
        </InsertParameters>
      </asp:SqlDataSource>

The real trick here is the CID parameter, of type OUTPUT, the same as you would code it in Sql Query Manager.

But how do we get the returned value? There seems to be no way.
Using the SqlDataSource1.Inserted event, we can capture the output parameter like this:
Code: 
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

    'Parameter needs a @ before it
    Dim sCID As String = e.Command.Parameters("@CID").Value.ToString()

'Here you can assign it to any variable.  Even a label.
 Label2.Text = sCID

  End Sub


Then I coded the DetailsView code:
Code: 
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
        DataSourceID="SqlDataSource1" Height="172px" Width="304px" DefaultMode="Insert">
        <Fields>
          <asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" />
          <asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" />
          <asp:CommandField ShowInsertButton="True" />
        </Fields>
      </asp:DetailsView>

I hope this will help most of you, and I'm sorry it's in VB format and not C#.

Happy Coding!











Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top