Archive

Archive for March, 2012

Export DataGrid into Excell in a Content Page

March 27, 2012 Leave a comment

Data will go into plain text but will open/save with xls format

void ExportDataGRidToExcell()
{
System.Web.UI.HtmlControls.HtmlForm frm = new System.Web.UI.HtmlControls.HtmlForm();
frm.Attributes.Add(“runat”, “server”);
this.Controls.Add(frm);
frm.Controls.Add(GridView1);
Response.Clear();
Response.Buffer = true;
Response.ContentType = “application/vnd.ms-excel”;
Response.Charset = “”;
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
frm.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}

Sometimes, when you render any asp.net control dynamically as I am doing with the GridView control in this tutorial, you can get HttpException with the following message:
Control ‘GridView1’ of type ‘GridView’ must be placed inside a form tag with runat=server.
Below 2 thing that you need to done in your page-

public override void VerifyRenderingInServerForm(Control control)
{ }

and add attribute EnableEventValidation into page directive-

<%@ Page Title=”” Language=”C#” EnableEventValidation=”false”

Advertisements
Categories: ASP.Net

Creating Comma Separate Values List from Table – UDF – SP

March 23, 2012 Leave a comment

The table is example is:TableName: NumberTable
NumberCols
first-record
second-record
third-record
fourth-record
fifth-record

Output : first-record,second-record,third-record,fourth-record,fifth-record

Create FUNCTION GetDateAsString
(
@OrderID int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ”
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + convert(varchar,PubDate,103)
FROM NumberTable where NumberTable.ID=@OrderID
if len(@listStr)> 2
SELECT @listStr=SUBSTRING(@listStr,2,len(@listStr)-1)
return @listStr
end

Categories: SQL