Getting Different Result When Running Code in Visual Studio 2012 Debugger and on Deployed Web Site

May 8, 2013 at 10:32 PM
Visual Studio Express 2012 for Web. Windows Server 2008 R2. IIS 7.0.

I have code that creates an Excel workbook from the results of a SQL query and then offers to open or download the Excel file. This code works fine and creates the expected files when run in the VS debugger. However, when run on the deployed site, the file downloaded is either empty or just contains the header. Ahyone know why the code would work in debug but not on the deployed site? The code is below.
 'Libraries needed for the NPOI Excel worksheet creation
Imports NPOI.HSSF.UserModel
Imports NPOI.HSSF.UserModel.HSSFCellStyle
Imports NPOI.HPSF
Imports NPOI.HSSF.Util
Imports NPOI.POIFS.FileSystem     

Private Sub DownloadMailMerge(Optional ByVal DType As String = "Group")
        Dim strSQL As String = "", Success As Boolean, MyDataSet As DataSet, I, NLines, NCol As Integer
        Dim SheetName As String, Row As HSSFRow, ShortFileName As String
        'Create workbook
        Dim xlWorkBook = New HSSFWorkbook()
        On Error GoTo DMM_Err
        SheetName = GroupDropDownList.SelectedItem.Text
        'Create Sheets
        Dim xlWorkSheet As HSSFSheet = xlWorkBook.CreateSheet(SheetName)
        xlWorkSheet.CreateRow(0).CreateCell(0).SetCellValue("test")
        With xlWorkSheet
            If DType = "Group" Then
                Row = xlWorkSheet.CreateRow(0)
                Row.CreateCell(0).SetCellValue("Name")
                Row.CreateCell(1).SetCellValue("Addressee")
                Row.CreateCell(2).SetCellValue("Salutation")
                Row.CreateCell(3).SetCellValue("AddressLine1")
                Row.CreateCell(4).SetCellValue("AddressLine2")
                Row.CreateCell(5).SetCellValue("City")
                Row.CreateCell(6).SetCellValue("State")
                Row.CreateCell(7).SetCellValue("Zip")
                Row.CreateCell(8).SetCellValue("Region")
                Row.CreateCell(9).SetCellValue("ChaplainSINGLE")
                Row.CreateCell(10).SetCellValue("ChaplainsPLURAL")
                Row.CreateCell(11).SetCellValue("StaffSINGLE")
                Row.CreateCell(12).SetCellValue("StaffPLURAL")
                Row.CreateCell(13).SetCellValue("Facility")
                Row.CreateCell(14).SetCellValue("LastGiftDate")
                Row.CreateCell(15).SetCellValue("LastGiftFundDescription")
                'Row.CreateCell(16).SetCellValue("LastGiftAppealDescription")
                NCol = 16
            ElseIf DType = "Top" Then
                Row = xlWorkSheet.CreateRow(0)
                Row.CreateCell(0).SetCellValue("Name")
                Row.CreateCell(1).SetCellValue("Addressee")
                Row.CreateCell(2).SetCellValue("Salutation")
                Row.CreateCell(3).SetCellValue("AddressLine1")
                Row.CreateCell(4).SetCellValue("AddressLine2")
                Row.CreateCell(5).SetCellValue("City")
                Row.CreateCell(6).SetCellValue("State")
                Row.CreateCell(7).SetCellValue("Zip")
                Row.CreateCell(8).SetCellValue("Region")
                Row.CreateCell(9).SetCellValue("ChaplainSINGLE")
                Row.CreateCell(10).SetCellValue("ChaplainsPLURAL")
                Row.CreateCell(11).SetCellValue("StaffSINGLE")
                Row.CreateCell(12).SetCellValue("StaffPLURAL")
                Row.CreateCell(13).SetCellValue("Facility")
                Row.CreateCell(14).SetCellValue("Director")
                NCol = 15
            Else
            End If
            'wrap the address block
            Dim WT As HSSFCellStyle = xlWorkBook.CreateCellStyle
            WT.WrapText = True
            xlWorkSheet.SetDefaultColumnStyle(0, WT)
        End With
        If DType = "Group" Then
            'Build the query
            Dim strDate, strAmount As String
            'Build the WHERE clause for the date.
            strDate = BuildDateString()
            'Build WHERE clause for amounts for group.
            strAmount = BuildAmountString()
            'Combine the date and amount criteria into main SQL statement.
            strSQL = GetMainSQL(strDate, strAmount)
            ShortFileName = Replace(GroupDropDownList.SelectedItem.Text, " ", "") + ".xls"
        ElseIf DType = "Top" Then
            'strSQL = "SELECT Name,Addressee,Salutation,AddressLine1,AddressLine2,City,State,Zip,Region,Chaplain,Director,LastGiftFund FROM [!Extranet_Appeal_Top_Donor_v]" ' ORDER BY RECORDS_SEARCHNAME.KEY_NAME, RECORDS_SEARCHNAME.FIRST_NAME, RECORDS_SEARCHNAME.MIDDLE_NAME
            strSQL = GetTopDonorSQL()
            ShortFileName = "Top Donor.xls"
        End If
        Session("ALMMSelect") = strSQL
        Dim Name, Addressee, Salutation, AddressLine1, AddressLine2, City, State, Zip, Region, Chaplain, Chaplains, Staff, Staffs, Director, Facility, LastGiftDate, LastGiftFund, LastGiftFundID, LastGiftAppeal As String
        Dim strTmp As String = ""
        Dim II, JJ As Integer
        MyDataSet = Utilities.GetDataSet3(strSQL, Success)
        If MyDataSet.Tables(0).Rows.Count > 0 Then
            NLines = 0
            For Each dr As DataRow In MyDataSet.Tables(0).Rows
                Chaplain = "" : Chaplains = "" : Staff = "" : Staffs = ""
                Name = IIf(IsDBNull(dr("Name")), "", dr("Name"))
                Addressee = IIf(IsDBNull(dr("Addressee")), "", dr("Addressee"))
                Salutation = IIf(IsDBNull(dr("Salutation")), "", dr("Salutation"))
                AddressLine1 = IIf(IsDBNull(dr("AddressLine1")), "", dr("AddressLine1"))
                AddressLine2 = IIf(IsDBNull(dr("AddressLine2")), "", dr("AddressLine2"))
                City = IIf(IsDBNull(dr("City")), "", dr("City"))
                State = IIf(IsDBNull(dr("State")), "", dr("State"))
                Zip = IIf(IsDBNull(dr("Zip")), "", dr("Zip"))
                Region = IIf(IsDBNull(dr("Region")), "", dr("Region"))
                LastGiftFund = IIf(IsDBNull(dr("LastGiftFund")), "", dr("LastGiftFund"))
                LastGiftFundID = IIf(IsDBNull(dr("LastGiftFundID")), "", dr("LastGiftFundID"))
                strTmp = ChaplainDict(LastGiftFundID)
                If InStr(strTmp, "Chaplains") > 0 Or InStr(strTmp, " chaplains ") > 0 Then
                    Chaplains = strTmp
                ElseIf InStr(strTmp, "Chaplain") > 0 Then
                    Chaplain = strTmp
                ElseIf InStr(strTmp, " and ") > 0 Then
                    Staffs = strTmp
                Else
                    Staff = strTmp
                End If
                Facility = FacilityDict(LastGiftFundID)
                If DType = "Group" Then
                    'LastGiftAppeal = IIf(IsDBNull(dr("LastGiftAppeal")), "", dr("LastGiftAppeal"))
                    LastGiftDate = IIf(IsDBNull(dr("LastGiftDate")), "", dr("LastGiftDate"))
                ElseIf DType = "Top" Then
                    Director = IIf(IsDBNull(dr("Director")), "", dr("Director"))
                End If
                'Do Not process benevolence fund gifts
                If InStr(LCase(LastGiftFundID), "0098Ben") = 0 And InStr(LCase(LastGiftFundID), "0098PHitch") = 0 Then
                    NLines = NLines + 1
                    With xlWorkSheet
                        Row = xlWorkSheet.CreateRow(NLines)
                        Row.CreateCell(0).SetCellValue(Name)
                        'Adjust row height for number of lines in address block
                        'Row.HeightInPoints = 14 * NAddressLines
                        Row.CreateCell(1).SetCellValue(Addressee)
                        Row.CreateCell(2).SetCellValue(Salutation)
                        Row.CreateCell(3).SetCellValue(AddressLine1)
                        Row.CreateCell(4).SetCellValue(AddressLine2)
                        Row.CreateCell(5).SetCellValue(City)
                        Row.CreateCell(6).SetCellValue(State)
                        Row.CreateCell(7).SetCellValue(Zip)
                        Row.CreateCell(8).SetCellValue(Region)
                        Row.CreateCell(9).SetCellValue(Chaplain)
                        Row.CreateCell(10).SetCellValue(Chaplains)
                        Row.CreateCell(11).SetCellValue(Staff)
                        Row.CreateCell(12).SetCellValue(Staffs)
                        Row.CreateCell(13).SetCellValue(Facility)
                        If DType = "Group" Then
                            Row.CreateCell(14).SetCellValue(LastGiftDate)
                            Row.CreateCell(15).SetCellValue(LastGiftFund)
                            Row.CreateCell(16).SetCellValue(LastGiftFundID)
                            'Row.CreateCell(17).SetCellValue(LastGiftAppeal)
                        ElseIf DType = "Top" Then
                            Row.CreateCell(14).SetCellValue(Director)
                        End If
                    End With
                Else
                    'Beep()
                End If
            Next
            'Format the columns to fit (autosize)
            For I = 0 To NCol - 1
                'xlWorkSheet.SetColumnWidth(I, 20 * 256)
                xlWorkSheet.AutoSizeColumn(I)
            Next
            'Create XLS file in memory
            Dim file As New MemoryStream()
            xlWorkBook.Write(file)
            'Stream file to browser
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", ShortFileName))
            Response.Clear()
            Response.BinaryWrite(file.GetBuffer())
            Response.End()
        End If
DMM_Exit:
        Exit Sub
DMM_Err:
        Resume Next
    End Sub