| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Social distancing? Try a better way to work remotely on your online files. Dokkio, a new product from PBworks, can help your team find, organize, and collaborate on your Drive, Gmail, Dropbox, Box, and Slack files. Sign up for free.

View
 

Lecture - How To - Get an Insert Identity from a SqlDataSource

Page history last edited by Dr. Ron Eaglin 8 years ago

 Getting an Insert Identity from a SQL Data Source

 

 

Prerequisites

 

This is part of the Category Codes Case Study. Follow these video's in order at Case Study - Category Codes

 

Summary

 

Demonstrates using a SQLDataSource to insert data into a stored procedure and retrieve the identity of an insert in that stored procedure. Technique can be used to get any value as an output parameter of the stored procedure. The value is then passed to a second form that uses it.

 

Video 

 

http://online1.daytonastate.edu/player2.php?id=c5dc3e08849bec07e33ca353de62ea04 

 

Code

 

InsertCategory.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="InsertCategory.aspx.cs" Inherits="TraumaFlow.CategoryManagement.InsertCategory" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Enter Category</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Enter Category</h1>
    <br />
     Every field in the form is a Category. A Category can have codes if the user is limited
     to specific choices they can make, or it can have a user entered value. Choices are 
     displayed in drop down lists, where user enterd values are entered through a text box.
     <br /><br />
    <table><tr>
    <td>Enter Category Name</td><td>
        <asp:TextBox ID="tbCategory" runat="server"></asp:TextBox></td>
    </tr></table>
        <asp:Button ID="btnSubmit" runat="server" Text="Submit and Enter Another Category" 
            onclick="btnSubmit_Click" />
             <asp:Button ID="btnSubmitCodes" runat="server" 
            Text="Submit and Enter Codes" onclick="btnSubmitCodes_Click" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
            SelectCommand="sp_InsertCategory"             
            SelectCommandType="StoredProcedure"  OnSelected="SqlDataSource1_OnSelected">            
            <SelectParameters>
                <asp:ControlParameter ControlID="tbCategory" Name="DisplayText" 
                    PropertyName="Text" Type="String" />
                <asp:Parameter Direction="Output" Name="Identity" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
        <br />
    </div>
    <hr />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="id" DataSourceID="SqlDataSource2" CellPadding="4" 
        ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" 
                ReadOnly="True" SortExpression="id" />
            <asp:BoundField DataField="NamespaceID" HeaderText="NamespaceID" 
                SortExpression="NamespaceID" />
            <asp:BoundField DataField="DisplayText" HeaderText="DisplayText" 
                SortExpression="DisplayText" />
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
        SelectCommand="SELECT * FROM [Categories]"></asp:SqlDataSource>
    </form>
</body>
</html>
 
InsertCategory.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace TraumaFlow.CategoryManagement
{
    public partial class InsertCategory : System.Web.UI.Page
    {
        private string CategoryID;
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            SqlDataSource1.Select(DataSourceSelectArguments.Empty);
            tbCategory.Text = String.Empty;
            GridView1.DataBind();
        }
        protected void SqlDataSource1_OnSelected(object sender, SqlDataSourceStatusEventArgs e)
        {
            System.Data.Common.DbCommand command = e.Command;
            Session.Add("CategoryID", command.Parameters["@Identity"].Value.ToString());
            CategoryID = command.Parameters["@Identity"].Value.ToString();
        }
        protected void btnSubmitCodes_Click(object sender, EventArgs e)
        {
            SqlDataSource1.Select(DataSourceSelectArguments.Empty);
            Response.Redirect("InsertCode.aspx?CategoryID="+CategoryID);
        }        
    }
} 
InsertCode.aspx
<%@ Page Language="C#" Title="Insert/Update Codes" AutoEventWireup="true" CodeBehind="InsertCode.aspx.cs" Inherits="TraumaFlow.CategoryManagement.InsertCode" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Insert Code</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1 style="text-align:center"></h1>
    <table><tr>
    <td>Select Category</td>
    <td>
        <asp:DropDownList ID="ddlSelectCategory" runat="server" 
            DataSourceID="SqlDataSource1" DataTextField="DisplayText" 
            DataValueField="id" AutoPostBack="True" 
            onselectedindexchanged="ddlSelectCategory_SelectedIndexChanged">
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
            SelectCommand="SELECT * FROM [Categories]"></asp:SqlDataSource>
    </td>
    </tr>
    <tr>
    <td>Enter Code</td><td>
        <asp:TextBox ID="tbCode" runat="server"></asp:TextBox></td>
        </tr>
    </table>
        <asp:Button ID="btnSubmit" runat="server" Text="Submit" 
            onclick="btnSubmit_Click" />
        <asp:Button ID="btnUpdate" runat="server" Text="Update" 
            onclick="btnUpdate_Click" Visible="False" />
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
            SelectCommand="sp_InsertCode" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="ddlSelectCategory" Name="CategoryID" 
                    PropertyName="SelectedValue" Type="Int32" />
                <asp:ControlParameter ControlID="tbCode" Name="DisplayText" PropertyName="Text" 
                    Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource4" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
            SelectCommand="sp_UpdateCode" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:SessionParameter Name="CodeID" SessionField="CodeID" Type="Int32" />
                <asp:ControlParameter ControlID="tbCode" Name="DisplayText" PropertyName="Text" 
                    Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>
        <hr />
        <asp:GridView ID="gvCodes" runat="server" AutoGenerateColumns="False" 
            CellPadding="4" DataKeyNames="CategoryID,CodeID" DataSourceID="SqlDataSource3" 
            EnableModelValidation="True" ForeColor="#333333" GridLines="None" OnRowCommand="gvCodes_RowCommand">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
                    InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" />
                <asp:BoundField DataField="CodeID" HeaderText="CodeID" InsertVisible="False" 
                    ReadOnly="True" SortExpression="CodeID" />
                <asp:BoundField DataField="Category" HeaderText="Category" 
                    SortExpression="Category" />
                <asp:BoundField DataField="Code" HeaderText="Code" SortExpression="Code" />  
                <asp:TemplateField>
                <ItemTemplate>
                <asp:LinkButton runat="server" ID="lbEdit" Text="Edit This Code" CommandName="editinform" 
                CommandArgument="<%# ((GridViewRow) Container).RowIndex %>"></asp:LinkButton>
                </ItemTemplate>
                </asp:TemplateField>                             
            </Columns>
            <EditRowStyle BackColor="#2461BF" />
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#EFF3FB" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" SelectCommand="SELECT Categories.id AS 'CategoryID', Codes.id AS 'CodeID', Categories.DisplayText AS 'Category', Codes.DisplayText AS 'Code' FROM Categories INNER JOIN Codes ON Categories.id = Codes.CategoryID
WHERE Categories.id = @CategoryID">
            <SelectParameters>
                <asp:ControlParameter ControlID="ddlSelectCategory" Name="CategoryID" 
                    PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>
