Hi all, I am saving the contents of a listbox to a local database. I put in an error trapping snippet to check that the save was going trouble free, but when the program gets to the record save section of the method, it raises en error with the description ‘DB Error: near “)”: syntax error’. I have no clue what Xojo is trying to tell me here. This forum does not have an error of that kind for me to relate to. Any ideas people???
This sounds like a SQL syntax error, so check the SQL commands you execute.
thanks for the input Christian, but this is a simple record save command db.InsertRecord(table,rec) where ‘table’ has been dimensioned as a string and loaded with a valid table name in the database. ‘rec’ has been dimensioned as a database record. There is no SQL executed before this error happened.
Maybe you have an un-escaped quote in a (string) field value?
Could be bug in xojo. Escape should happen automatically.
Here is the line of code that is causing the error. I have commented out the section that reads a cell from a list box and replaced it with the string ‘data’. I still get the error. This is the offending line; rec.Column(rec.FieldName(x))=“data”
Try with rec.FieldName(x) replaced with the field name itself. Maybe rec.FieldName(x) doesn’t return a valid field name.
Cant do that. I am writing a generic save method for a set of list boxes in my app, by specifically naming field names, I am removing the abstraction ability of the method. The method is creatively called ‘SaveLB’ and the passed parameters are; lb as listbox,columns as integer,table as string. Old fashioned, but it worked reliably on VB
“Try” means for debugging purposes.
My apologies for asking for advice then not taking it.Naming the field instead of using the column index works without any problem as you suggested. Does this suggest I am doing something wrong. I have tested the X value previously and it refers to a valid column in the listbox I am saving data from.
Well, either your function does return a wrong name for this column, or the name is a reserved work in SQLite. I think you can wrap the column name in  and it will work then.
I’ve had this error in the past too.
In my case I tried to save an alphanumeric field as a numeric field (without the quotes).
Eli;  are not legal operators in Xojo so this kills that idea.
Paul; thanks for the suggestion, but I do have all of the table fields defined as text and I am predominantly saving text, so I dont think this is the problem, but just to be sure I will try your idea out.
This has nothing to do with Xojo - the SQL column name needs to be wrapped in  when a SQLite keyword is used as column name. Like that:
rec.Column("[" + rec.FieldName(x) + “]”)=“data”
SQlite will always store your values, even when assigning a string to a column defined as integer. SQlite is typeless.
And i think using keywords for field names is a bad idea.
I am now getting ‘DB Error: table vendors has no column named’ when using the following code rec.Column("[" + rec.FieldName(x) + “]”)=lb.Cell(ctr,x) where ‘ctr’ is a row counter and x is a column counter. I have to agree with Christian.
What is the column name of the one that is not working?
‘0’ is not a valid SQLite column name.