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=NorthwindConnectionStringconnectionString=Data Source =.;Integrated Security = SSPI; Initial Catalog=Northwind;/>

           
 

</connectionStrings>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: