troubleshooting Question

System.OutofMemoryException error when inserting records using sql server ce 3.0 + .Net Framework 4.0

Avatar of SNichols1124
SNichols1124 asked on
Visual Basic.NETMicrosoft SQL ServerMicrosoft SQL Server 2005
8 Comments1 Solution1101 ViewsLast Modified:
I have an unusual problem.  I have a bit of code that runs fine in the .NET 2.0 Framework with Compact Framework 2.0, but generates a 'System.OutofMemoryException'  error when upgraded to the .NET Framework 4.0. The code is below:

    Private Shared Sub UpdatePLUTableInSIMSDatabase()
        AppServices.Logger.Write("Update PLU - SDF")

        Using comm As New SqlServerCe.SqlCeCommand()
            'Set connection string
            comm.Connection = DBClass.conn

            'Drop PLU table
            Try
                comm.CommandText = "DROP TABLE PLU"
                comm.ExecuteNonQuery()
            Catch ex As Exception
                AppServices.Logger.Write("Create PLU - sdf" & "," & ex.Message)
            End Try

            'Create PLU table
            Try
                Dim createSDF_PLUTable As String = DBClass.LoadResource("CreateSDF_PLU.sql", DBClass.resourcePath)
                comm.CommandText = createSDF_PLUTable
                comm.ExecuteNonQuery()
            Catch ex As Exception
                AppServices.Logger.Write("Create PLU - sdf" & "," & ex.Message)
            End Try


            'Retrieve all records from PLU and insert them into the SIMSDB
            Using dt As DataTable = AppServices.DataBase.ExecSQL("SELECT * FROM PLU", False)
                'Execute Insert For Each Record.
                Dim insertSDF_PLU As String = DBClass.LoadResource("SDF_PLU.sql", DBClass.resourcePath)
                Dim sbInsert As New System.Text.StringBuilder()
                For Each row As DataRow In dt.Rows
                    sbInsert.Append(insertSDF_PLU)
                    sbInsert.Replace("{0}", IIf(row.Item(0) Is DBNull.Value, _
                        "null", row.Item(0)).ToString)     'PLU
                    sbInsert.Replace("{1}", IIf(row.Item(1) Is DBNull.Value, _
                        "null", row.Item(1)).ToString)     'OLD_PLU
                    sbInsert.Replace("{2}", IIf(row.Item(2) Is DBNull.Value, _
                        "null", row.Item(2)).ToString)     'DEPT
                    sbInsert.Replace("{3}", IIf(row.Item(3) Is DBNull.Value, _
                        "null", row.Item(3)).ToString)     'DESC
                    sbInsert.Replace("{4}", IIf(row.Item(4) Is DBNull.Value, _
                        "null", row.Item(4)).ToString)     'PRICE
                    sbInsert.Replace("{5}", IIf(row.Item(5) Is DBNull.Value, _
                        "null", row.Item(5)).ToString)     'RECALLED_ITEM
                    sbInsert.Replace("{6}", IIf(row.Item(6) Is DBNull.Value, _
                        "null", row.Item(6)).ToString)     'BSL
                    sbInsert.Replace("{7}", IIf(row.Item(7) Is DBNull.Value, _
                        "null", row.Item(7)).ToString)     'CURRENT_ON_HAND
                    sbInsert.Replace("{8}", IIf(row.Item(8) Is DBNull.Value, _
                        "null", row.Item(8)).ToString)     'ITEM_LIFECYCLE
                    sbInsert.Replace("{9}", IIf(row.Item(9) Is DBNull.Value, _
                        "null", row.Item(9)).ToString)     'VELOCITY_CODE
                    sbInsert.Replace("{10}", IIf(row.Item(10) Is DBNull.Value, _
                        "null", row.Item(10)).ToString)  'INVENTORY_TYPE
                    sbInsert.Replace("{11}", IIf(row.Item(11) Is DBNull.Value, _
                        "null", row.Item(11)).ToString)  'VENDOR_NUMBER
                    sbInsert.Replace("{12}", IIf(row.Item(12) Is DBNull.Value, _
                        "null", row.Item(12)).ToString)  'SBT_FLAG
                    sbInsert.Replace("{13}", IIf(row.Item(13) Is DBNull.Value, _
                        "null", row.Item(13)).ToString)  'ORDER_CAP
                    sbInsert.Replace("{14}", IIf(row.Item(17) Is DBNull.Value, _
                        "N", row.Item(17)).ToString)     'Alcohol_Flag
                    sbInsert.Replace("'{15}'", IIf(row.Item(18) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(18).ToString)))     'Prim_SKU

                    sbInsert.Replace("'{16}'", IIf(row.Item(19) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(19).ToString)))     'Expanded Item Description 1
                    sbInsert.Replace("'{17}'", IIf(row.Item(20) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(20).ToString)))     'Expanded Item Description 2
                    sbInsert.Replace("'{18}'", IIf(row.Item(21) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(21).ToString)))     'Expanded Item Description 3
                    sbInsert.Replace("'{19}'", IIf(row.Item(22) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(22).ToString)))     'Expanded Item Description 4
                    sbInsert.Replace("{20}", IIf(row.Item(23) Is DBNull.Value, _
                        "null", row.Item(23).ToString))     'UOM Units
                    sbInsert.Replace("'{21}'", IIf(row.Item(24) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(24).ToString)))     'Unit of Measure
                    sbInsert.Replace("{22}", IIf(row.Item(25) Is DBNull.Value, _
                        "null", row.Item(25).ToString))     'Unit Price
                    sbInsert.Replace("'{23}'", IIf(row.Item(26) Is DBNull.Value, _
                        "null", String.Format("'{0}'", row.Item(26).ToString)))     'Label Price
                    sbInsert.Replace("{24}", IIf(row.Item(27) Is DBNull.Value, _
                        "null", row.Item(27).ToString))     'Stocking UPC
                    sbInsert.Replace("{25}", IIf(row.Item(28) Is DBNull.Value, _
                        "null", row.Item(28).ToString))     'Print Label
                    sbInsert.Replace("{26}", IIf(row.Item(29) Is DBNull.Value, _
                        "null", row.Item(29).ToString))     'Print GTIN


                    Try
                        comm.CommandText = sbInsert.ToString
                        comm.ExecuteNonQuery()
                    Catch ex As Exception
                        AppServices.Logger.Write("PLU_Insert - sdf" & "," & ex.Message & " Detail: " & comm.CommandText)
                    End Try
                    sbInsert.Length = 0
                Next row
            End Using
        End Using
        AppServices.Logger.Write("Update PLU - SDF Completed")
    End Sub
ASKER CERTIFIED SOLUTION
SNichols1124

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros