Errors With SQLite and VB.net when reading database

Question

I seem to be receiving random error messages when trying to read queries from a SQLite DB stored on a network drive. On my development machine, I rarely ever get an error, but users are reporting random errors such as:

Unable to open database. File is encrypted or is not a database
Database disk image is malformed
Or it just doesn't return any data.

My code looks like such:

Private Sub LoadStoreCalls()
    Dim tmpID As String
    Dim QryString As String

Dim SQLconnect As New SQLite.SQLiteConnection()

SQLconnect.ConnectionString = "Data Source=" & SpiceWorksPath & ";New=False;Compress=True;Read Only=True;"

Try
    'Open a connection to the database
    SQLconnect.Open()

    'Get StoreCode
    tmpID = Mid(StoreCode, 2) & "-" & StoreName

    QryString = "SELECT id, summary, status, c_location, c_store_device FROM tickets WHERE status = 'open' AND c_location = '" & tmpID & "'"
    Dim ExQry As New SQLiteCommand(QryString, SQLconnect)
    ExQry.CommandType = CommandType.Text

    Dim da As New SQLiteDataAdapter(ExQry)
    dasSpice.Clear()
    da.Fill(dasSpice, "Calls")

    SQLconnect.Close()

Catch ex As Exception
    If SQLconnect.State = ConnectionState.Open Then SQLconnect.Close()
    MsgBox(ex.Message)

End Try
End Sub

The problem is that my application relies on this data being returned to populate additional entries of a datagridview control, and because I cannot replicate this error on my development machine using debug, I cannot find where the fault is occurring.

If the user gets one of these errors, they usually keep trying to run the query and eventually it will work. Or they just exit my application and go back in and then it seems to work for a while. The errors are random and not always from running the same query.

I'm assuming it's due to an issue talking to an SQLite DB on a shared drive, but I can't find any information regarding setting timeouts. I also can't work out how to 'catch' the error because I can't replicate it myself. I have tried adding logging details to the Catch event handler, but it simply just returns me the error message (above) in the logs.

Any help would be greatly appreciated. Thanks

I have added log entries after every operation that occurs within that sub routine so I can try and identify where the software is falling over. I have managed to find that everytime this problem occurs, my log files stop after Clearing the dataset and then filling it: So after dasSpice.Clear() and before da.Fill(dasSpice, "Calls") — Riples
– Riples, Commented Sep 4, 2013 at 4:24
Try saving the exception type, exception message and stack trace. They might help you. I guess you might have the exception due to timeout. — Lajos Arpad
– Lajos Arpad, Commented Sep 4, 2013 at 4:52

Riples · Accepted Answer · 2014-04-23 12:51:16Z

2

After many hours of troubleshooting and researching I found that SQLite does not play well with remote connections. Not only was it causing errors in my application, it was also throwing errors in the parent application.

My alternative was to write an application to query the database that ran locally on the SQLite machine. This fixed all of my issues.

For anyone interested...

answered Apr 23, 2014 at 12:51

Riples

1,1672 gold badges25 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Errors With SQLite and VB.net when reading database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related