SSRS subreport with drill down step by step in sql server 2008

sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we will toggle (0pen) the sub report and not on the load of main report.

To learn basic of reports see my previous article How to create basic report in Sql Server 2008

Here is the report output which we will create in this article.

Let’s create a new blank project to work (if you are familiar then omit this section)

• Click Start -> All Programs -> Microsoft Sql Server 2008 -> Sql Server Business Intelligence Development Studio
• File -> New -> Project..
• Business Intelligence -> Report Server Report
• Give a name to the project and click OK

It will create blank project with two folders

1. Shared Data Source
2. Reports

Right click on Shared Data Source and select Add New Data Source

Click Edit to Add connection String and set your Sql Server connection

Now we created a new Shared Data Source which we will use in our project for every report.

We will use Northwith database to create an Orders report with Order Details as sub report, so let’s create our main report Orders

• Right Click on Reports folder
• Select Add -> New Item...
• Select Report and give name to it “Orders.rdl”

It will open a blank surface to add our report columns, header Title etc.

Open Toolbox and drag a Table to the report surface

It will open Data Source Property Dialog

Click on Use Shared Data Source reference and select our created data source

Click Next and type your query or procedure name which you want to use for this report, in our case

 Select * from Orders

Execute it to check the records and click Finish

A table will be added to the report area, now time to drag columns one by one, if need more column click on header and add more columns to it.

Adjust header background color and text color and font.

Click on Preview button to see the report, good it is working fine till now, let’s format the date column.

Right click on Order Date column -> Expression and change it to

=Format(Fields!OrderDate.Value, "dd MMM, yyyy")

Now report will show Order date as 04 Jul 2014.

Now time to create Order Details report which we will use as sub report in our main report Orders As earlier we created our Orders report, create a new report with name “Order Detail” and here is the sql statement

Select P.ProductId, P.ProductName, od.UnitPrice, OD.Quantity, OD.Discount From [Order Details] OD
INNER JOIN Products P ON P.ProductId = OD.ProductID
Where OD.[OrderID] = @OrderID

As you can see we used @OrderID as a parameter so this report will take order Id to run it which we will pass on toggle of parent report.

Now we will add our order detail report to main report Orders.

Right click on the item row -> Insert Row -> Inside Group – Below

Select all the column to merge, right click and merge it.

Open Toolbox and drag a Subreport in our merged cells

Right click on it -> Subreport Properties

From Use this report as a sub report select Order Details

Select Parameters -> Click Add -> Select OrderID in both dropdown

Now if you will see the order report it will show all the order with detail without drill down so let’s add drill down

Again open sub report properties -> Visibility

1. Select Hide
2. Select Display can be toggled by this report item
3. From drop down select Order Id

Now we completed run and see the report.

How to create basic report in Sql Server 2008

In this article we will learn how to create a basic SSRS Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in report.
So why we are waiting, let's start

• Click on Start
• Click All Programs
• Expand Microsoft SQL Server 2008 folder
• Sql Server Business Intelligence Development Studio
• Click File menu
• Click New
• Click Project…
• From Project type select Business Intelligence
• From Templates: select Report Server Report
• Select folder to save and enter project Name

We have created a blank project without any data source and without any default report because we will create our data source and reports step by step. As you can see there two folders created

1. Shared Data Source: where we will create our datasource
2. Reports: where we will create our all the reports

Let’s create our shared data source first which we will use for our all the reports in this project

• Right click on Shared Data Source Folder
• Click Add New Data Source
• It will open Shared Data Source Properties dialog
• Enter Name for data source say MyDataSource
• Select Type: since I am going to use Sql Server Northwind database so select “Microsoft Sql Server”
• Click Edit button to define the connection string
• Enter server name say localhost
• Logon to the server: Select your authentication type
• Select or enter a database name : Northwind
• Click Test Connection
• Click Ok

Here is our all the settings:

We set our shared data source, time to create a procedure or write Sql statement to get data for report, since it is our first report so we will not use complicated procedure with different parameters but very simple Sql statement

 SELECT * FROM [Suppliers]

Now we will create our first report, we will use blank report without using report wizard, so let’s create it.

• Right click on Reports Folder
• Click Add
• Click New Item, it will open Add New Item dialog
• From Templates: select Report
• Give name to report, say, Suppliers.rdl
• Click Add

It will open a blank surface to design our report, here we will add report header, footer and a table to design the report.
To add report header

• Right click on the surface
• Click Insert
• Click Report Header
• Add Textbox to add the report header text

