ASP.net and Excel

[zilonox]

First off, I hope this is the right area for this question - it seemed like the best.

Secondly, I don't know if this problem is an Excel problem, an ASP.net configuration problem, a SQL problem or an issue with the ACE OLEDB driver. I'm fully prepared to not receive an answer if it is deemed not an Excel issue (since this is a board for Excel and all), but I wanted to post it here just to eliminate possibilities.

Description of the Problem
I have a simple asp.net 2.0 web page set up that allows a user to input a start date and and end date and then generate an Excel spreadsheet from data stored in a SQL Server (2005) database.

I validate the dates before submitting them to a paramatized stored proceedure on the SQL Server. I also have an Excel spreadsheet stored on the web server that I copy, rename and then connect to via the ACE OLEDB driver to dump the results of the query into. I then save the spreadsheet and give the user a link to download the file from. (This is an Excel 2007 xlsm file (due to some VBA code I have in it - which works, btw) in case that is important.)

Now, this all works great, up to a point. I started getting word that some of the spreadsheets generated had no data in them. Through experimentation (trial and error), I was able to determine that everything would work so long as the query returned 10,993 records or less. One record more than that, and the spreadsheet would be blank when downloaded and opened.

Troubleshooting Steps I Have Taken
I am certain the stored proceedure is fine as when I run it through the SQL Server Management Console it returns all records (12,592 for the current 2013 YTD results).

Since the maximum file size of the spreadsheet is 1,264 kb when it has 10,993 rows of data in it, I didnt' figure the

[COLOR=#0000ff]<[/COLOR][COLOR=#8b4513]httpRuntime[/COLOR] [COLOR=#ff0000]maxRequestLength[/COLOR][COLOR=#0000ff]>[/COLOR]

attribute in the web.config would be the cause since it defaults to 4096, but I went ahead and upped it to 8192 anyway. As I suspected, it didnt' make a difference.

Additionally, I tried setting the

[COLOR=#0000ff]<[/COLOR][COLOR=#8b4513]httpRuntime[/COLOR] [COLOR=#ff0000]executionTimeout[/COLOR][COLOR=#0000ff]>[/COLOR]

attribute to 180 (3 minutes) just to make sure there was plenty of time to return data from the SQL Server, but that didn't help either.

I am certain it is not a problem with malformed data in the database since it doesn't matter what time frame I query against - as soon as the results grow beyond 10,993 rows the spreadsheet comes up blank.

I am certian it is not a problem with downloading the spreadsheet from the web server because the file is empty when I look at it on the web server.

Code Samples
Here is my code dealing with the spreadsheet (this is in VB.net in case it's not obvious):

[COLOR=#008000]' Create Excel connection string variable[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] cnExcel [COLOR=#0000ff]As String[/COLOR] = [COLOR=#8b4513]"Provider=Microsoft.ACE.OLEDB.12.0;"[/COLOR] _
            & [COLOR=#8b4513]"Data Source="[/COLOR] & xlfn _
            & [COLOR=#8b4513]";"[/COLOR] & [COLOR=#8b4513]"Extended Properties=""Excel 12.0 xml;HDR=YES"";"[/COLOR]

[COLOR=#008000]' Create the connection object by using the preceding connection string[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] oleExcel [COLOR=#0000ff]As New[/COLOR] OleDbConnection(cnExcel)

[COLOR=#008000]' Open connection with the workbook[/COLOR]
oleExcel.Open()

[COLOR=#008000]' Create new OleDbCommand to access the worksheet.[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] cmdExcel [COLOR=#0000ff]As New[/COLOR] OleDbCommand()


[COLOR=#008000]' Connect the command to the connection[/COLOR]
cmdExcel.Connection = oleExcel

I fill a SqlDataReader (myReader) with the results from the SQL query and then use a loop to insert that into the open spreadsheet (RawData is the name of the worksheet I'm inserting into):

[COLOR=#008000]' Now populate the spreadsheet[/COLOR]
[COLOR=#0000ff]While[/COLOR] myReader.Read
    strTemp = [COLOR=#8b4513]"INSERT INTO [RawData$] (ID, Name, Rep, Office, RecDate,"[/COLOR]
    strTemp &= [COLOR=#8b4513]" A01, A02, A03, A04, A05, A06, A07, A08, A09, A10, Comments)"[/COLOR]
    strTemp &= [COLOR=#8b4513]" values('"[/COLOR] & myReader.Item(0)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(1) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(2)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(3) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(4)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(5) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(6)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(7) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(8)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(9) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(10)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(11) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(12)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(13) & [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(14)
    strTemp &= [COLOR=#8b4513]"', '"[/COLOR] & myReader.Item(15) & [COLOR=#8b4513]" ')"[/COLOR]
    cmdExcel.CommandText = strTemp
    cmdExcel.ExecuteNonQuery()
[COLOR=#0000ff]End While[/COLOR]

In Conclusion
10,994 is no where near the row limit of Excel 2007, so if anything I would suspect the problem lies with either a data limit of the ACE OLEDB driver, or the timeout settings of the web server. As shown above, I've played with the web server settings and have not yet found a solution. That points me towards ACE being the issue, but I'm not sure how to go about testing/solving that. I'd greatly appreciate any pointers anyone can offer.

And again, I realize this may well not be an Excel/ACE issue at all and I am prepared to accept "Not our problem" if that is indeed the answer. It would at least eliminate some possibilities for me.

Thank you very much for the time you spent reading this.
Z