I have spent about 2 hours going round in circles and would really appreciate some help. I have a method on an OPEN Event when App loads to see if a Database Exists, and if it doesn’t : Create it, else load the Data that is in the Database.
No Matter what I Do I Get a NILL Exception when the Check Runs when there is a Database:
Dim DatabaseExistance as FolderItem = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
if DatabaseExistance <> nil and not DatabaseExistance.exists then
Dim db As New REALSQLDatabase
db.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
Dim DatabaseStructure as String
Dim DatabaseDefaultData as String
DatabaseStructure = "CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)"
DatabaseDefaultData = "INSERT INTO LockdownStore (Mins, Password) VALUES ('15:00','')"
If db.CreateDatabaseFile Then
db.SQLExecute (DatabaseStructure)
db.SQLExecute (DatabaseDefaultData)
db.Commit
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If
else
Dim sql As String
sql = "SELECT Mins,Password from LockdownStore"
Dim data As RecordSet
data = LockdownDB.SQLSelect(sql)
If LockdownDB.Error Then
MsgBox("DB Error: " + LockdownDB.ErrorMessage)
Return
End If
If data <> Nil Then
While Not data.EOF
InptBox_Minutes.Text = data.Field("Mins").StringValue
InptBox_Password.Text = data.Field("Password").StringValue
data.MoveNext
Wend
data.Close
End If
end if
Failing HERE:
data = LockdownDB.SQLSelect(sql)
I have added a path to the Database in the Navigation via : Select a Database, and can get to it. There is one row of data : Mins : 15:00 and Password is Blank.
Dim DatabaseExistance as FolderItem = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
if DatabaseExistance <> nil and not DatabaseExistance.exists then
Dim db As New REALSQLDatabase
db.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
Dim DatabaseStructure as String
Dim DatabaseDefaultData as String
DatabaseStructure = "CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)"
DatabaseDefaultData = "INSERT INTO LockdownStore (Mins, Password) VALUES ('15:00','')"
If db.CreateDatabaseFile Then
db.SQLExecute (DatabaseStructure)
db.SQLExecute (DatabaseDefaultData)
db.Commit
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If
InptBox_Minutes.Text = "JUST CREATED"
else
Dim LockdownDB As New REALSQLDatabase
LockdownDB.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
Dim sql As String
sql = "SELECT Mins,Password from LockdownStore"
Dim data As RecordSet
data = LockdownDB.SQLSelect(sql)
If LockdownDB.Error Then
MsgBox("DB Error: " + LockdownDB.ErrorMessage)
Return
End If
If data <> Nil Then
While Not data.EOF
InptBox_Minutes.Text = data.Field("Mins").StringValue
InptBox_Password.Text = data.Field("Password").StringValue
data.MoveNext
Wend
data.Close
End If
end if
I am told the DB ERROR : Operation cannot be completed because the database is closed
If db.CreateDatabaseFile Then
db.SQLExecute (DatabaseStructure)
db.SQLExecute (DatabaseDefaultData)
db.Commit
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If[/code]
It is best practice to check for an error after each statement. In the case above, you are creating the table (which could generate an error) then inserting a record into it (also could result in an error), but your not checking for errors. Each statement should check for an error after execution. If you were error checking, I think both of these would have thrown an error because you are not connected to the database. Yes, you created it, but in order to create table and insert you need to be connected.
Also, review the docs, but I do not think COMMIT does anything unless you first call BEGIN TRANSACTION with the intent on doing a ROLLBACK if there is an error.
Here is working code (just incase there is anyone NEW to this like me Googling away)
Dim DatabaseLocation as FolderItem = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
if DatabaseLocation <> nil and not DatabaseLocation.exists then
Dim db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
Dim DatabaseStructure as String
Dim DatabaseDefaultData as String
DatabaseStructure = "CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)"
DatabaseDefaultData = "INSERT INTO LockdownStore (Mins, Password) VALUES ('30:00','')"
If db.CreateDatabaseFile Then
db.SQLExecute (DatabaseStructure)
db.SQLExecute (DatabaseDefaultData)
db.Commit
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If
InptBox_Minutes.Text = "30:00"
else
Dim LockdownDB As New SQLiteDatabase
LockdownDB.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
Dim sql As String
sql = "SELECT Mins,Password from LockdownStore"
If LockdownDB.Connect Then
Dim data As RecordSet
data = LockdownDB.SQLSelect(sql)
If data <> Nil Then
While Not data.EOF
InptBox_Minutes.Text = data.Field("Mins").StringValue
InptBox_Password.Text = data.Field("Password").StringValue
data.MoveNext
Wend
data.Close
End If
Else
MsgBox("Error: " + LockdownDB.ErrorMessage)
End If
End If