Popular Posts

There was an error in this gadget

Sep 30, 2013

Export to Excel from Datatable in Asp.Net







This function is used to export datatable to export.



public void ExportToExcel(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            string filename = "DownloadIRTList.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();
        }
    }



Call the function

DataTable dataTableObj = incedenceManagerObj.GetResolvedList();
ExportToExcel(dataTableObj);




Sep 3, 2013

update columns values with column of another table based on condition




Lets we have two tables.Name of table 1 is t_table1 and table 2 is t_table2.

Table1:
 
CREATE TABLE [dbo].[t_table1](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [full_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [short_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
 


 Table2:

CREATE TABLE [dbo].[t_table2](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [short_name_table1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
Lets dump some data:
for table 1:


full_name short_name
Jubayer Ahmed J.Ahmed
Sera Rubaya S.Rubaya
Ahmed Khalid A. Khalid
Ahmed Sharid A. Sharid

for table 2:


full_name
Jubayer Ahmed
Sera Rubaya
Ahmed Khalid
Ahmed Sharid

We will update short_name_table1 columns of table 2 by the value of t t_table1.
Now run this query...

update t_table2
   set t_table2.short_name_table1 = t_table1.short_name
   FROM t_table1 INNER JOIN  t_table2 on t_table2.full_name = t_table2.short_name_table1