I have a page with several list/menu drop down boxes in which the user selects criteria. Then, upon clicking "Submit", the criteria is passed to a aspx page with a datagrid which displays the data using the user's criteria as parameters for the dataset. When the user selects to see all the data (rather than selecting particular criteria), they get either no results or only a few results, and at the top of the page they get the following error message:
System.OutOfMemoryException: Exception of type System.OutOfMemoryException was thrown.
The query is not particularly large (less than 800 items), and I have successfully displayed all values of queries that were much larger (I have one that is over 2800 records). All I can figure is that the query is too complex, since it evaluates six different parameters and involves several tables in the database. However I do not know how to get around it.
Here is my query, if it is any help:
SELECT [Table-Sections].FacilityID, [Table-Facility].FacilityName, [Table-Facility].SchoolLevel, [Table-Surveys].FeederZone, [Table-Sections].SectionID, [List-Sections].SectionName, [List-Sections].SectionSort, [Table-Sections].SystemTypeID, [List-SystemType].SystemType, [Table-Sections].YearInstalled, [Table-Sections].ExpectancyID, [List-Expectancy].Expectancy, [Table-Sections].SquareFootage, [Table-Sections].CostperSqFt, [Table-Sections].SlopeID, [List-Slope].Slope, [Table-Sections].History, [Table-Sections].[Photo-Footprint], [Table-Sections].[Photo-Overall], [Table-Sections].Cost, [Table-Sections].SummaryCost, [Table-Sections].WarrantyPeriod, [Table-Sections].ManufacturerName, [Table-Sections].RoofingContractor, [Table-Sections].EstimatedRepairYear
FROM ((([Table-Surveys] RIGHT JOIN ([List-Sections] RIGHT JOIN ([Table-Facility] RIGHT JOIN [Table-Sections] ON [Table-Facility].FacilityID = [Table-Sections].FacilityID) ON [List-Sections].SectionID = [Table-Sections].SectionID) ON [Table-Surveys].FacilityID = [Table-Sections].FacilityID) INNER JOIN [List-SystemType] ON [Table-Sections].SystemTypeID = [List-SystemType].SystemTypeID) INNER JOIN [List-Expectancy] ON [Table-Sections].ExpectancyID = [List-Expectancy].ExpectancyID) INNER JOIN [List-Slope] ON [Table-Sections].SlopeID = [List-Slope].SlopeID
WHERE ((([Table-Facility].SchoolLevel) Like '%'+?+'%') AND (([List-Sections].SectionName) Like '%'+?) AND (([List-SystemType].SystemType) Like '%'+?+'%') AND (([Table-Sections].YearInstalled) Like '%'+?+'%') AND (([List-Expectancy].Expectancy) Like '%'+?+'%') AND (([List-Slope].Slope) Like '%'+?+'%'))
ORDER BY [Table-Facility].FacilityName, [List-Sections].SectionSort
The query works successfully if you drill down enough (to where only one or two records are displayed), but not if all or many records are queried. Any help would be greatly appreciated.