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

Open in new window

LVL 3
sasllcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

13598Commented:
Try setting the command timeout. It may just be timing out.
ccmd.commandtimeout = 95 or something like that
0
sasllcAuthor Commented:
When I added the timeout command, it put a grey messagebox on the screen just as the program started up, saying "CommandTimeout", and when I clicked on OK, it exited the program...so I don't understand what's with that.

But I now think that the error messages about memory were on to something.  The last few times I tried to run the app, it just locked up rather than giving me an error.  But one time I did get a CE error saying "Program Memory Low".  I warm booted the scanner and ran it again, and when it got to the point where it locked up--similar to where it got in the past when it showed an error--I went into control panel> system and found that the 'Program Memory" had all been used.  There was none available---and I don't understand why.

In contrast, if I run it the old way (using the sSQL function shown in my code), the program memory never gets anywhere near running out.  Just now I ran it the old way, and the record count got far beyond the point where it had died in the past, yet the program memory is only using 21K out of the usual 37K. I can't say I've looked in this area very often while programs were running, but I'm guessing this is normal.  As I watch the program memory usage, it varies from about 21K up to 22K and then back down to 21K, but it stays in that range.

What is it in my new code that could possibly keep eating up memory as it builds the tables---especially program memory?
0
13598Commented:
I would need to look at your main sub where you call these functions to follow the logic and see what's eating up the memory.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

13598Commented:
If you are calling RunSQLso per record you may want to try and dispose of the command object since you are creating a new one everytime you call it.
Can you use Using so it automatically diposes of it or dispose of it?
Something like:
using  ccmd = (New System.Data.SqlServerCe.SqlCeCommand(sSQL, pcnn))
            ccmd.CommandType = CommandType.Text
            ccmd.ExecuteNonQuery()
end using

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
13598Commented:
The other thing to look for is that you are indeed only opening the connection once. But based on the code you posted I cannot really tell.
0
sasllcAuthor Commented:
Looks like the .dispose fixed it!  I'm continuing to run tests...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.