Very Long Strings Into Excel from OleDB Command

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have seen this problem a number of times around the net, but never found a
suitable solution...

I have a SQL Server view with two columns. One is 8 characters (varchar),
the other is varchar(8000) and contains data from 500 characters up to 8000.
I am using an ASP.Net page to give people the opportunity to export this data
to Excel for analysis etc. Since it's an ASP.Net page, I thought OleDb to
Excel would be the best way to do this, since Excel automation is not a good
way to go on a server.

The method I am using goes like this:

- create a dataset based on user input parameters
- open a connection to a copy of a spreadsheet template with the field names
already populated
- use an oledb command to populate the s'sheet

The method works fine normally, but falls over on long text strings due to
the well known 255 character limitation.

Is there any way I can push the long text strings into excel with a single
command?

Thanx
Niall
 
¤ I have seen this problem a number of times around the net, but never found a
¤ suitable solution...
¤
¤ I have a SQL Server view with two columns. One is 8 characters (varchar),
¤ the other is varchar(8000) and contains data from 500 characters up to 8000.
¤ I am using an ASP.Net page to give people the opportunity to export this data
¤ to Excel for analysis etc. Since it's an ASP.Net page, I thought OleDb to
¤ Excel would be the best way to do this, since Excel automation is not a good
¤ way to go on a server.
¤
¤ The method I am using goes like this:
¤
¤ - create a dataset based on user input parameters
¤ - open a connection to a copy of a spreadsheet template with the field names
¤ already populated
¤ - use an oledb command to populate the s'sheet
¤
¤ The method works fine normally, but falls over on long text strings due to
¤ the well known 255 character limitation.
¤
¤ Is there any way I can push the long text strings into excel with a single
¤ command?

If the following does not work then it probably is not possible via data access methods:

http://support.microsoft.com/default.aspx?scid=kb;en-us;281517


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
¤ How can I get the 'count' of records in Excel i.e the number of records

SELECT COUNT(*) AS ROW_COUNT FROM [Sheet1$]


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top