ODBC access to an excel spread sheet in asp

[boylesg]

The following code seems to work sometimes, but often craps out on this line: objConn.Open("Invoice"); with an internal server error.I am running it on my own PC from [url="http://localhost......"]http://localhost......[/url]..What have I done wrong to cause this intermittent error? var objConn = Server.CreateObject("ADODB.Connection"); objConn.Open("Invoice"); var objRS = Server.CreateObject("ADODB.Recordset"); objRS.ActiveConnection = objConn; objRS.CursorType = 3; // Static cursor. objRS.LockType = 2; // Pessimistic Lock. objRS.Source = "Select * from BILLING"; objRS.Open(); Response.Write("Original Data"); Response.Write("<TABLE><TR>"); for (X = 0; X < objRS.Fields.Count; X++) { Response.Write("<TD>" + objRS.Fields.Item(X).Name + "</TD>"); } Response.Write("</TR>"); objRS.MoveFirst(); while (!objRS.EOF) { Response.Write("<TR>") for (X = 0; X < objRS.Fields.Count; X++) { Response.write("<TD>" + objRS.Fields.Item(X).Value + "</TD>"); } objRS.MoveNext(); Response.Write("</TR>"); } Response.Write("</TR></TABLE"); objRS.Close(); objConn.Close();

[boylesg]

My code is simply a javascript modification of the following code from http://support.microsoft.com/kb/195951 <!-- Begin ASP Source Code --> <%@ LANGUAGE="VBSCRIPT" %> <% Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "ADOExcel" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.ActiveConnection = objConn objRS.CursorType = 3 'Static cursor. objRS.LockType = 2 'Pessimistic Lock. objRS.Source = "Select * from myRange1" objRS.Open %> <br> <% Response.Write("Original Data") 'Printing out original spreadsheet headings and values. 'Note that the first recordset does not have a "value" property 'just a "name" property. This will spit out the column headings. Response.Write("<TABLE><TR>") For X = 0 To objRS.Fields.Count - 1 Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>") Next Response.Write("</TR>") objRS.MoveFirst While Not objRS.EOF Response.Write("<TR>") For X = 0 To objRS.Fields.Count - 1 Response.write("<TD>" & objRS.Fields.Item(X).Value) Next objRS.MoveNext Response.Write("</TR>") Wend Response.Write("</TABLE>") 'The update is made here objRS.MoveFirst objRS.Fields(0).Value = "change" objRS.Fields(1).Value = "look" objRS.Fields(2).Value = "30" objRS.Update 'Printing out spreadsheet headings and values after update. Response.Write("<br>Data after the update") Response.Write("<TABLE><TR>") For X = 0 To objRS.Fields.Count - 1 Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>") Next Response.Write("</TR>") objRS.MoveFirst While Not objRS.EOF Response.Write("<TR>") For X = 0 To objRS.Fields.Count - 1 Response.write("<TD>" & objRS.Fields.Item(X).Value) Next objRS.MoveNext Response.Write("</TR>") Wend Response.Write("</TABLE>") 'ADO Object clean up. objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing %> <!-- End ASP Source Code -->

[boylesg]

[quote name='Greg Boyles' post='174481' date='Jun 19 2010, 03:14 PM']The following code seems to work sometimes, but often craps out on this line: objConn.Open("Invoice"); with an internal server error.I am running it on my own PC from [url="http://localhost......"]http://localhost......[/url]..What have I done wrong to cause this intermittent error? var objConn = Server.CreateObject("ADODB.Connection"); objConn.Open("Invoice"); var objRS = Server.CreateObject("ADODB.Recordset"); objRS.ActiveConnection = objConn; objRS.CursorType = 3; // Static cursor. objRS.LockType = 2; // Pessimistic Lock. objRS.Source = "Select * from BILLING"; objRS.Open(); Response.Write("Original Data"); Response.Write("<TABLE><TR>"); for (X = 0; X < objRS.Fields.Count; X++) { Response.Write("<TD>" + objRS.Fields.Item(X).Name + "</TD>"); } Response.Write("</TR>"); objRS.MoveFirst(); while (!objRS.EOF) { Response.Write("<TR>") for (X = 0; X < objRS.Fields.Count; X++) { Response.write("<TD>" + objRS.Fields.Item(X).Value + "</TD>"); } objRS.MoveNext(); Response.Write("</TR>"); } Response.Write("</TR></TABLE"); objRS.Close(); objConn.Close();[/quote]More precisely if I change objRS.Source = "Select * from BILLING"; to another field set immediately after viewing a different field set then the page crashes with an internal server error. But it seems that if I leave IE for several minutes before refreshing the page with the changes to objRS.Source = "Select * from BILLING"; then the data from the new field set is displayed.