Similar to report header we can add report footer and its contents, like report generation date.
To add report content (supplier detail)

• From Toolbox drag a Table on report surface
• It will open a dialog to select the data source
• Select Use shared data source reference
• Select your shared data source from drop down
• Click Next
• It will open Query Designer
• Select Text from Command type drop down
• Paste our Sql statement and execute it.
• Click Finish

Now we will add column to this table

• Open Report Data window
• If it is not visible then open it from view menu
• Drag different columns from DataSet in table column
• If you need more columns, right click on header to add the more columns

Once you added your all the columns, click preview to see the result, it is working but One thing we are missing, alternate row color for data, so let's add it

• Select entire row
• Open Property
• Select BackgroundColor, clieck expression

Paste following into the expression pan

= IIf(RowNumber(Nothing) Mod 2 = 0, "Transparent", "WhiteSmoke")

Here is our created report

Gridview multiple header rows

How to show multirow header in gridview in asp.net? i was searching for this question but could not found any easy way so I decided to try my own and found it is not so difficult. Simply we can use header template and item template and design it according to our requirements. we will see this in action in this article.

First let's see what we are going to do, here is the final output which we will achieve in this article. 

Now we know what we are going to do so let's see the HTML of this grid

<asp:GridView ID="gvProducts" runat="server"
  Width="99%"
  GridLines="None"
  HeaderStyle-CssClass="gvHeader"
  CssClass="gvRow"
  AlternatingRowStyle-CssClass="gvAltRow"
  AutoGenerateColumns="false">
  <Columns>
    <asp:TemplateField>
      <HeaderTemplate>
        <th colspan="6">Category</th>
        <tr class="gvHeader">
           <th style="width:0px"></th>
           <th colspan="3">Hardware</th>                        
           <th colspan="3">Software</th>
        </tr>
        <tr class="gvHeader">
          <th></th>
          <th>S. No.</th>
          <th>Product</th>
          <th>Price</th>

          <th>Product</th>
          <th>Descript</th>
          <th>Price</th>
        </tr>
      </HeaderTemplate> 
      <ItemTemplate>
        <td style="width:40px"><%# Eval("SNO") %></td>
        <td><%# Eval("Product")%></td>
        <td><%# Eval("Price")%></td>
        <td><%# Eval("HProduct")%></td>
        <td><%# Eval("Description")%></td>
        <td><%# Eval("HPrice")%></td>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

We need used some trick here so let's understand those

1. Only One TemplateField
2. In HeaderTemplate for category we expand the header to 6 columns and no tr because header template will automatically create the first tr
3. Then create a new row by using tr
4. Here first th without any text and two more column both with colspan = 3 so total 7 columns while we need only 6, why, don't worry for now add one extra column in each row in header as well as item except first header row
5. Now in ItemTemplate use td for every column and bind the value according to your requirement

Everything is don for designing the multiple rows in header, so let's create a hard coded data table and bind the gridview

DataTable dTab = new DataTable();

dTab.Columns.Add("SNO");
dTab.Columns.Add("Product");
dTab.Columns.Add("Price");
dTab.Columns.Add("HProduct");
dTab.Columns.Add("Description");
dTab.Columns.Add("HPrice");
DataRow dr = dTab.NewRow();
for (int i = 0; i < 10; i++)
{
    dr = dTab.NewRow();
    dr["SNO"] = i + 1;
    dr["Product"] = String.Format("MS-Office {0}", i + 1);
    dr["Price"] = 10 * (i + 1);
    dr["HProduct"] = String.Format("Mouse {0}", i + 1);
    dr["Description"] = String.Format("USB mouse {0}", i + 1);
    dr["HPrice"] = 7 * (i + 1);
    dTab.Rows.Add(dr);
}            

gvProducts.DataSource = dTab;
gvProducts.DataBind();

Copy and paste HTML in your aspx page and code in page load method and run it. you will see a simple grid with and extra line at beginning, add following style to your page.

