Hi out there,



I desperately need your help, because I don't know what to do. My problem 

to export data from a database (which are displayed on a webpage) to a 

excel sheet.



(1) The "application/vnd.ms-excel" is working, but I don't like it very 

much...



(2) The solution via "CreateObject("Excel.Application")" is my favorite, 

but it's not working. Not even Excel is opening !

-> Does anybody know what the reason could be ? Does Excel need to be 

installed on the web-server ?



(3) I managed to create a ADO connection to the Excel file and it works 

fine to read our data from Excel. However, I'm not able to write data to 

the Excel sheets. The "xlSheet.Update" command (see below) produces

"error '80004005' [Microsoft][ODBC Excel Driver] Operation must use an 

updatable query". I already tried to set standard-write-rights to the 

folder on the web server, where the file is located.

-> Does anybody has some hints for me...



(4) Does anybody know of another possibility to get this working?



Thanks in advance and I really appreciate any help...

Regards Matthias





Here is my code for (3):

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&

     TempFile & ";DefaultDir=" & Server.MapPath("\") & TempPath

Set xlSheet = Server.CreateObject("ADODB.Recordset")

xlSheet.ActiveConnection = oConn

xlSheet.LockType = 2

xlSheet.CursorType = 3

xlSheet.Open "Select * from myRange", oConn, 1, 3

xlSheet.AddNew

xlSheet.Fields(0).Value = "Test0"

xlSheet.Fields(1).Value = "Test1"

xlSheet.Update

(2) Yes, Excel needs to be on the server.  I don't know if some subset of 

Excel component will do the trick as well.



(3) The help document I read on this error said that 'full Control' for 

IUSR_servername is needed on the file.  If this is a problem in the 

directory you are using, make a separate directory just for the Excel 

files and include Full Control rights for IUSR_servername.



- George



>>> mhambach@w... 08/21/01 09:32AM >>>

Hi out there,



I desperately need your help, because I don't know what to do. My problem

to export data from a database (which are displayed on a webpage) to a

excel sheet.



(1) The "application/vnd.ms-excel" is working, but I don't like it very

much...



(2) The solution via "CreateObject("Excel.Application")" is my favorite,

but it's not working. Not even Excel is opening !

-> Does anybody know what the reason could be ? Does Excel need to be

installed on the web-server ?



(3) I managed to create a ADO connection to the Excel file and it works

fine to read our data from Excel. However, I'm not able to write data to

the Excel sheets. The "xlSheet.Update" command (see below) produces

"error '80004005' [Microsoft][ODBC Excel Driver] Operation must use an

updatable query". I already tried to set standard-write-rights to the

folder on the web server, where the file is located.

-> Does anybody has some hints for me...



(4) Does anybody know of another possibility to get this working?



Thanks in advance and I really appreciate any help...

Regards Matthias





Here is my code for (3):

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=3D790;Dbq

"&

     TempFile & ";DefaultDir=" & Server.MapPath("\") & TempPath

Set xlSheet = Server.CreateObject("ADODB.Recordset")

xlSheet.ActiveConnection = oConn

xlSheet.LockType = 2

xlSheet.CursorType = 3

xlSheet.Open "Select * from myRange", oConn, 1, 3

xlSheet.AddNew

xlSheet.Fields(0).Value = "Test0"

xlSheet.Fields(1).Value = "Test1"

xlSheet.Update

George,



thanks a lot for your help.



Where do you get this help document? It's a book or online help of any 

tool? I talked with our IT administration guys. They say, that there is no 

such user existing so that you can't give him rights...! I tried to 

give "full access" rights to everyone as a default, but still get the same 

problem....



Is ths user called 'IUSR' or 'IUSER' or 'IUSR_Servername'?



Regards

Matthias



> (2) Yes, Excel needs to be on the server.  I don't know if some subset

> of Excel component will do the trick as well.

> 

> (3) The help document I read on this error said that 'full Control' for 

> IUSR_servername is needed on the file.  If this is a problem in the 

> directory you are using, make a separate directory just for the Excel 

> files and include Full Control rights for IUSR_servername.

> 

> - George

OK. I noticed the Internet guest account also must have write permissions 

on the database file.  To answer your question on the accounts: IUSR_server

name Internet guest account under IIS, such as IUSR_MYSERVER.  This 

account is setup automatically when IIS is installed and is a local 

account because your web server is a standalone server.  Or at least it 

should be.  Here's the article:





MS Knowledgebase

PRB: ASP Returns 'Operation Must Use an Updateable Query' Error

ID: Q175168





The information in this article applies to:



Active Server Pages

Microsoft Internet Information Server versions 4.0, 5.0

ActiveX Data Objects (ADO), versions 2.0, 2.1, 2.1 SP2, 2.5

Microsoft Data Access Components version 2.5









SYMPTOMS

The following is a common error encountered when using ActiveX Data 

Objects (ADO) with Active Server Pages:





Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access 97 Driver] Operation must use an 

updateable query.





CAUSE

This article explains the three primary causes of this error, and the 

workarounds. Although this article refers to Microsoft Access databases, 

the information provided here also applies to other types of databases.





RESOLUTION

This error is typically encountered when your script attempts to perform 

an UPDATE or some other action that alters the information in the 

database. This error occurs because ADO is unable to write to the database 

for one of the following reasons:





The most common reason is that the Internet Guest account (IUSR_MACHINE) 

does not have Write permissions on the database file (.mdb). To fix this 

problem, use the Security tab in Explorer to adjust the properties for 

this file so that the Internet Guest account has the correct permissions. 

NOTE: When using Microsoft Access databases with ADO, it is also necessary 

to give the Internet Guest account Write permissions on the directory 

containing the .mdb file. This is because Jet creates an .ldb file to 

handle database locking.





A second cause of this error is that the database was not opened with the 

correct MODE for writing. If you perform the Open on the Connection 

object, you use the Mode property to indicate the permissions on the 

connection as shown here:









      SQL = "UPDATE Products Set UnitPrice = 2;"

      Set Conn = Server.CreateObject("ADODB.Connection")

      Conn.Mode = 3      '3 = adModeReadWrite

      Conn.Open "myDSN"

      Conn.Execute(SQL)

      Conn.Close



NOTE: By default, the MODE is set to 0(adModeUnknown), which generally 

allows updates.





Another cause of this error is that the "Read Only" setting may be checked 

in the Options page for this DSN in the ODBC Manager.





The last issue and work around pertains to any SQL data source. The error 

can be caused by SQL statements that violate referential integrity of the 

database. Here are a few of the most common queries that fail:







The simplest group to deal with are those you cannot change: crosstab, SQL 

pass-through, union, or update (or make-table) action queries that have 

UniqueValue properties set to Yes.





Another very common cause is when the join includes linked ODBC tables 

that do not have unique indexes. In this case, there is no way for SQL to 

guarantee that records are unique in a table that has fields whose value 

will change with the query.





One cause does have a robust workaround. If you try to update a join field 

on the "one" side of a "one-to-many" query it will fail unless you turn on 

cascading updates. This way, you delegate referential integrity to the JET 

engine.





STATUS

This behavior is by design.

Additional query words: kbdsi

Keywords : kberrmsg kbADO kbASP kbDatabase kbGrpASP kbGrpMDAC kbDSupport 

kbMDAC210SP2 kbMDAC250 kbiis400 kbiis500 kbGrpASPDB

Version : WINDOWS:2.0,2.1,2.1 SP2,2.5; winnt:4.0,5.0

Platform : WINDOWS winnt

Issue type : kbprb



>>> mhambach@w... 08/21/01 01:46PM >>>

George,



thanks a lot for your help.



Where do you get this help document? It's a book or online help of any

tool? I talked with our IT administration guys. They say, that there is no

such user existing so that you can't give him rights...! I tried to

give "full access" rights to everyone as a default, but still get the same

problem....



Is ths user called 'IUSR' or 'IUSER' or 'IUSR_Servername'?



Regards

Matthias



> (2) Yes, Excel needs to be on the server.  I don't know if some subset

> of Excel component will do the trick as well.

>

> (3) The help document I read on this error said that 'full Control' for

> IUSR_servername is needed on the file.  If this is a problem in the

> directory you are using, make a separate directory just for the Excel 

> files and include Full Control rights for IUSR_servername.

>

> - George

Good Luck... I use application/vnd.ms-excel  as I have tried to use the

CreateObject and keep getting a message that I cannot access the object and

I do have Excel on the machine, if anyone knows how to get around this it

would be greatly appeciated. Paul.



-----Original Message-----

From: Matthias [mailto:mhambach@w...]

Sent: August 21, 2001 9:33 AM

To: ASP Web HowTo

Subject: [asp_web_howto] Tricky problem: Data export to Excel...





Hi out there,



I desperately need your help, because I don't know what to do. My problem

to export data from a database (which are displayed on a webpage) to a

excel sheet.



(1) The "application/vnd.ms-excel" is working, but I don't like it very

much...



(2) The solution via "CreateObject("Excel.Application")" is my favorite,

but it's not working. Not even Excel is opening !

-> Does anybody know what the reason could be ? Does Excel need to be

installed on the web-server ?



(3) I managed to create a ADO connection to the Excel file and it works

fine to read our data from Excel. However, I'm not able to write data to

the Excel sheets. The "xlSheet.Update" command (see below) produces

"error '80004005' [Microsoft][ODBC Excel Driver] Operation must use an

updatable query". I already tried to set standard-write-rights to the

folder on the web server, where the file is located.

-> Does anybody has some hints for me...



(4) Does anybody know of another possibility to get this working?



Thanks in advance and I really appreciate any help...

Regards Matthias





Here is my code for (3):

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&

     TempFile & ";DefaultDir=" & Server.MapPath("\") & TempPath

Set xlSheet = Server.CreateObject("ADODB.Recordset")

xlSheet.ActiveConnection = oConn

xlSheet.LockType = 2

xlSheet.CursorType = 3

xlSheet.Open "Select * from myRange", oConn, 1, 3

xlSheet.AddNew

xlSheet.Fields(0).Value = "Test0"

xlSheet.Fields(1).Value = "Test1"

xlSheet.Update

If you know the name of the dll the objects are being called from you can

modify the permissions on the dll to include the web server Internet guest

account, IUSR_machine.   This is sometimes a requirement when using a

custom dll.  You might try it at least.



- George



>>> paulbeaulieu@s... 08/22/01 07:43AM >>>

Good Luck... I use application/vnd.ms-excel  as I have tried to use the

CreateObject and keep getting a message that I cannot access the object and

I do have Excel on the machine, if anyone knows how to get around this it

would be greatly appeciated. Paul.



-----Original Message-----

From: Matthias [mailto:mhambach@w...]

Sent: August 21, 2001 9:33 AM

To: ASP Web HowTo

Subject: [asp_web_howto] Tricky problem: Data export to Excel...





Hi out there,



I desperately need your help, because I don't know what to do. My problem

to export data from a database (which are displayed on a webpage) to a

excel sheet.



(1) The "application/vnd.ms-excel" is working, but I don't like it very

much...



(2) The solution via "CreateObject("Excel.Application")" is my favorite,

but it's not working. Not even Excel is opening !

-> Does anybody know what the reason could be ? Does Excel need to be

installed on the web-server ?



(3) I managed to create a ADO connection to the Excel file and it works

fine to read our data from Excel. However, I'm not able to write data to

the Excel sheets. The "xlSheet.Update" command (see below) produces

"error '80004005' [Microsoft][ODBC Excel Driver] Operation must use an

updatable query". I already tried to set standard-write-rights to the

folder on the web server, where the file is located.

-> Does anybody has some hints for me...



(4) Does anybody know of another possibility to get this working?



Thanks in advance and I really appreciate any help...

Regards Matthias





Here is my code for (3):

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq

"& TempFile & ";DefaultDir=" & Server.MapPath("\") & TempPath

Set xlSheet = Server.CreateObject("ADODB.Recordset")

xlSheet.ActiveConnection = oConn

xlSheet.LockType = 2

xlSheet.CursorType = 3

xlSheet.Open "Select * from myRange", oConn, 1, 3

xlSheet.AddNew

xlSheet.Fields(0).Value = "Test0"

xlSheet.Fields(1).Value = "Test1"

xlSheet.Update

Hi Matthias:



I've read your e-mails below and I got interested in how to use excel

with asp.



I have an intranet project that uses an Access database of products, and

from this database I need to build an excel worksheet.



But I got one problem: how do I insert new rows in an excel worksheet by

using asp?



Your below code seems to be very simple. Now I only need to know to add

new rows.



Can you help me out?



Regards,



Flavio.





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

Subject: Tricky problem: Data export to Excel...

From: "Matthias" <mhambach@w...>

Date: Tue, 21 Aug 2001 09:32:56

X-Message-Number: 10



Hi out there,



I desperately need your help, because I don't know what to do. My

problem 

to export data from a database (which are displayed on a webpage) to a 

excel sheet.



(1) The "application/vnd.ms-excel" is working, but I don't like it very 

much...



(2) The solution via "CreateObject("Excel.Application")" is my favorite,



but it's not working. Not even Excel is opening !

-> Does anybody know what the reason could be ? Does Excel need to be

installed on the web-server ?



(3) I managed to create a ADO connection to the Excel file and it works 

fine to read our data from Excel. However, I'm not able to write data to



the Excel sheets. The "xlSheet.Update" command (see below) produces

"error '80004005' [Microsoft][ODBC Excel Driver] Operation must use an 

updatable query". I already tried to set standard-write-rights to the 

folder on the web server, where the file is located.

-> Does anybody has some hints for me...



(4) Does anybody know of another possibility to get this working?



Thanks in advance and I really appreciate any help...

Regards Matthias





Here is my code for (3):

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&

     TempFile & ";DefaultDir=" & Server.MapPath("\") & TempPath

Set xlSheet = Server.CreateObject("ADODB.Recordset")

xlSheet.ActiveConnection = oConn

xlSheet.LockType = 2

xlSheet.CursorType = 3

xlSheet.Open "Select * from myRange", oConn, 1, 3

xlSheet.AddNew

xlSheet.Fields(0).Value = "Test0"

xlSheet.Fields(1).Value = "Test1"

xlSheet.Update

A couple things about CreateObject("Excel.Application"):



1) Excel must be installed on the web server for this to work.  You can't

create a COM component that is not installed and registered.

Should you decide to install Excel on the web server...

2) Excel is not intended or optimized for web applications.

3) Excel is an out of process server.  This means that you would need to

