Sub CreateTableIfNotExists
'Find all the tables in this database
Command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name='t_orders'"
'instantiate reader
Reader.New1
'Fill reader
Reader.Value = Command.ExecuteReader
If Reader.ReadNextRow = True Then
' No table with this name in the database.
Sub addRecord
' row is a global array containing the row data from the editing screen
' col is a global array containing the column names
' p1, p2 etc are the command parameters, the number part matching the index of the relevant column in row()
' tableName is a global variable
Connection.BeginTransaction
For i = 1 To ArrayLen(row()) - 1
Command.AddParameter("p" & i)
Command.SetParameter("p" & i, row(i))
Next
Command.CommandText = "INSERT INTO " & tableName & " VALUES (null, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)"
' nb although a null is seemingly put in the ID column a unique value will be supplied by sqlite
Command.ExecuteNonQuery
Connection.EndTransaction
End Sub
Sub updateRecord
' row is a global array containing the row data from the editing screen
' col is a global array containing the column names
' tableName is a global variable
Connection.BeginTransaction
For i = 0 To ArrayLen(row()) - 1
Command.AddParameter("p" & i)
Command.SetParameter("p" & i, row(i))
Next
' p is shorthand for string "=@p" - saves confusion about quote marks
p = "=@p"
' q is a temporary holder for Command.CommandText - (q for query string)
q = "UPDATE " & tableName & " SET "
For i = 1 To ArrayLen(row()) - 2
q = q & col(i) & p & i & ", "
Next
q = q & col(i) & p & i & " WHERE " & col(0) & "=@p0"
Command.CommandText = q
Command.ExecuteNonQuery
Connection.EndTransaction
End Sub
Sub deleteRecord
Connection.BeginTransaction
' assumes the ID will always be the first column - name stored in col(0), value in row(0)
' row is a global array containing the row data from the editing screen
' col is a global array containing the column names
' tableName is a global variable
Command.AddParameter("p0")
Command.SetParameter("p0" , row(0))
Command.CommandText = "DELETE FROM " & tableName & " WHERE " & col(0) & "=@p0"
Command.ExecuteNonQuery
Connection.EndTransaction
End Sub
...INSERT INTO " & tableName & " VALUES (null, @p1, etc etc...
Sub addRecord
' recList is an ArrayList containing the row data from the editing screen
' fieldList is an ArrayList containing the field names
' tableName is a global variable holding the table name
' fields is the total number of fields in the table - you could use
' fieldList.count if preferred, and forget this global variable
' command @ parameters are named p0, p1 etc in series
' variable q is a temporary holder for Command.CommandText - (q for query string)
Connection.BeginTransaction
For i = 0 To fields - 1
Command.AddParameter("p" & i)
Command.SetParameter("p" & i, recList.Item(i))
Next
q = "INSERT INTO " & tableName & " VALUES (null"
For i = 0 To fields -1
q = q & ", @p" & i
Next
q = q & ")"
Command.CommandText = q
Command.ExecuteNonQuery
Connection.EndTransaction
End Sub
Sub GetFields
Command.CommandText = "PRAGMA table_info ('" & tableName & "')"
Reader.Value = command.ExecuteReader
fieldList.Clear
Do While Reader.ReadNextRow = True
fieldList.Add(Reader.GetValue(1))
Loop
Reader.Close
fields = fieldList.Count
End Sub
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?
We use cookies and similar technologies for the following purposes:
Do you accept cookies and these technologies?