Writing _to_ Excel (*.xls) -file _from_ database with ASP

[Guest_imported]

isap (Visitor) Jul 16, 2001
How to insert data FROM MS SQL-database TO Microsoft Excel -file (*.xls)?

Do you have any good example, how to do this? I've searched for many days, and I haven't still found a good example showing how to do this. Even Microsoft's example from:

---------------------------------------------------------

http://support.microsoft.com/support/kb/articles/Q195/9/51.asp

---------------------------------------------------------

didn't work and I got the following error:

---------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Excel Driver] Operation must use an updateable query.

/aspexcel/excel_update.asp, line 17
---------------------------------------------------------

I wonder what's wrong with this?

I know that there's many components available, but I can't use them, due to strict budget, so it should be done with pure ASP.

Thank you!

 

[Rilez]

Well surely all that would be required would be to have a ODBC link to the excel spreadsheet. Then just update as you would a database. Don't know never done it but hope this gives you some ideas

 

[BarkingFrog]

If a column in your Excel spreadsheet contains both text and numbers, the Excel ODBC driver cannot correctly interpret which data type the column should be.

Does the data within any of your columns contain both text and numbers?

 

[wushutwist]

As BarkingFrog pointed out, there are some major problems with the Excel ODBC drivers. These types of issues basically make it useless for any type of real world application.
Wushutwist

 

[Rilez]

Ah well, sorry as I said I havn't used Excel ODBC before. Now know NOT ever to use it so thanks. _kp, sorry mate. But can't you use a different method? EG a csv file or even a small VB program to do the same thing. I'd even go as far as saying that you could possibily use access as a temporary store and then get the data out of that. (might help the data type being found but again I don't know)

 

[Guest_imported]

Yep, there's most likely columns with text and numbers in the client's database as they update it.

I think that, even if the data would go to the Excel -file, some problems will be encountered, because Excel-files most likely do not handle multi-user access. There would have been only one file and if many users will access it at the same time it wouldn't work.

So, I decided to use OWC (Office Web Components) to do the graphs. It stores them to Web-server as *.gif -images and then displays them (and the numeric data) in Excel embedded in IE.

You probably know this, but there's one "feature" in IIS4 that locks the image file for 60 seconds (it will remain locked 60 sec. even after closing the web-browser). So it cann't be deleted nor overwritten. The solution was to store these images elsewhere than in the server's

http://wwwroot

-path. Then files can be deleted or overwritten.

Thank you!