modify your IIS settings to allow for the creation of out of process

components.





You may want to check out some of the components that are on the market for

creating Excel files in a web application environment.



=================================

Kyle M. Burns, MCSD

ECommerce Technology Manager

Centra Credit Union

kburns@c...



 



-----Original Message-----

From: Matthias [mailto:mhambach@w...]

Sent: Tuesday, August 21, 2001 4:33 AM

To: ASP Web HowTo

Subject: [asp_web_howto] Tricky problem: Data export to Excel...





Hi out there,



I desperately need your help, because I don't know what to do. My problem 

to export data from a database (which are displayed on a webpage) to a 

excel sheet.



(1) The "application/vnd.ms-excel" is working, but I don't like it very 

much...



(2) The solution via "CreateObject("Excel.Application")" is my favorite, 

but it's not working. Not even Excel is opening !

-> Does anybody know what the reason could be ? Does Excel need to be 

installed on the web-server ?



(3) I managed to create a ADO connection to the Excel file and it works 

fine to read our data from Excel. However, I'm not able to write data to 

the Excel sheets. The "xlSheet.Update" command (see below) produces

"error '80004005' [Microsoft][ODBC Excel Driver] Operation must use an 

updatable query". I already tried to set standard-write-rights to the 

folder on the web server, where the file is located.

-> Does anybody has some hints for me...



(4) Does anybody know of another possibility to get this working?



Thanks in advance and I really appreciate any help...

Regards Matthias





Here is my code for (3):

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="&

     TempFile & ";DefaultDir=" & Server.MapPath("\") & TempPath

Set xlSheet = Server.CreateObject("ADODB.Recordset")

xlSheet.ActiveConnection = oConn

xlSheet.LockType = 2

xlSheet.CursorType = 3

xlSheet.Open "Select * from myRange", oConn, 1, 3

xlSheet.AddNew

xlSheet.Fields(0).Value = "Test0"

xlSheet.Fields(1).Value = "Test1"

xlSheet.Update