Corrupt SQL CE database.

[IamMacro]

Hi ,

Im getting totally random Corrupt databases with SQL CE.

Are there any precautions i can take against this ?
Is there a way to tell what's causing the problem?

Im doing quite alot of unfriendly things to the database like deleting an entire table and inserting stuff again, but still everything should work , cause one time its giving me no errors at all , next time i use the query analyzer with a big(select statement) it gives error messages.
mzzzz , could it be that the query analyzer reports a message about it being corrupt while it is not , but still locks the .sdf file ?

Im also trying to use the Built in Compacter and repair utility but it keeps saying im missing the SqlCompact30.dll
(where do i put it , anyway)

thx for you help.

[IamMacro]

I might have solved this myself , just dont delete everything from a table and do inserts again or watch where you open your PDA Sqlconnection always close it at the right place.

[Strider]

the dlls must be in the applications installation directory or else in the windows directory

[IamMacro]

Im still getting random corrupt database messages.
What's the best practice to avoid corrupt databases , are they connection related or , should i be using DataAdapters instead of insertcommand or updatecommands.

Most of my code subs looks like this

Dim sqlConn As SqlCeConnection = New SqlCeConnection("Data source=.\My Documents\PDADATA3.sdf")
sqlConn.Open()

dim command as new insertcommand
'insercommand.parameters.add("")...
'...or updatecommand..
insertcommand.executenonquery

sqlconn.close

Ive got many subs looking like this , should i try setting the SQlconn as a private and open it in formload ?

Im always able to repair and compact the database.
But this will give my users another thing to worry about..

[petevick]

Hi,
personally, I open the database at the beginning, and close at the end, for performance reasons.
After mass inserts/deletes, it is always worth doing a compact

Pete

[IamMacro]

Hi ,

My program doesnt really have a beginning or an end , users can select "previous" or "next" while they go through the forms , or can i use Form.Activate or form.deactivate.

Im using Compact on the database now atleast once after an insert , and i hope my problems are now over , havent gotten a single corrupt database yet.

thx.

[petevick]

Hi,
again, I would open in your 'sub main' or at the start of your first form, and close when you exit the program, log off, compact etc.

I am sure you will get better performance as opening a connection is fairly heavy

Pete

[IamMacro]

Thx ,
I noticed a nice boost on the heavier forms.

[IamMacro]

Dim src As String
src = "\My Documents\PDADATA3.SDF"
Dim pw As String = ""
Dim dest As String = "\My Documents\PDADATA4.SDF"
' Initialize SqlCeEngine Object.
Dim engine As New SqlCeEngine("Data Source = " + src + ";password=" + pw)
Try
engine.Compact(("Data Source = " + dest))
engine.Dispose()
verhoog()
File.Delete(src)
File.Move(dest, src)
'MessageBox.Show("Compact Done...", "SQL CE 2.0 App", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button2)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
'Dispose of the SqlCeEngine object.
engine.Dispose()
End Try

This is working nicely to counter the errors.