Add, Update, Delete Records in a Gridview using SqlDataSource

Add, Update, Delete Records in a Gridview using SqlDataSource

http://baimey.wordpress.com/2010/03/08/add-update-delete-records-in-a-gridview-using-sqldatasource/

By default, the GridView control doesn’t have support for inserting new records. However you can use the built-in edit or delete functionality of the GridView control. Let us explore how to insert new records and Update and Delete existing records in Gridview. Just copy and paste the code in your project. We will be using the ‘Categories’ table in the ‘Northwind’ database.

GridView.aspx

        <asp:GridView
ID=”GridView1″
runat=”server”
AutoGenerateColumns=”False”
DataKeyNames=”CategoryID”

            DataSourceID=”SqlDataSource1″
ShowFooter=”true”
AllowPaging=”True”
AllowSorting=”True”
OnRowCommand=”GridView1_RowCommand”>

            <Columns>

          
 

                <asp:CommandField
ShowDeleteButton=”True”
ShowEditButton=”True”/>                

                <asp:TemplateField
HeaderText=”CategoryID”
InsertVisible=”False”
SortExpression=”CategoryID”>

                    <EditItemTemplate>

                        <asp:Label
ID=”Label1″
runat=”server”
Text=’<%# Eval(“CategoryID”) %>‘></asp:Label>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label1″
runat=”server”
Text=’<%# Bind(“CategoryID”) %>‘></asp:Label>

                    </ItemTemplate>                   

                </asp:TemplateField>

                <asp:TemplateField
HeaderText=”CategoryName”
SortExpression=”CategoryName”>

                    <EditItemTemplate>

                        <asp:TextBox
ID=”TextBox1″
runat=”server”
Text=’<%# Bind(“CategoryName”) %>‘></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label2″
runat=”server”
Text=’<%# Bind(“CategoryName”) %>‘></asp:Label>

                    </ItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox
ID=”CategoryNameTextBox”
Runat=”server”></asp:TextBox>

                        </FooterTemplate>

                </asp:TemplateField>

                <asp:TemplateField
HeaderText=”Description”
SortExpression=”Description”>

                    <EditItemTemplate>

                        <asp:TextBox
ID=”TextBox2″
runat=”server”
Text=’<%# Bind(“Description”) %>‘></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label
ID=”Label3″
runat=”server”
Text=’<%# Bind(“Description”) %>‘></asp:Label>

                    </ItemTemplate>

                    <FooterTemplate>

                        <asp:TextBox
ID=”DescriptionTextBox”
Runat=”server”></asp:TextBox>

                    </FooterTemplate>                  

                </asp:TemplateField>

                <asp:templatefield>                   

                        <footertemplate>

                              <asp:linkbutton
id=”btnNew”
runat=”server”
commandname=”New”
text=”New”
/>

                        </footertemplate>

                  </asp:templatefield>

              
 

            </Columns>

        </asp:GridView>

//

        <asp:SqlDataSource
ID=”SqlDataSource1″
runat=”server”
ConnectionString=”Data Source=SUPROTIM;Initial Catalog=Northwind;Integrated Security=True”

            DeleteCommand=”DELETE FROM [Categories] WHERE [CategoryID] = @CategoryID”
InsertCommand=”INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)”

            ProviderName=”System.Data.SqlClient”
SelectCommand=”SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]“

            UpdateCommand=”UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = @Description WHERE [CategoryID] = @CategoryID”>

            <DeleteParameters>

                <asp:Parameter
Name=”CategoryID”
Type=”Int32″
/>

            </DeleteParameters>

            <UpdateParameters>

                <asp:Parameter
Name=”CategoryName”
Type=”String”
/>

                <asp:Parameter
Name=”Description”
Type=”String”
/>

                <asp:Parameter
Name=”CategoryID”
Type=”Int32″
/>

            </UpdateParameters>

            <InsertParameters>

                <asp:Parameter
Name=”CategoryName”
Type=”String”
/>

                <asp:Parameter
Name=”Description”
Type=”String”
/>

            </InsertParameters>

        </asp:SqlDataSource>

  
 

 

GridView.aspx.cs

protected
void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        SqlConnection conn = new
SqlConnection(

                   ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

        try

        {

            if (e.CommandName.Equals(“New”))

            {

                LinkButton btnNew = e.CommandSource as
LinkButton;

                GridViewRow row = btnNew.NamingContainer as
GridViewRow;

                if (row == null)

                {

                    return;

                }

                TextBox txtCatName = row.FindControl(“CategoryNameTextBox”) as
TextBox;

                TextBox txtDescription = row.FindControl(“DescriptionTextBox”) as
TextBox;                

                SqlCommand cmd = new
SqlCommand(

                    “INSERT INTO [Categories] ([CategoryName], [Description]) VALUES (@CategoryName, @Description)”,

                    conn);

                cmd.Parameters.AddWithValue(“CategoryName”, txtCatName.Text);

                cmd.Parameters.AddWithValue(“Description”,txtDescription.Text);

                conn.Open();

                if (cmd.ExecuteNonQuery() == 1)

                {

                    GridView1.DataBind();

                }

            }

        }

        catch (Exception ex)

        {

 

        }

        finally

        {

            conn.Close();

        }

    }

Web.config

<connectionStrings>

            <addname=“NorthwindConnectionString“connectionString=“Data Source =.;Integrated Security = SSPI; Initial Catalog=Northwind;“/>

          
 

</connectionStrings>