Discussions on Microsoft technologies: Gridview update with sqldatasource using stored procedure

Hi,
Here i have a complete example of how to use the sqldatasource and gridview to update the records using the stored procedure.
Aspx page with code:
<%@ Page Language="C#" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
    protected void grvEmployees_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string employeeID = grvEmployees.DataKeys[e.RowIndex].Value.ToString();
 
        string gender = ((DropDownList)grvEmployees.Rows[e.RowIndex].FindControl("DropDownList1")).SelectedValue.ToString();
 
        empDataSource.UpdateParameters["Gender"].DefaultValue = gender;
        empDataSource.UpdateParameters["EmployeeID"].DefaultValue = employeeID.ToString();
        empDataSource.Update();
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Dropdownlist in EditItemTemplate</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="grvEmployees" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID"
            DataSourceID="empDataSource" AllowPaging="true" PageSize="10" AutoGenerateEditButton="True"
            OnRowUpdating="grvEmployees_RowUpdating">
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <%# Container.DataItemIndex + 1 %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="EmployeeID" InsertVisible="False" SortExpression="EmployeeID">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("EmployeeID") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Gender" SortExpression="Gender">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Gender") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# DataBinder.Eval(Container.DataItem,"Gender") %>'>
                            <asp:ListItem Value="M">M</asp:ListItem>
                            <asp:ListItem Value="F">F</asp:ListItem>
                        </asp:DropDownList>
                    </EditItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
 
    </div>
    <asp:SqlDataSource ID="empDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
        SelectCommand="SELECT [EmployeeID], [Gender] FROM [AdventureWorks].[HumanResources].[Employee]"
        UpdateCommand="[HumanResources].[dbo.uspUpdateEmployeeGender]"
        UpdateCommandType="StoredProcedure">
        <UpdateParameters>
            <asp:Parameter Type="Char" Name="Gender"></asp:Parameter>
            <asp:Parameter Name="EmployeeID"></asp:Parameter>
        </UpdateParameters>
    </asp:SqlDataSource>
    </form>
</body>
</html>

Srored procedure:

USE [AdventureWorks]GO/****** Object: StoredProcedure [HumanResources].[dbo.uspUpdateEmployeeGender] Script Date: 04/08/2011 12:34:21 ******/SET
GO
SET
GO ANSI_NULLS ON QUOTED_IDENTIFIER ON-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================CREATE
@EmployeeID [int]
@Gender [nchar] PROCEDURE [HumanResources].[dbo.uspUpdateEmployeeGender], (1) AS
BEGIN
UPDATE [HumanResources].[Employee] SET [Gender] = @Gender WHERE [EmployeeID] = @EmployeeID;END
GO
Happy coding...
Discussions on Microsoft technologies

Friday, April 8, 2011

Gridview update with sqldatasource using stored procedure

1 comment:
