I have a website in ASP.net using Vb.net as code behind it is throwing errors for certain values of timestamps when I am trying to retrieve data in a grid view. What could be the problem? The error is System out of memory the website is hosted on a server which is IIS.
My code behind is as follows:
Inherits System.Web.UI.Page
Public dad As OleDbDataAdapter
Protected Sub Page_Load(ByVal sender As Object, e As EventArgs) Handles Me.Load
End Sub
Protected Sub TextBox2_TextChanged(sender As Object, e As EventArgs) Handles TextBox2.TextChanged
End Sub
Protected Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles TextBox3.TextChanged
End Sub
Protected Sub btnSave_Click1(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
Dim connectionString As [String] = "Provider=Microsoft.ACE.OLEDB.12.0;Data" + " Source=C:\Program Files (x86)\GL Communications Inc\DataImport\VQT.mdb"
Dim ds As New DataSet()
Dim conn As New OleDbConnection(connectionString)
conn.Open()
Dim cmd As New OleDbCommand("SELECT ROUND(AVG(POLQA_Score),3) AS mean_POLQA_score FROM VQTPOLQA WHERE VQT_Timestamp BETWEEN ? AND ? AND VQuad_PhoneID =?", conn)
Dim cmd2 As New OleDbCommand("SELECT ROUND(Stdev(POLQA_score),3) AS stdev_POLQA_score FROM VQTPOLQA WHERE VQT_Timestamp BETWEEN ? AND ? AND VQuad_PhoneID=?", conn)
Dim cmd3 As New OleDbCommand("SELECT ROUND(AVG(RTD),3) AS mean_RTD FROM VQuadData WHERE VQuad_Timestamp BETWEEN ? AND ? AND RTD>0 AND VQuad_PhoneID=?", conn)
Dim cmd4 As New OleDbCommand("SELECT ROUND(Stdev(RTD),3) AS STDEV_RTD FROM VQuadData WHERE VQuad_Timestamp BETWEEN ? AND? AND RTD>0 AND VQuad_PhoneID=?", conn)
Dim param As OleDbParameter
param = cmd.CreateParameter
param.OleDbType = OleDbType.Date
param.Value = DateTime.Parse(TextBox3.Text)
param.ParameterName = "@StartDate"
cmd.Parameters.Add(param)
Dim param1 As OleDbParameter
param1 = cmd.CreateParameter
param1.OleDbType = OleDbType.Date
param1.Value = DateTime.Parse(TextBox2.Text)
param1.ParameterName = "@EndDate"
cmd.Parameters.Add(param1)
GridView1.DataBind()
Dim param2 As OleDbParameter
param2 = cmd2.CreateParameter
param2.OleDbType = OleDbType.Date
param2.Value = DateTime.Parse(TextBox3.Text)
param2.ParameterName = "@StartDate"
cmd2.Parameters.Add(param2)
Dim param3 As OleDbParameter
param3 = cmd2.CreateParameter
param3.OleDbType = OleDbType.Date
param3.Value = DateTime.Parse(TextBox2.Text)
param3.ParameterName = "@EndDate"
cmd2.Parameters.Add(param3)
GridView2.DataBind()
Dim param4 As OleDbParameter
param4 = cmd3.CreateParameter
param4.OleDbType = OleDbType.Date
param4.Value = DateTime.Parse(TextBox3.Text)
param4.ParameterName = "@StartDate"
cmd3.Parameters.Add(param4)
Dim param5 As OleDbParameter
param5 = cmd3.CreateParameter
param5.OleDbType = OleDbType.Date
param5.Value = DateTime.Parse(TextBox2.Text)
param5.ParameterName = "@EndDate"
cmd3.Parameters.Add(param5)
GridView3.DataBind()
Dim param6 As OleDbParameter
param6 = cmd4.CreateParameter
param6.OleDbType = OleDbType.Date
param6.Value = DateTime.Parse(TextBox3.Text)
param6.ParameterName = "@StartDate"
cmd4.Parameters.Add(param6)
Dim param7 As OleDbParameter
param7 = cmd4.CreateParameter
param7.OleDbType = OleDbType.Date
param7.Value = DateTime.Parse(TextBox2.Text)
param7.ParameterName = "@EndDate"
cmd4.Parameters.Add(param7)
GridView4.DataBind()
conn.Close()
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
End Sub
Private Sub ClearControls(ByVal control As Control)
For i As Integer = control.Controls.Count - 1 To 0 Step -1
ClearControls(control.Controls(i))
Next
If Not (TypeOf control Is TableCell) Then
If control.[GetType]().GetProperty("SelectedItem") IsNot Nothing Then
Dim literal As New LiteralControl()
control.Parent.Controls.Add(literal)
Try
literal.Text = DirectCast(control.[GetType]().GetProperty("SelectedItem").GetValue(control, Nothing), String)
Catch
End Try
control.Parent.Controls.Remove(control)
ElseIf control.[GetType]().GetProperty("Text") IsNot Nothing Then
Dim literal As New LiteralControl()
control.Parent.Controls.Add(literal)
literal.Text = DirectCast(control.[GetType]().GetProperty("Text").GetValue(control, Nothing), String)
control.Parent.Controls.Remove(control)
End If
End If
Return
End Sub
Protected Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
Dim dt As New DataTable("GridView_Data")
For Each cell As TableCell In GridView5.HeaderRow.Cells
dt.Columns.Add(cell.Text)
Next
For Each row As GridViewRow In GridView5.Rows
dt.Rows.Add()
For i As Integer = 0 To row.Cells.Count - 1
dt.Rows(dt.Rows.Count - 1)(i) = row.Cells(i).Text
Next
Next
Dim wb As New XLWorkbook
wb.Worksheets.Add(dt)
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=GridViewPOLQA.xlsx")
Using MyMemoryStream As New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.[End]()
End Using
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
Return
End Sub
Protected Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button4.Click
Dim dt As New DataTable("GridView_Data")
For Each cell As TableCell In GridView6.HeaderRow.Cells
dt.Columns.Add(cell.Text)
Next
For Each row As GridViewRow In GridView6.Rows
dt.Rows.Add()
For i As Integer = 0 To row.Cells.Count - 1
dt.Rows(dt.Rows.Count - 1)(i) = row.Cells(i).Text
Next
Next
Dim wb As New XLWorkbook
wb.Worksheets.Add(dt)
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=GridViewRTD.xlsx")
Using MyMemoryStream As New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.[End]()
End Using
End Sub
Public Shared Sub ExportToExcel(data As IEnumerable(Of Dynamic), sheetName As String)
Dim wb As New XLWorkbook()
Dim ws = wb.Worksheets.Add(sheetName)
ws.Cell(2, 1).InsertTable(data)
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
HttpContext.Current.Response.AddHeader("content-disposition", [String].Format("attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")))
Using memoryStream As New MemoryStream()
wb.SaveAs(memoryStream)
memoryStream.WriteTo(HttpContext.Current.Response.OutputStream)
memoryStream.Close()
End Using
HttpContext.Current.Response.[End]()
End Sub
This is error stack trace I am getting as mentioned below.
[OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.]
System.Convert.ToBase64String(Byte[] inArray, Int32 offset, Int32 length, Base64FormattingOptions options) +119
System.Web.UI.ObjectStateFormatter.Serialize(Object stateGraph, Purpose purpose) +229
System.Web.UI.ObjectStateFormatter.System.Web.UI.IStateFormatter2.Serialize(Object state, Purpose purpose) +13
System.Web.UI.Util.SerializeWithAssert(IStateFormatter2 formatter, Object stateGraph, Purpose purpose) +40
System.Web.UI.HiddenFieldPageStatePersister.Save() +106
System.Web.UI.Page.SavePageStateToPersistenceMedium(Object state) +108
System.Web.UI.Page.SaveAllState() +653
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5314
The web.config file is as follows:-
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\VQT_GL Testing.mdb""
providerName="System.Data.OleDb" />
<add name="ConnectionString2" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\VQT_GL Testing.mdb""
providerName="System.Data.OleDb" />
<add name="ConnectionString3" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\VQT_GL Testing.mdb""
providerName="System.Data.OleDb" />
<add name="ConnectionString4" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\VQT_GL Testing.mdb""
providerName="System.Data.OleDb" />
<add name="ConnectionString5" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\VQT_GL Testing.mdb""
providerName="System.Data.OleDb" />
<add name="ConnectionString6" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\VQT_GL Testing.mdb""
providerName="System.Data.OleDb" />
<add name="VQTConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Program Files (x86)\GL Communications Inc\DataImport\VQT.mdb""
providerName="System.Data.OleDb" />
</connectionStrings>
<system.web>
<compilation debug="false" strict="false" explicit="true" targetFramework="4.5.2" />
<customErrors mode="Off"/>
<authentication mode="None"/>
<httpRuntime targetFramework="4.5.2" maxRequestLength="1048576" executionTimeout="3600" />
<machineKey
validationKey="62AFB48AEFD48352027301E7D1E6180E95AFF287377B03FA4492404998D3C1ADD2EBB9C8D18DD4A87E53759167ADC385AE29CC3B0F12965B4D68A4964D0F8062"
decryptionKey="A4E89B2AA3F120979F154A5EFCF4F4254A1925B59285A2A9"
validation="SHA1" decryption="AES"
/>
</system.web>
<system.webServer>
<security>
<requestFiltering>
<!--The default size is 30000000 bytes (28.6 MB). MaxValue is 4294967295 bytes (4 GB)-->
<!-- 100 MB in bytes -->
<requestLimits maxAllowedContentLength="104857600" />
</requestFiltering>
</security>
<modules runAllManagedModulesForAllRequests="true"/>
</system.webServer>
<appSettings>
<add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
</appSettings>
</configuration>
