Deleting Multiple Rows in a GridView

 
 

A gridview allows us to delete only a single row at a time. We will extend this functionality to select multiple rows and delete all of the selected rows in a single stroke. In this article, I assume that you are aware of creating asp.net web applications and have worked with gridview.

The sample makes use of the Northwind database. We will be pulling data from the Employee table. For this sample to work, drop all the Foreign Key relationships on the Employee Table. To do so, in Sql Server Management Studio, browse to the Northwind database and open the Employee table in design view. Right click in the Table designer on the right hand side and choose ‘Relationships’. Select all the relationships like FK_Orders_Employees,  FK_EmployeeTerritories_Employees etc and delete them. This step is necessary as we will get a constraint violation exception if we do not do so.

Once we are through with the task of removing the relationships in the Employee table, let us explore the steps to create a gridview with functionality to delete multiple rows at a time.

Perform the following steps :

Step 1: Create an .aspx page and add a GridView and a SqlDataSource control to it.

Step 2: Configure the connection of SqlDataSource to point to the Northwind database.  Create queries for the Select and Delete commands. The resultant code will look similar as given below :

<asp:SqlDataSource
ID=”SqlDataSource1″
Runat=”server”

    SelectCommand=”SELECT EmployeeID, LastName, City FROM Employees”

    DeleteCommand=”DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID”

    ConnectionString=”<%$ ConnectionStrings:NorthwindConnectionString %>
>

       <DeleteParameters>

           <asp:Parameter
Name=”EmployeeID”
/>

       </DeleteParameters>

</asp:SqlDataSource>                                   

Step 3: Once the SqlDataSource has been configured, bind the gridview with this data source.

Step 4: To create a checkbox in each row, follow these steps:

1.    Create a TemplateField inside the <Columns> to add custom content to each column.

2.    Inside the TemplateField, create an ItemTemplate with a CheckBox added to it.

<asp:TemplateField>

       <ItemTemplate>

             <asp:CheckBox
ID=”chkRows”
runat=”server”/>

      </ItemTemplate>

 </asp:TemplateField>

This will add a checkbox to each row in the grid.

Step 5: Add a button control, and rename it to btnMultipleRowDelete.

The resultant markup in the design view will look similar to the code below :

<asp:GridView
ID=”GridView1″
runat=”server”
AutoGenerateColumns=”False”
DataKeyNames=”EmployeeID” DataSourceID=”SqlDataSource1″>

  
 

   <Columns>

      <asp:TemplateField>

          <ItemTemplate>

            <asp:CheckBox
ID=”cbRows”
runat=”server”/>

          </ItemTemplate>

       </asp:TemplateField>

 
 

<asp:BoundField
DataField=”EmployeeID”
HeaderText=”EmployeeID”
InsertVisible=”False” ReadOnly=”True”
SortExpression=”EmployeeID”
/>

<asp:BoundField
DataField=”LastName”
HeaderText=”LastName”
SortExpression=”LastName”
/>

<asp:BoundField
DataField=”City”
HeaderText=”City”
SortExpression=”City”
/>

   </Columns>

</asp:GridView>

 
 

<asp:SqlDataSource
ID=”SqlDataSource1″
Runat=”server”

SelectCommand=”SELECT EmployeeID, LastName, City FROM Employees”

DeleteCommand=”DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID”

ConnectionString=”<%$ ConnectionStrings:NorthwindConnectionString %>
>

   <DeleteParameters>

       <asp:Parameter
Name=”EmployeeID”
/>

   </DeleteParameters>

</asp:SqlDataSource>

 
 

<asp:Button

   ID=”btnMultipleRowDelete”

   OnClick=”btnMultipleRowDelete_Click”

   runat=”server”

   Text=”Delete Rows”
/>

 
 

In Code behind file (.cs) for C# and (.vb) for VB.NET, code the button click event. Our code will first loop through all the rows in the GridView. If a row is checked, the code retrieves the EmployeeID and passes the selected value to the Delete Command.

C#

 
 

 
 

protected
void btnMultipleRowDelete_Click(object sender, EventArgs e)

{

        // Looping through all the rows in the GridView

        foreach (GridViewRow row in GridView1.Rows)

        {

            CheckBox checkbox = (CheckBox)row.FindControl(“cbRows”);

 
 

            //Check if the checkbox is checked.

//value in the HtmlInputCheckBox’s Value property is set as the //value of the delete command’s parameter.

            if (checkbox.Checked)

            {

                // Retreive the Employee ID

int employeeID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

// Pass the value of the selected Employye ID to the Delete //command.

SqlDataSource1.DeleteParameters[“EmployeeID”].DefaultValue = employeeID.ToString();

                SqlDataSource1.Delete();

            }

        }

 }

 
 

Run the code, and select a few rows in the grid. ‘Delete Rows’ button, the selected rows get deleted. Rather than deleting rows one at a time, deleting them in a batch is a good practice. I would encourage you to read Scott Mitchell’s article for the same.

 
 

