title: System out of memory exception when loading an excel spreadsheet with 100000+ rows with 25 columns or more to a gridview
body: <p>i have to load a gridview with a huge amount of data 100k rows and about 27 columns. the datatable gives me a system out of memory exception error everytime i load it and then try to bind the gridview which is posted below. </p>
  
  <p>What I have tried:</p>
  
  <p>i have done research for about 2 weeks now and nothing has helped. can someone please give me a proper answer as to what i need to do? 
  The below is what i do to load the spreadsheet directly into a dataset which is giving me the system out of memory exception. But now i would like to use an access database. </p>
  
  <pre><code>Public Function loadExcel()
          If GlobalVariable.outputSelectBackButtonFlag = True Then
              BindData()
          Else
              ''loadExcel Function to show the file to the gridView.
              Dim MyConnection As System.Data.OleDb.OleDbConnection
              Dim DtSet As New System.Data.DataSet("TaskList")
              Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
              Dim taskTable As New DataTable("TaskList")
  
              Dim filePath = Path.Combine(GlobalVariable.savedPath, GlobalVariable.excelFileName)
  'Code to Use an Oledb Connection to get data from the Excel File
              If File.Exists(filePath) Then
                  MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; filePath &amp; ";Extended Properties='Excel 12.0 Xml;HDR=YES;';")
                  MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
                  DtSet = New System.Data.DataSet
                  MyCommand.Fill(DtSet)
                  GridView1.DataSource = DtSet.Tables(0)
                  Session("TaskTable") = DtSet
                  BindData()
                  MyConnection.Close()
              Else
                  Response.Redirect("UploadFile.aspx", True)
              End If
              Return Nothing
  End Function
  </code></pre>