.gvHeader th{
   padding:3px; 
    background-color:#DDEECC; 
   color:maroon; 
   border:1px solid #bbb;}
 .gvRow td{padding:3px; 
    background-color:#ffffff; 
    border:1px solid #bbb;}
.gvAltRow td{
    padding:3px; 
    background-color:#f1f1f1; 
    border:1px solid #bbb;}

Run your page and you will see your grid similar to above image in this article but still one extra column is there so to remove the extra column add following three lines and your extra column will be hidden and you will get the perfect output

.gvHeader th:first-child{display:none;}
.gvRow td:first-child{display:none;}
.gvAltRow td:first-child{display:none;}

Now we completed out multiple row header and formatting.

Handling Errors in Stored Procedures

The following article introduces the basics of handling errors in stored procedures. If you are not familiar with the difference between fatal and non-fatal errors, the system function @@ERROR, or how to add a custom error with the system stored procedure sp_addmessage, you should find it interesting.

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do (assuming it’s a non-fatal error) is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. Notice that the previous sentence is specific to non-fatal errors. There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error.

The following example demonstrates how a fatal error affects a procedure.

USE tempdb
go
CREATE PROCEDURE ps_FatalError_SELECT
AS
SELECT * FROM NonExistentTable
PRINT 'Fatal Error'
go
EXEC ps_FatalError _SELECT

--Results--

Server:Msg 208,Level 16,State 1,Procedure ps_FatalError_SELECT,Line 3
Invalid object name 'NonExistentTable'.

The SELECT in the procedure references a table that does not exist, which produces a fatal error. The procedure aborts processing immediately after the error and the PRINT statement is not executed.

To demonstrate how a non-fatal error is processed, I need to create the following table.

USE tempdb
go
CREATE TABLE NonFatal
(
Column1 int IDENTITY,
Column2 int NOT NULL
)

This example uses a procedure to INSERT a row into NonFatal, but does not include a value for Column2 (defined as NOT NULL).

USE tempdb
go
CREATE PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
PRINT 'NonFatal'
go
EXEC ps_NonFatal_INSERT

--Results--

Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 4
Cannot insert the value NULL into column 'Column2',table 'tempdb.dbo.NonFatal'; 
column does not_allow nulls.INSERT fails.  
The statement has been terminated.
NonFatal

The last line of the results (shown in blue) demonstrates that the error did not affect the processing of the procedure—the PRINT statement executed.

You might be wondering what actions cause fatal errors. Unfortunately, the actions that cause a fatal error are not well documented. Each error has an associated severity level that is a value between 0–25. The errors with a severity level of 20 or above are all fatal, but once you get below this value there is no well-defined rule as to which errors are fatal. In truth, though, worrying about which errors are fatal is a bit useless because there is no code you can implement that will allow you to handle them gracefully. Of course, you can use pro-actice coding to make sure fatal-errors do not occur. For example, if your application allows users to type in the name of the table on which a query is based you can verify it’s existence before referencing it with dynamic SQL.

@@ERROR

The @@ERROR system function is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of the function immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset when the next statement executes successfully.

Let’s alter ps_NonFatal_INSERT to use @@ERROR with the following.

USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
 BEGIN
  PRINT 'Error Occured'
 END

--Results--

The command(s)completed successfully.

When an error occurs, the PRINT statement produces the "Error Occurred" message. The following code shows the results of a valid call to ps_NonFatal_INSERT.

USE tempdb
go
EXEC ps_NonFatal_INSERT 111

--Results--

(1 row(s)affected)

The next example shows the results of a call that produces the "does not allow nulls" error.

USE tempdb
go
EXEC ps_NonFatal_INSERT

--Results--

Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 4
Cannot insert the value NULL into column 'Column2',table 'tempdb.dbo.NonFatal'; 
column does not_allow nulls.INSERT fails. 
The statement has been terminated.
Error Occured

The last line of the results (in blue) indicates the PRINT statement executed as expected.

RAISERROR

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table. You can use this statement with the error handling code presented in the previous section to implement custom error messages in your applications. The syntax of the statement is shown here.

RAISERROR ({msg_id |msg_str }{,severity ,state }
   [ ,argument [ ,,...n ] ] ))
   [ WITH option [ ,,...n ] ]

A description of the components of the statement follows.

msg_id

The ID for an error message, which is stored in the error column in sysmessages. The domain of the error column for custom messages are values greater than 50,000.

msg_str

A custom message that is not contained in sysmessages. The maximum length of the message is 400 characters. Variable substitution can be used to create a more meaningful message.

severity

The severity level associated with the error. The valid values are 0–25. Severity levels 0–18 can be used by any user, but 19–25 are only available to members of the fixed-server role sysadmin. When levels 19–25 are used, the WITH LOG option is required.

state

A value that indicates the invocation state of the error. The valid values are 0–127. This value is not used by SQL Server.

Argument, . . .

One or more variables that are used to customize the message. For example, you could pass the current process ID (@@SPID) so it could be displayed in the message.

WITH option, . . .

The three values that can be used with this optional argument are described here.

LOG - Forces the error to logged in the SQL Server error log and the NT application log.

NOWAIT - Sends the message immediately to the client.

SETERROR - Sets @@ERROR to the unique ID for the message or 50,000.

The number of options available for the statement make it seem complicated, but it is actually easy to use. The following shows how to create an ad hoc message with a severity of 10 and a state of 1.

RAISERROR ('An error occured updating the NonFatal table',10,1)

--Results--

An error occured updating the NonFatal table

The statement does not have to be used in conjunction with any other code, but for our purposes it will be used with the error handling code presented earlier. The following alters the ps_NonFatal_INSERT procedure to use RAISERROR.

USE tempdb
go
ALTER PROCEDURE ps_NonFatal_INSERT
@Column2 int =NULL
AS
DECLARE @ErrorMsgID int

INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
 BEGIN
  RAISERROR ('An error occured updating the NonFatal table',10,1)
 END

When an error-producing call is made to the procedure, the custom message is passed to the client. The following shows the output generated by Query Analyzer.

USE tempdb
go
EXEC ps_NonFatal_INSERT

--Results--

Server:Msg 515,Level 16,State 2,Procedure ps_NonFatal_INSERT,Line 6
Cannot insert the value NULL into column 'Column2',table 'tempdb.dbo.NonFatal'; 
column does not_allow nulls.INSERT fails.
The statement has been terminated.
An error occured updating the NonFatal table

The output may seem confusing because we still see the same error message displayed before we started using RAISERROR. The custom error (in blue) is also displayed. The output is a function of Query Analyzer and we cannot control its behavior. When you develop client applications you will have control over what is displayed to the end user so the output will be less confusing.

Adding a Permanent Custom Message

If you have a message that is going to be used frequently, it is more efficient to add it to the sysmessages table and reference it by its unique ID. The system stored procedure sp_addmessages adds an error message to sysmessages. The following shows how to add a new error message.

sp_addmessage @msgnum =50001,
@severity =10,
@msgtext ='An error occured updating the NonFatal table'

--Results--

(1 row(s)affected)

Note that the ID for a custom message must be greater than 50,000. The new message can be accessed with RAISERROR using the following.

RAISERROR (50001,10,1)

--Results--

An error occured updating the NonFatal table

- See more at: http://www.sqlteam.com/article/handling-errors-in-stored-procedures#sthash.UUCdEhiJ.dpuf

Update Trigger In SQL

Create trigger Trigger_Name ON Table_Name
FOR UPDATE
AS
DECLARE @Now_date date=getdate();
DECLARE @Updated_date date;
select @Updated_date =i.doj from inserted i;

if(DATEDIFF(yy,@Updated_date ,@Now_date ) >= 5)
UPDATE Table_Name set Column_Name=Value where id=1;

PRINT 'AFTER UPDATE Trigger fired.'
GO


UPDATE test set doj='5/5/2000'



http://codedisplay.com/dynamically-runtime-bind-data-into-crystal-report-using-asp-net-c-vb-net/

Print data in Excel

using System.Xml;
using System.Xml.Xsl;
using System.IO;
using System.Drawing;

 Public void ExportToExcel(DataTable dt, string FileName)
    {
        if (dt.Rows.Count > 0)
        {
            string filename = FileName + ".xls";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            DataGrid dgGrid = new DataGrid();
            dgGrid.DataSource = dt;
            dgGrid.DataBind();

            //Get the HTML for the control.
            dgGrid.RenderControl(hw);
            //Write the HTML back to the browser.
            //Response.ContentType = application/vnd.ms-excel;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition",
                                  "attachment; filename=" + filename + "");
            this.EnableViewState = false;
            Response.Write(tw.ToString());
            Response.End();
        }
    }

Methods in Javascrip in asp.net

<head runat="server">
    <title></title>
    <script type="text/javascript" language="javascript">
        function sum() {

             var value1 = document.getElementById("txt1").value;
             if (value1.length < 1) {
                 value1 = 0;
             }
             var value2 = document.getElementById("txt2").value;
             if (value2.length < 1) {
                 value2 = 0;
             }
             var value3 = document.getElementById("txt3").value;
             if (value3.length < 1) {
                 value3 = 0;
             }
             var total = (parseInt(value1) + parseInt( value2) +parseInt( value3));
             document.getElementById("txt4").value = total;
           
           


         }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:TextBox ID="txt1"  onkeyup="sum();" runat="server"></asp:TextBox><br />
        <asp:TextBox ID="txt2"  onkeyup="sum();" runat="server"></asp:TextBox><br />
        <asp:TextBox ID="txt3"  onkeyup="sum();" runat="server"></asp:TextBox><br />
        <asp:TextBox ID="txt4" runat="server"></asp:TextBox><br />
   
    </div>
    </form>
</body>

Nagar data

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
    <td>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
      </td>
    <td>
        <asp:RadioButtonList ID="RadioButtonList1" runat="server"
            RepeatDirection="Horizontal">
        <asp:ListItem>english</asp:ListItem>
        <asp:ListItem>hindi</asp:ListItem>
        <asp:ListItem>nepali</asp:ListItem>
        <asp:ListItem>science</asp:ListItem>
        </asp:RadioButtonList>
    </td>
    </tr>
   

    </table>
        <asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" /><br /><br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
        <Columns>
        <asp:TemplateField HeaderText="Name">
        <ItemTemplate>
            <asp:Label ID="lbl1" runat="server" Text='<%#Eval("name") %>'></asp:Label> </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Subject">
        <ItemTemplate>
         <asp:RadioButtonList ID="rd1" runat="server"
            RepeatDirection="Horizontal" ViewStateMode="Enabled">
        <asp:ListItem>english</asp:ListItem>
        <asp:ListItem>hindi</asp:ListItem>
        <asp:ListItem>nepali</asp:ListItem>
        <asp:ListItem>science</asp:ListItem>
        </asp:RadioButtonList></ItemTemplate>
        </asp:TemplateField>
        </Columns>
        </asp:GridView>
        <asp:Button ID="Button2" runat="server" Text="submit all"
            onclick="Button2_Click" /><br />
        <asp:Button ID="Button3" runat="server" Text="Button" onclick="Button3_Click" />
    </div>
    <asp:GridView runat="server" ID="GridView2"  AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="Id" HeaderText="UserId" />
<asp:BoundField DataField="Name" HeaderText="UserName" />
<asp:BoundField DataField="sub" HeaderText="LastName" />
</Columns>
</asp:GridView>

    </form>
</body>
</html>

------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
using System.Drawing;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con1"].ConnectionString);
    SqlDataAdapter da;
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindgrid();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string sub = "";
        for (int i = 0; i < RadioButtonList1.Items.Count; i++)
        {
            if (RadioButtonList1.Items[i].Selected == true)
            {
                sub = RadioButtonList1.Items[i].ToString();
                break;
            }
        }
        cmd = new SqlCommand("insert into test(name,sub) values(@name,@sub)", con);
        cmd.Parameters.AddWithValue("@name", TextBox2.Text);
        cmd.Parameters.AddWithValue("@sub", sub);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    public void bindgrid()
    {
        da = new SqlDataAdapter("select name from test",con);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }


    protected void Button2_Click(object sender, EventArgs e)
    {
        string sub = "";
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            Label lblname = (Label)GridView1.Rows[i].FindControl("lbl1");
            RadioButtonList rd = (RadioButtonList)GridView1.Rows[i].FindControl("rd1");
            //sub = (GridView1.Rows[0].FindControl("rd1") as RadioButtonList).SelectedValue;
            for (int j = 0; j < rd.Items.Count; j++)
            {
                if (rd.Items[j].Selected == true)
                {
                    sub = rd.Items[j].ToString();
                    break;
                }
            }
            cmd = new SqlCommand("insert into test(name,sub) values(@name,@sub)", con);
            cmd.Parameters.AddWithValue("@name", lblname.Text);
            cmd.Parameters.AddWithValue("@sub", sub);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    protected void Button3_Click(object sender, EventArgs e)
    {
        da = new SqlDataAdapter("select (name+':'+sub) as name from Test",con);
        DataTable dt = new DataTable();
        da.Fill(dt);
        ExportToExcel(dt, "abc");
    }
    void ExportToExcel(DataTable dt, string FileName)
    {
        if (dt.Rows.Count > 0)
        {
            string filename = FileName + ".xls";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            DataGrid dgGrid = new DataGrid();
            dgGrid.DataSource = dt;
            dgGrid.DataBind();

            //Get the HTML for the control.
            dgGrid.RenderControl(hw);
            //Write the HTML back to the browser.
            //Response.ContentType = application/vnd.ms-excel;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition",
                                  "attachment; filename=" + filename + "");
            this.EnableViewState = false;
            Response.Write(tw.ToString());
            Response.End();
        }
    }

}