Conclusion

 
 

By default, the gridview provides the functionality to delete a single row at a time. In this article, we explored how to extend the functionality of the grid view and delete multiple rows. I hope this article was useful and I thank you for viewing it.

Advertisements

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>

A scrollable ASP.NET Gridview with a fixed header

 

Introduction

ASP.NET Gridview has the provision of providing paging, set a particular pagesize etc.. But at times our clients are too adamant to demand for scrollable gridview with a steady header.Gridview as such doesn’t have a scrollable feature . But still a work around is possible. This is being discussed in this article that provides us a convenient scrollable grid with a steady header.

STEP 1- Scrollable GridView

Add the gridview into a asp:Panel control

<asp:Panel
runat=”server”
ScrollBars=”auto”
Width=”500px”>

            <asp:GridView
ID=”GridView1″
runat=”server”>

            </asp:GridView>

        </asp:Panel>

This Panel has a scroll bar feature so it tends to scroll the grid. But our problem is not yet solved. What is the next issue- Constant header.
The above code makes the whole panel content to scroll. But most of the application needs a constant header. Is it possible? This is explained in the next section 

STEP 2- Stable header
This can be done in 2 ways

1. Add the css to the Panel

01..fixedHeader

02. {

03. overflow: auto;

04. height: 145px;

05.  

06. }

07. table th

08. {

09.   

10. position: relative;

11.   

12. }

2. In the above way we have added style to the Panel as a whole.
Again we can achieve stable header by adding header style for the grid.
This is the style:

1..fixedHeader

2. {

3. background-color: aqua;

4. position: relative;

5. top: expression(this.offsetParent.scrollTop);

6. }


And the HTML becomes 

1.<HeaderStyle CssClass=”fixedHeader”
/>

 
 

Conclusion
This article deals with a scrollable gridview that has a stable header.Though this is not possible as such as the gridview doesnot have a scrollable property it can be done by a work around. I hope it helps.Happy Coding.

Thank you

Baimey

How to use Masked Edit Extender in ASP.NET

 

Introduction

In this article, I will show you step by step procedure, how to use a Masked Edit Extender
In ASP.NET using Visual Studio 2005 or Visual Studio 2008.

The use of Masked Edit Extender it that you are not require to type any number of character you have to just select from the up down button…it will change dynamically when you click the button,

To follow this code sample, you must use Visual Studio 2005 or Visual Studio 2008.

Note : I have used to comment HTML part of the code.

Note: Masked Edit Extender is a part of AjaxControlToolkit.


To attach right click on the toolbox under Ajax Extensions and select Choose Items – > Browse Button – > select the AjaxControlToolkit.dll. If you are using Visual Studio 2008, you may not need AJAX Toolkit but if you are using Visual Studio 2005, you will need to download it from http://www.msdn.com/ or http://www.asp.net/.

Here is step by step to implement a sample using mask extender.

Step 1.

Start -> All Programs -> Visual Studio 2005 or Visual Studio 2008

Step 2.

Now go to File Menu -> New -> Web Site

Step 3.

Under Visual Studio Installed Template-> Choose ASP.NET WEB SITE -> Choose File System from the location combo box -> Set the path by the browse button – > Choose the language from the Language ComboBox (Visual C# , Visual Basic , J #) Choose Visual C#.

Step 4.

Click on the OK Button.

This is the source code window and in this page you will se this code.


Type your webpage title here  

<form id=””form1?”
runat=””server””>

</form>

See here is a tab named Design in the bottom of this page.

Step 5.

 
 

Click on this tab and you will see a blank web page where you can drag any control from the toolbox (which is in the left side of this window).


Step 6.

Now drag controls under the AJAX Extensions.

First control you are going to drag and drop on the page is – Script Manager.

First register the ajaxtoolkit in the page. Thena dd the code below:

Enter the details below in
the textboxes and you will see how the masked control

Works

MaskType=”Number”
Mask=”999″
MessageValidatorTip=”true”
AcceptNegative=”None”

InputDirection=”RightToLeft”
ErrorTooltipEnabled=”true”
>

ControlToValidate=”TextBox2″
ControlExtender=”MaskedEditExtender1″

MinimumValue=”1″
MaximumValue=”105″
MinimumValueMessage=”Please enter correct

roll number” MaximumValueMessage=”Invalid roll number! The highest roll

number is
105″ Display=”Dynamic” IsValidEmpty=”false”

InvalidValueMessage=”The roll number does not exist”
EmptyValueMessage=”The roll number is not entered”>

Step 7.

Now run your web site by Ctrl + F5


Step 8.

When you run the Masked Edit Extender will seem like this following

Now in the Masked text boxes.you can’t enter characters or hexadecimal values you can only enter digits and here will be some restrictions. which the masked text box will follow.

Thank
you

Baimey