SQL Server Comact Error 0XC0000005 After Device Is Powered Off And Back On

This is my first attempt to use SQL Server Compact with Visual Studio 2005.

I wrote a program for Inventory Scaning which requires the database to be stored on an SD Card. As long as the device is left powered on, the programs function normally. However, if the device powers off and is then turned back on the 0xC0000005 error is displayed when the program attempts to do anything with the SQL Connection.

I have also written a small sample program to test and the results are repeatable each time.

The sample program consists of a single form with six buttons (Create Database, Open Database, Insert Records, Delete Records, Close Database, Dispose Database).

Both programs are written in VB.

Here is the code for the sample program.


Public Class frmSQLTest


Private cnDB As System.Data.SqlServerCe.SqlCeConnection

Private connString As String


Private Sub frmSQLTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.txtDB.Text = "Storage CardTest.sdf"

End Sub


Private Sub txtDB_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDB.LostFocus

connString = "Data Source=" & Me.txtDB.Text

Me.txtStatus.Text = "Connection String Set"

End Sub


Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

If Me.txtDB.Text = Nothing Then

MessageBox.Show("Unable to Create Database")

Exit Sub

End If

Dim eng As New System.Data.SqlServerCe.SqlCeEngine(connString)

eng.CreateDatabase()

eng = Nothing

Me.txtStatus.Text = "Database Created"

End Sub


Private Sub btnOpenDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenDB.Click

Dim sqlDB As New System.Data.SqlServerCe.SqlCeCommand

If cnDB Is Nothing Then

cnDB = New System.Data.SqlServerCe.SqlCeConnection(connString)

End If

cnDB.Open()

Me.txtStatus.Text = "Database Opened"

sqlDB.Connection = cnDB

sqlDB.CommandText = "CREATE TABLE Master (Item nvarchar(12), Description nvarchar(19))"

sqlDB.ExecuteNonQuery()

sqlDB.CommandText = "CREATE INDEX idx_Master on Master (Item)"

sqlDB.ExecuteNonQuery()

Me.txtStatus.Text = "Master Table Created"

sqlDB = Nothing

End Sub


Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click

Dim RecCount As Long

Dim sqlDB As New System.Data.SqlServerCe.SqlCeCommand

sqlDB.Connection = cnDB

For RecCount = 1 To Me.nudRecs.Value

sqlDB.CommandText = "INSERT INTO Master ([Item], [Description]) " _

& "VALUES ('" & RecCount.ToString & "','Test Item " & RecCount.ToString & "')"

sqlDB.ExecuteNonQuery()

If RecCount Mod 100 = 0 Then

Me.txtStatus.Text = "Updating Record..." & RecCount.ToString

End If

Next

Me.txtStatus.Text = "Records Inserted"

sqlDB = Nothing

End Sub


Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Dim sqlDB As New System.Data.SqlServerCe.SqlCeCommand

sqlDB.Connection = cnDB

sqlDB.CommandText = "Delete FROM Master"

Me.txtStatus.Text = "Master Table Cleared"

sqlDB = Nothing

End Sub


Private Sub btnCloseDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseDB.Click

cnDB.Close()

Me.txtStatus.Text = "Database Closed"

End Sub


Private Sub btnDispose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDispose.Click

cnDB = Nothing

System.IO.File.Delete(Me.txtDB.Text)

Me.txtStatus.Text = "Database Diposed"

End Sub


End Class

In my tests, I start the program (loaded on the Storage Card in the same directory as the database). SQL Server Compact is also installed on the SD Card to save space in Main Memory.

Steps to Re-Create the Error:

1. Click the Create Database button. Database is created properly.
2. Click the Open Database button. Database connection opens properly.
3. Click the Insert Records button. Records are inserted into the table properly.
4. Turn off the Symbol MC3000 unit.
5. Turn the unit back on.
6. When I click on any of the following buttons, the error is displayed. Close Database, Insert Records, Delete Records.

I have also tried closing the database (as well as closing and disposing the database) prior to step 4 above, but when I try to re-open the database (or create the database) again, the error displays.

Is there anyway to correct this issue?

With the real program, the database must be kept open as long as each form is open to prevent speed issues and the database must be stored on the SD Card since there is not enough storage space available on the device.