Writing to Excel using ASP.NET

[timothym]

Private Sub Page_Load(ByVal sender As System.Object, ByVal e A
System.EventArgs) Handles MyBase.Load

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Dat
Source='C:\Timmy\Demographic Application\CENTRAL.XLS';Extende
Properties=""Excel 8.0;HDR=NO"""
Dim conn As New OleDbConnection(strConn)
Dim strSQL As String = "UPDATE [General$A86:A86] SET F1
2000"
Dim cmd As New OleDbCommand(strSQL)
cmd.Connection = conn
conn.Open()
Dim rows As Integer = cmd.ExecuteNonQuery()
cmd.Connection.Close()
conn.Close()
conn = Nothing
End Sub

Hi!

I found the following code (or very similar) in an example. I want t
write to the A86 cell of the General sheet in the workbook mentione
in the connection string. However, the problem is that after I run th
asp.net test example above, and then try to open the spreadsheet, Exce
says that there is a "memory write problem" and also that it is locke
for editing. I cannot even delete the spreadsheet because Windows say
there is a sharing violation. I do not know what is really wrong wit
the above code as I practically copied it from the MSDN website.
would greatly appreciate any help!

Thanks!
Timoth

 

[Bill Manville]

The microsoft.public.excel.programming newsgroup may not be the most
appropriate one to ask this question in, since I don't think many of
the regular participants here have any knowledge of ASP.NET

(I hope someone will jump in and prove me wrong).

Maybe one of the Microsoft.public.dotnet.framework.aspnet newsgroups
would be more appropriate.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[onedaywhen]

Jump! My knowledge of ASP.NET is limited though, so I tried your code
from a VB.NET Windows application. The code worked and I could
successfully open the workbook afterwards. I don't see why the results
would be any different from an ASP.NET app. I do this kind of thing
with 'ADO classic' all the time and I don't recall seeing the errors
you describe. Have you tried with a new blank workbook (containing a
worksheet called General, of course)?

 

[timothym]

Hi,

I am using VB.NET. I made a form with a button which, when clicked does
the excel update. However, the memory problem still occurs as before.
Here is the button click handler. I need to do this operation on the
existing worksheet (i.e. General). I found an example which created a
spreadsheet and then used my syntax (actually I copied it! ) to do
various tasks to the file. It worked when I used it in its entirety,
but when I tailored it to my needs (i.e. to do just the update on MY
..xls file) it gave me the same problems....I do not know what is
happening!

But anyway, thanks for your help!


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim conn As New System.Data.OleDb.OleDbConnection(strconn1)
conn.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "UPDATE [Births$C4:C4] SET F1 = 2000"
cmd.ExecuteNonQuery()
conn.Close()
Application.Exit()
End Sub

 

[timothym]

Could it be that it is because this spreadsheet contains automatic link
to other data. Bacause when I open it normally, it pops up with
message box saying "The workbook you opened contains automatic links t
information in another workbook. Do you want to update this workboo
with changes made to the other workbook?" I always click no, and wor
normally, but maybe with the ADO insert it causes some malfunction.
don't really know!
Your help would be greatly appreciated!

Thanks!
Timoth

 

[timothym]

OK problem solved!

It turned out that I was specifying Extended Properties = Excel 8.0
when the spreadsheeth was written with an older version of Excel.
converted the spreadsheet to the newer version and then the update
worked!

Thanks,
Timoth