Getting up to speed on SQLite usage, I wrote this little Method for saving app preferences. This one Method is completely self contained. If no SQLlite database is found, it will get created. Simple two parameter usage allows for Creating, Reading, Updating and Deleting from a two field SQLite file. 1- Badge is any name you designate for the value youre saving or retrieving. 2- Token is the actual text value being saved.
Usage: CRUD(Badge as string, Token as string), returns String. In addition to saving Textbox values, I recently discovered that it is possible to save and retrieve the entire contents of any Listbox, along with headings!! If you see any way to improve this little Method, let me know.
[code]//CRUD v1.2, Paul Townsend
//Create Module or use existing Module: makes this Method accessible from everywhere
//Insert Method (blue button called Insert) and fill in 3 parameters (next line)
//Method Name “CRUD” , Parameters “badge as string, token as String”, Return Type String
//Paste ALL this code into this Module you just named CRUD
//Usage: CRUD(Badge as string, Token as string), returns String
//Insert or Update: requires both Badge and Token, returns “”
//Retrieve: requires Badge and “” token, returns Token
//Delete: requires badge and “delete” as token, returns “”
//Dump: requires “all” for both badge and token, returns ALL db data in Token
Dim dbName As String = “CRUD.db” //This is the name of the SQLite db file
Dim db As SQLiteDatabase
Dim dbFile As FolderItem = SpecialFolder.Desktop.Child(dbName) //Create db file on Desktop
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
Dim sql, request, reply As String
Dim data As RecordSet
//first we open or create database file as need be ==========================
If dbfile.exists Then
If Not DB.Connect Then //opens database file
Return "CRUD Error: " + DB.ErrorMessage
End
Else // does db does not exist createTable
If Not DB.CreateDatabaseFile Then
Return "CRUD Error: " + DB.ErrorMessage
End
End // now that the db is created & opened or just opened
sql = “CREATE TABLE [CRUD]( [Badge] text PRIMARY KEY NOT NULL UNIQUE, [Token] text);”
DB.SQLExecute(sql)
If DB.Error Then
//do nothing, the table has already been created
End
// then we create SQL statements for each operation =====================================
If badge = “” Then
Return “Error: null badge”
End If
If badge =“all” And token = “all” Then //dump: badge and token both = “all” then
sql = “SELECT * FROM CRUD;”
request = “all”
Elseif badge > “” And token = “delete” Then //delete: both badge and token = “delete”
sql = “DELETE FROM CRUD WHERE badge=” + “’” + badge + “’;”
request = “delete”
Elseif badge > “” And token > “” Then //insert or replace data: got badge and token
sql = “INSERT OR REPLACE INTO [CRUD] ([badge],[token]) "
token = replace(token,”’","’’") //allows for ’ character without confusing SQL
sql=sql + “VALUES (” + “’” + badge + “’, '” + token + “’);”
request = “write”
Elseif badge > “” And token = “” Then //retrieve data: got badge, but token is empty
sql = “SELECT * FROM CRUD WHERE badge=” + “’” + badge + “’;”
request = “read”
Else
Return “Unknown Error” // should never happen
End If
// Finally got all the right SQL assigned ===================================
DB.SQLExecute(sql)
If DB.Error Then
Return "CRUD Error: " + request + " " + DB.ErrorMessage
End
If request = “read” Then
data = DB.SQLSelect(sql)
reply = data.field(“token”)
If reply="" Then Return “Read Error: Badge Not Found”
Return reply
Elseif request = “write” Then
Return “”
Elseif request = “delete” Then
Return “”
Elseif request = “all” Then
data= DB.SQLSelect(sql)
If DB.Error Then
Return "CRUD Select Error: " + DB.ErrorMessage
Else
If data <> Nil Then
While Not data.eof
//reply = data.field(“badge”).StringValue
reply = reply + data.field(“badge”).StringValue + Chr(9) + data.field(“token”).StringValue + EndOfLine
data.MoveNext
Wend
End
Return reply
End
End
Return “nothing here”
[/code]