[RESOLVED] MC3000 Memory/Sql Server Ce Problems

I've developed a simple stock tacking application in VS 2005 using SQL Server CE for a MC3000 device running CE 5.0. See here http://www.bar-codenow.com/symcbamoco.html

The application works fine but after scanning around 20-30 products the app slows down and eventually you start to see database errors. (With each scan it looks up a product record and records the count to a transaction table.)

The device I'm using has 32mb of ram and the database sits on a 1gb storage card. I've tried the 'SSCE:Temp File Directory' setting but it doesn't make a difference. The database itself is only a couple of mb.

I have noticed if I exit the application and re-enter the problem goes away for another 20 or so scans.

Does anybody have any idea's how I can free up some more room or what the problem may be?

Thanks

It sure sounds like you are tanking up data in objects in your program and not releasing them - is that possible??

Can you show some SQL code that you are using to talk to the database??

The form load opens the connection

Code:

---

DbConn.ConnectionString = "data source=" & default_path & "mc3000.sdf;SSCE:Temp File Directory = " & default_path
DbConn.Open()

---

I then pass the connection through into functions in a database class (the RunSQL is used to run the INSERT for new records)

Code:

---

Public Sub RunSQL(ByVal DbConn As SqlCeConnection, ByVal sql As String, ByVal showerror As Boolean)

        Try
            Dim objCmd As SqlCeCommand = New SqlCeCommand(sql, DbConn)
            objCmd.CommandType = Data.CommandType.Text
            objCmd.ExecuteNonQuery()
            objCmd.Dispose()
        Catch ex As Exception
            If showerror = True Then
                MsgBox("RunSQL:" & ex.Message)
            End If
        End Try

    End Sub

Public Function GetStock(ByVal DbConn As SqlCeConnection, ByVal company As Integer, ByVal depot As String, ByVal part As String) As StockStruct
        Dim stock As New StockStruct
        GetStock = stock

        Try
            Dim cmd As New SqlCeCommand("select * from stock where company=" & company & " and depot='" & depot & "' and part='" & part & "'", DbConn)
            cmd.ExecuteReader()

            Dim reader As SqlCeDataReader
            reader = cmd.ExecuteReader

            If reader.Read Then
                GetStock.stockid = reader("stockid")
                GetStock.company = reader("company")
                If IsDBNull(reader("depot")) = False Then
                    GetStock.depot = reader("depot").ToString.Trim
                End If
                If IsDBNull(reader("part")) = False Then
                    GetStock.part = reader("part").ToString.Trim
                End If
                If IsDBNull(reader("desc1")) = False Then
                    GetStock.desc1 = reader("desc1").ToString.Trim
                End If
                If IsDBNull(reader("stock_type")) = False Then
                    GetStock.stock_type = reader("stock_type").ToString.Trim
                End If
                GetStock.uoi = reader("uoi")
                GetStock.wines_flag = reader("wines_flag")
                GetStock.dp = reader("dp")
            End If

            reader.Close()
            cmd.Dispose()
        Catch ex As Exception
            MsgBox("GetStock:" & ex.Message)
        End Try
    End Function

---

Finally I close the connection when the form closes

Code:

---

If DbConn.State = ConnectionState.Open Then
            DbConn.Close()
        End If

---

On a similar line to your comment, I supose the problem could be something to do with SYMBOL.DLL/SYMBOL.BARCODE.DLL I use to read the scanned barcode not releasing memory?

With a quick look that seems fine...

I do think this is redundant - right?

Code:

---

Dim cmd As New SqlCeCommand("select * from stock where company=" & company & " and depot='" & depot & "' and part='" & part & "'", DbConn)
cmd.ExecuteReader()  <-- No need for this EXECUTEREADER
                    ...- as the one below actually is the one you want to use - right?

Dim reader As SqlCeDataReader
reader = cmd.ExecuteReader

---

Missed that one! Yes it is redundant, it shouldn't really cause a problem other than it issues two reads.

I've not got the device with me so I can't test the change. Thinking about it, I may run a test with keying the barcodes rather than scanning to try and rule out the Symbol scanner class.

That's a really good idea - eliminate parts of code from processing...

Like skip the database insert's or update's as well.

We've encountered no issues with our users marking 4 or so entries per student in classes of 25 to 100 students - several classes per day.

You might want to also find out how to measure the amount of memory in use (within the app itself) and write this figure to a .txt file from the app. Determine the figure and write it to the app in lots of places - so you can see where the rogue issues are.

Good luck...

btw - you are scaring me about the database errors - what kind of errors were you seeing?

Thanks for the advice I will give that a try.

Before the database was put onto the memory card I had real problems creating the database and tables. I had to keep adjusting the memory slider until I could get it to work. The errors in the app are normally that it can't open tables, presumably becuase of the memory issue. As you suggested I will monitor the memory more carefully to see whats causing it to jump up.

Thanks

Cracked it!!!!

I tested the app without using the integrated scanner and it worked without any problems so obviously it was down the symbol.barcode.dll library I was using.

After many hours changing the code I couldn't solve the problem and each time after 10-12 scans the program ran slower and slower, yet if I excited the form and returned it worked OK for another 10-12 scans.

I displayed to screen the following code to show me the memory

Code:

---

GC.GetTotalMemory(True)

---

This solved the problem and I tested 50 scans without any issues. For the production version I have replaced the code with a GC.Collect.

Thanks for your help.