Various runtime errors in SQL in Compact Framework environment

For years I've had a vb (visual studio 2005) program running great on various handheld scanners, using CE 5.0, compact framework 2.0, and SQL server 2005.  I can load very large files with no problem.  I never have really understood all the behind-the-scenes code for writing records, because that part was written for me long ago.

But lately I've needed to speed up the process or writing data records to a table on the scanner.  I thought I remembered that the current code opens and closes the database every time a record is written, so I wanted to try changing things so that the database was opened on the front end, then all the records would be written, and finally the database would be closed.

With the changes I've made, it actually builds the files about ten times faster, and it seems to be writing the records just fine--until I get to a large file, which always bombs out with errors.  I've tried different files, different tables, and different scanners, and I get a variety of runtime errors, such as "TypeLoadException", and "Note enough storage available to complete the operation.  Err# 0", and sometimes just a plain old "Err# 0".  I'm always reading in text files and writing data records to the SQL tables, and in the cases where I'm trying to read in a large file, it only gets through about 25K of text before it dies.  I checked and found that it really did write the records to the table before it errored out.

In the code below, first I'm showing the "RunSQL" function that has worked fine all these years, with any size file.  Note that it has the cn.open and cn.close statements at the beginning and end, presumably opening and closing the database each time it writes a record--greatly slowing things down.

Next I'm showing the new "RunSQLso" function that I'm trying to use to speed things up.  The primary changes I made were elimination of the cn.open and cn.close statements.  Also, variables 'cn' and 'cmd' were defined locally, and I kept getting various syntax and/or runtime errors until I started using 'pcnn' in place of 'cn', and 'ccmd' in place of 'cmd'.  'pcnn' is a public variable defined as SqlCeConnection, and 'ccmd' is a public variable defined as SqlCeCommand.

And, before I ever build any records in the sSQLso function, I call the OpenSQL function (shown below)--intending to get the database open, ready for the sSQLso function to write all its records without any opening or closing.

And, like I say, this all works great for a smaller number of records.  Can anyone see anything I'm missing--or anything I can try?  TIA

***This is the old version***
    Public Function RunSQL(ByVal sSQL As String) As Boolean
        'run sql statements that return no records (Insert, update, delete commands)
        RunSQL = False

        Dim cn As SqlCeConnection = New SqlCeConnection("Data Source=" & psDataSDF)
        Try
            cn.Open()

            Dim cmd As SqlCeCommand = New System.Data.SqlServerCe.SqlCeCommand(sSQL, cn)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()

        Catch ex As System.Data.SqlServerCe.SqlCeException
            If ex.NativeError = 25016 Then Exit Function 'duplicate record
            DisplaySQLCEErrors(ex)
            Exit Function
        Finally
            cn.Close()
        End Try

        RunSQL = True
    End Function

***This is the new version, followed by the open and close functions***

    Public Function RunSQLso(ByVal sSQL As String) As Boolean
        'run sql statements that return no records (Insert, update, delete commands)
        'but this "so" (stay open) version does NOT open and close the file each time, instead
        'opening the file at the start and closing it at the end...
        RunSQLso = False

        'Dim cn As SqlCeConnection = New SqlCeConnection("Data Source=" & psDataSDF)
        Try
            'cn.Open()

            ccmd = (New System.Data.SqlServerCe.SqlCeCommand(sSQL, pcnn))
            ccmd.CommandType = CommandType.Text
            ccmd.ExecuteNonQuery()

        Catch ex As System.Data.SqlServerCe.SqlCeException
            If ex.NativeError = 25016 Then Exit Function 'duplicate record
            DisplaySQLCEErrors(ex)
            Exit Function
        Finally
            'cn.Close()
        End Try

        RunSQLso = True

    End Function

---------------------------------------------------
    Public Function OpenSQL() As Boolean
        'try to open the database
        OpenSQL = False

        pcnn = New SqlCeConnection("Data Source=" & psDataSDF)

        Try
            pcnn.Open()
            OpenSQL = True

        Catch ex As System.Data.SqlServerCe.SqlCeException
            If ex.NativeError = 25016 Then Exit Function 'duplicate record
            DisplaySQLCEErrors(ex)
            Exit Function
        Finally

        End Try

    End Function

---------------------------------------------------

    Public Function CloseSQL() As Boolean
        'try to open the database
        CloseSQL = False

        pcnn = New SqlCeConnection("Data Source=" & psDataSDF)
        Try
            pcnn.Close()
            CloseSQL = True

        Catch ex As System.Data.SqlServerCe.SqlCeException
            If ex.NativeError = 25016 Then Exit Function 'duplicate record
            DisplaySQLCEErrors(ex)
            Exit Function
        Finally

        End Try

    End Function

Select allOpen in new window