Hi Bjorn
No, you're not missing anything... Since posting my message I've thought of three even wackier ways of doing this.
For Helen's benefit, once we have the data in some kind of list structure, this is the code:
Code:
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
And this is the code that gets the field names into fieldList:
Code:
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
It's based on something I found here, I can't take the credit. Note the single & double quote characters in the Command string.
The editable table code I mentioned earlier is here:
http://www.basic4ppc.com/forum/code-...e-desktop.html
but I can't get it to work and am hoping someone can sort me out!
Caravelle