Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of SNichols1124
SNichols1124 asked on
Microsoft SQL ServerVisual Basic.NETMicrosoft 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
Avatar of SNichols1124
SNichols1124

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

Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answers