InsertCode.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace TraumaFlow.CategoryManagement
{
    public partial class InsertCode : System.Web.UI.Page
    {
        public int CodeID()
        {
            if (Session["CodeID"] != null)
            {
                return Convert.ToInt32(Session["CodeID"]);
            }
            else
            { return 0; }
        }
        public int CategoryID()
        {
            if (Request["CategoryID"] != null)
            {
                return Convert.ToInt32(Request["CategoryID"]);
            }
            else
            { return 0; }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (CategoryID() != 0)
                {
                    ddlSelectCategory.SelectedValue = Convert.ToString(CategoryID());
                }
            }
        }
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            SqlDataSource2.Select(DataSourceSelectArguments.Empty);
            tbCode.Text = string.Empty;
            gvCodes.DataBind();
        }
        protected void ddlSelectCategory_SelectedIndexChanged(object sender, EventArgs e)
        {
            tbCode.Text = string.Empty;
            gvCodes.DataBind();
        }
        protected void gvCodes_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            int index = Convert.ToInt32(e.CommandArgument);
            if (e.CommandName == "editinform")
            {
                GridViewRow row = gvCodes.Rows[index];
                tbCode.Text = row.Cells[3].Text;
                Session.Add("CodeID", row.Cells[1].Text);
                btnSubmit.Visible = false;
                btnUpdate.Visible = true;
            }
        }
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            SqlDataSource4.Select(DataSourceSelectArguments.Empty);
            tbCode.Text = string.Empty;
            gvCodes.DataBind();
            btnSubmit.Visible = true;
            btnUpdate.Visible = false;
            Session.Remove("CodeID");
        }
    }
}

Stored Procedure

sp_InsertCategory

ALTER PROCEDURE [dbo].[sp_InsertCategory] (
   @DisplayText varchar(200),
   @Identity INT OUTPUT
   )
AS
BEGIN
 
  DECLARE @N INT
  SELECT @N = COUNT(*)    FROM Categories 
                        WHERE NamespaceID = 1
                        AND DisplayText = @DisplayText
                        
IF @N = 0                        
  BEGIN
  INSERT INTO Categories
    (NamespaceID, DisplayText)
  VALUES
    (1, @DisplayText)
  END  
  SELECT @Identity = @@IDENTITY  
 
END 

 

 

 

Reference Materials

 

The OnSelected event handler for a SqlDataSource - http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selected.aspx 

 

All events associated with a SqlDataSource (used in this video) - http://msdn.microsoft.com/en-us/library/5b6ksy3z 

 

It is also good to know and understand the DbCommand object (used in this video) - http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx 

 

 

Additional Information

 

 

COP 4834 Lectures Page

ALTER PROCEDURE [dbo].[sp_InsertCategory] (
   @DisplayText varchar(200),
   @Identity INT OUTPUT
   )
AS
BEGIN
 
  DECLARE @N INT
  SELECT @N = COUNT(*)    FROM Categories
                        WHERE NamespaceID = 1
                        AND DisplayText = @DisplayText
                        
IF @N = 0                        
  BEGIN
  INSERT INTO Categories
    (NamespaceID, DisplayText)
  VALUES
    (1, @DisplayText)
  END  
  SELECT @Identity = @@IDENTITY  
 
END

Comments (0)

You don't have permission to comment on this page.