November 14, 2011 at 4:06 pm
I need to retrieve data from a SQL server database to a .net .aspx page and, on that page, use the data to populate a spreadsheet the user can save.
From what I have read it is best to do this using XML as I don't need to then use the Excel Interop COM object (?)
If I execute this:
SELECT UserID, UserName FROM tblUsers FOR XML Auto, Root('Users')
it produces this:
<Users>
<tblUsers UserID="1" UserName="Steve" />
<tblUsers UserID="2" UserName="Andrea" />
<tblUsers UserID="3" UserName="Zebedee" />
</Users>
... is that any use? Will I be able to create a spreadsheet with two columns called UserID and UserName with 3 rows with 1,2 and 3 in the first cells and Steve, Andrea and Zebedee in the second cell?
Anyone know what sort of object I should populate in the .net page? Normally I populate Datasets or SQLDataReaders with whatever data I retrieve from the database - and use these as the datasource for controls. But I guess I don't want to do this with an XML stream?
Thanks for any help - as you no doubt realise - don't know where to start with this.
November 14, 2011 at 5:17 pm
Why not simply create a SSIS Package and pump the data out to an Excel Spreadsheet? It would be much easier, you can use a stored procedure to create the query, so you can modify the query if needed without having to modify the SSIS package. You can also schedule the package to execute when needed
Why are you including a .NET webpage in the middle?
Andrew SQLDBA
November 15, 2011 at 1:26 am
Thanks for your reply. The .net page in the middle is to present users with a list of contacts that they want to filter ... by ticking check boxes to say 'I want record 1, 4, 9, 18 and 32 from this particular set of data - create me a spreadsheet with just that data in it'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply