INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Database

Using Stored Procedures and Output Parameters with the SqlDataControl by bigfoot
Posted: 29 Sep 08


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!











 

Back to Microsoft: ASP.NET FAQ Index
Back to Microsoft: ASP.NET Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close