Click here to Skip to main content
15,889,931 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,

I'm getting an Outofmemory Exception while loading a table into an mdb file using oledb connection. the table is having more than 6 lakh records and the file size will not 150 mb. we have that space in our server. still it is giving us exception.

Can any one tell me the solution for this? Please find the code below.

VB
Dim SqlDs As New DataSet
        Dim Conn As New SqlConnection(SQLNativeConnStr)
        Conn.Open()
        Dim SQLStr As String = "SELECT * FROM " & TableName & ""
        Dim SqlAdp As SqlDataAdapter = New SqlDataAdapter(SQLStr, Conn)
        SqlAdp.Fill(SqlDs)
        Conn.Close()
        Dim dt As DataTable = SqlDs.Tables(0)

        'Dim OleDbCon As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileLoadPath & ";")
        'OleDbCon.Open()
        'Dim OleDbCmd As OleDbCommand = New OleDbCommand(MdbTableScript, OleDbCon)
        'OleDbCmd.ExecuteNonQuery()
        'OleDbCon.Close()

        Dim SqlQuery As String = "SELECT * FROM [" & TableName & "]"
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileLoadPath & ";"
        Dim Adapter As New OleDbDataAdapter(SqlQuery, ConnString)
        Dim CmdBuilder As New OleDbCommandBuilder(Adapter)
        CmdBuilder.QuotePrefix = "["
        CmdBuilder.QuoteSuffix = "]"
        Dim ds As New DataSet
        Adapter.FillSchema(ds, SchemaType.Source)
        ds.Tables(0).TableName = "Temp"

        For i As Integer = 0 To dt.Rows.Count - 1
            Dim RowValues(ds.Tables(0).Columns.Count - 1) As Object
            For j As Integer = 0 To ds.Tables(0).Columns.Count - 1
                If dt.Rows(i)(j).ToString().ToUpper() = "" Then
                    If ds.Tables(0).Columns(j).DataType.ToString() = "System.String" Then
                        RowValues(j) = ""
                    Else
                        RowValues(j) = 0
                    End If
                Else
                    RowValues(j) = dt.Rows(i)(j).ToString()
                End If
            Next
            ds.Tables(0).Rows.Add(RowValues)
        Next
        Adapter.Update(ds, "Temp")
        Adapter.Dispose()


Thanks in advance

Regards,
Srinivas.
Posted
Updated 6-Jun-12 5:30am
v2
Comments
Dave Kreskowiak 7-Jun-12 11:41am    
You can find a small example of using a OleDbCommand object to INSERT a row at http://stackoverflow.com/questions/337333/how-would-i-insert-data-into-an-access-table-using-vb-net.

After that, start going through http://www.google.com/#hl=en&sclient=psy-ab&q=vb.net+insert+rows+into+database+tutorial&oq=vb.net+insert+rows+into+database+tutorial&aq=f&aqi=&aql=1&gs_l=hp.3...105611.107124.1.107390.9.9.0.0.0.0.312.1482.0j3j3j1.7.0.eqn%2Ccconf%3D0-95%2Cmin_length%3D2%2Crate_low%3D0-015%2Crate_high%3D0-015%2Csecond_pass%3Dfalse.1.0.0.irQACV5qqNE&pbx=1&bav=on.2,or.r_gc.r_pw.r_qf.,cf.osb&fp=642c61ec5c05d173&biw=1680&bih=848

1 solution

You're using a DataAdapter to fill a DataTable from the SQL database, and you expect the system to hold over 6,000,000 records IN MEMORY?? How big is a single record?? Do the math...

A more appropriate way to do this would be to create a SSIS (SQL Server Integration Services) job to export the data for you.

Or, you could recode your app to use DataReaders instead of DataTables. The DataReader will read one record at a time from the database and allow you to create the record in the new database, as you read them. There is no limit to the number of records you can do this with, except for disk space.
 
Share this answer
 
Comments
Jαved 6-Jun-12 15:30pm    
Good 5 from me.
thatraja 6-Jun-12 15:40pm    
5!
Dave Kreskowiak 7-Jun-12 11:04am    
If you need to add rows, to the table in the Access database, when you're done copying the data over, call another method that adds the rows directly to the database. Don't use a DataTable!
VJ Reddy 7-Jun-12 11:52am    
Good answer. 5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900