Need help on exporting to excel from datagrid. (I dont want third party tool). I tried to render the data to HTML as given below:

//first let's clean up the response.object
response.Clear();
response.ClearContent();
response.ClearHeaders();
response.Buffer = false;

//set the response mime type for excel
response.ContentType = "application/ms-excel";

response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", StrFileName));

//create a string writer
StringWriter stringWrite = new StringWriter();

//create an htmltextwriter which uses the stringwriter
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

//instantiate a datagrid
DataGrid dg = new DataGrid();

//set the datagrid datasource to the dataset passed in
dg.DataSource = dr;

//bind the datagrid
dg.DataBind();

//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);

//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();

It works fine if i edit the file and save. I tried to create new worksheet in the same excel file and saved this changes, new temp folder has been created. (filename_files as like HTML files if you save any web page) Please let me know how to avoid this?

Harini

Hi I have answered the same question in SQL / ADO / ADO.Net Try this: SELECT SNO, Id, ( SELECT (COUNT(*) + 1) FROM table1 b WHERE b.SNO < a.SNO AND a.Id = b.Id) FROM table1 a OR SELECT SNO, Id, ( SELECT COUNT(*) FROM table1 b WHERE b.SNO <= a.SNO AND a.Id = b.Id) FROM table1 a

Harini

Hi Try this: SELECT SNO, Id, ( SELECT (COUNT(*) + 1) FROM table1 b WHERE b.SNO < a.SNO AND a.Id = b.Id) FROM table1 a OR SELECT SNO, Id, ( SELECT COUNT(*) FROM table1 b WHERE b.SNO <= a.SNO AND a.Id = b.Id) FROM table1 a

Harini

Hi Are you having any identity column (or serial number) field in your application? Then you need to pass extra parameters like current page number and number of records per page to your SP and based on these parameters you can return results. -- Calculate first and last record number SET @FirstRec = (@CurrentPageIndex - 1) * CAST(@PageSize AS INT) SET @LastRec = (@CurrentPageIndex * CAST(@PageSize AS INT) + 1) SELECT * FROM Table1 WHERE IDCol > @FirstRec AND IDCol < @LastRec If @PageSize = 10 and @CurrentPageIndex = 1, then @FirstRec will be 0 and @LastRec = 11. So first page will return only 10 records If @PageSize = 20 and @CurrentPageIndex = 1, then @FirstRec will be 0 and @LastRec = 21. So first page will return only 20 records and so on. Hope this solves your problems.

Harini

Then try this when binding data: DateTime DtmDate = DateTime.Now; System.Globalization.DateTimeFormatInfo dfi = new System.Globalization.DateTimeFormatInfo(); dfi.ShortDatePattern = "yyyy-MM-dd"; Response.Write(DtmDate.ToString("d", dfi));

Harini

modified on Wednesday, April 30, 2008 1:35 AM

Hi Are you binding data from database? You can do it in SQL Query using the convert() function as given below: SELECT CONVERT(NVARCHAR(10),Birthday,121) AS Birthday FROM Table1

Harini

Hi Try this: SELECT Organization, [Report Year], [Income], [Expenditure] FROM Table1 a WHERE [Report Year] >= ( SELECT TOP 1 [Report Year] FROM Table1 b WHERE a.Organization = b.Organization ORDER BY [Report Year] DESC ) ORDER BY Organization Hope you are getting results as expected.

Harini

Hi I am not clear with your question, do you want to get two maximum values from a column without using cursor? Please go thro' the following SQL Script, I am not sure whether you are looking for this solution: USE northwind DECLARE @MaxStock SMALLINT DECLARE @NextMaxStock SMALLINT SELECT @MaxStock = MAX(UnitsInStock) FROM PRODUCTS SELECT @MaxStock SELECT @NextMaxStock = MAX(UnitsInStock) FROM PRODUCTS WHERE UnitsInStock < @MaxStock SELECT @NextMaxStock SELECT @MaxStock + @NextMaxStock Hope this is clear.

Harini

Click this link: http://msdn2.microsoft.com/en-us/library/aa579385(EXCHG.80).aspx[^]

Harini

Hi I think there is no error in the SubString function, there is an error after scope function. For searching multiple folders, syntax is given below: SELECT "DAV:displayname" FROM SCOPE('shallow traversal of "http://myserver/public"', 'shallow traversal of "http://myserver/public/userinfo"', 'shallow traversal of "http://myserver/public/userinfo/phonenumber"') Please check this site for more information: http://msdn2.microsoft.com/en-us/library/aa579385(EXCHG.80).aspx[^] When i debug your code, i got the following statement: select Vpath, path, FileName, size, write, attrib, SUBSTRING(Characterization,90,DATALENGTH(Characterization)-1) as Characterization, DocTitle, rank from scope('shallow traversal of \"/Software\",\"/Industries\",\"/\"') where FREETEXT(contents,'abc') and FileName NOT LIKE 'inc_%' and FileName NOT LIKE '%.js' and FileName NOT LIKE 'searchresult.aspx' and FileName NOT LIKE '%.css' order by rank desc which is different from the given syntax. Hope this helps.

Harini

Hi, One more thing, replace the variable searchText as parameter to avoid security (SQL Injection).

Harini N K wrote:

cmdSearch.CommandText = "SELECT Vpath, path, FileName, size, write, attrib, Characterization, DocTitle, rank FROM SCOPE('shallow traversal of " + "\"" + "/Software" + "\"" + "') where FREETEXT(contents,'" + searchText + "') order by rank desc ";

This is a good coding practice in any langauge.

Harini

Ok ... That is good!!

Harini

Hi You need to split the string "\Software" as "\"" for each double quotes with escape character: See below: cmdSearch.CommandText = "SELECT Vpath, path, FileName, size, write, attrib, Characterization, DocTitle, rank FROM SCOPE('shallow traversal of " + "\"" + "/Software" + "\"" + "') where FREETEXT(contents,'" + searchText + "') order by rank desc "; Let me know if that works.

Harini

Hi You can use TemplateColumn and add as many as button within the template column.

Harini

Try the following SQL statement: First drop all the child tables and then go for parent tables: USE NORTHWIND SELECT OBJECT_NAME(c.parent_object_id) ParentTableName, OBJECT_NAME(c.referenced_object_id) ChildTableName FROM sys.foreign_key_columns c INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id AND a.column_id = c.referenced_column_id Hope this helps!!

Harini

Hi Use DIV section instead of panel. Add your diagram section inside DIV Section and you can set the size for width and height; add the style as OVERFLOW: auto; Hope this solves your problem.

Harini

Hi experts, I want to change value of an identity column without dropping table / references / constraints / deleting rows from that table. I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging. Is there any SQL statement to modify that column as Identity Seed to NO.?

Harini

Check this link[^]

Harini

Hi Added references should appear in the project explorer under 'References'. If not add again.

Harini

OK. Hope you also added reference ("Add Reference..") to your project. Check again

Harini