Exception near "="; Exception Error Number 1

Databases
1

Here’s the relevant code:


// Build the Insert String
sql_Insert = "INSERT INTO Magazine (Mag_Names=?, Mag_Title=?, Mag_Price=?, Mag_Date=?, Mag_Pages=?, Mag_Scanner=?, Mag_Comments=?)"

// Store a Record
db.ExecuteSQL(sql_Insert, LB.Cell(Insert_Idx,0), LB.Cell(Insert_Idx,1), LB.Cell(Insert_Idx,2), LB.Cell(Insert_Idx,3), LB.Cell(Insert_Idx,4), LB.Cell(Insert_Idx,5), LB.Cell(Insert_Idx,6))

Some columns are empty (but the DB have nothing with NOT NULL).
The number of columns is > than 7 (10 I think, but columns 7 to 10 are not used).

NB: the line db.ExecuteSQL(sql_Insert) works fine, but store nothing, of course.

image
image611×504 52.4 KB

PS: I shut down the computer earlier this morning, took a breakfast, then powered it on, fired Xojo anr that project: same result.
Shut down, gone to Post Office / then local McDonald, then…
Started up the m1, fired Xojo, load the project, run it, load data, save as .sqlite and same error.

Sometimes, shuting down the computer is enough to change the results (but usually to “work fine” to “get a crash”…).

Ideas ?

PPS: traditional INSERT worked fine.

2

You are using a wrong syntax. Please try “Insert into table (field1, field2) Values (?,?)”

3

Thank you Thomas, but same result.

I will look carefully what I wrote in TextEdit (at a larger text size) in case I 've made a mistake…

I wrote:


sql_Insert = "INSERT INTO Magazine(Mag_Names=?, Mag_Title=?, Mag_Price=?, Mag_Date=?, Mag_Pages=?, Mag_Scanner=?, Mag_Comments=?) VALUES (LB.Cell(Insert_Idx,0), LB.Cell(Insert_Idx,1), LB.Cell(Insert_Idx,2), LB.Cell(Insert_Idx,3), LB.Cell(Insert_Idx,4), LB.Cell(Insert_Idx,5), LB.Cell(Insert_Idx,6))"

db.ExecuteSQL(sql_Insert)
4

You are using the UPDATE syntax for INSERT - please try it without “=?”

// Store a Record
db.ExecuteSQL("INSERT INTO Magazine (Mag_Names, Mag_Title, Mag_Price, Mag_Date, Mag_Pages, Mag_Scanner, Mag_Comments) VALUES (?, ?, ?, ?, ?, ?, ?)", LB.Cell(Insert_Idx,0), LB.Cell(Insert_Idx,1), LB.Cell(Insert_Idx,2), LB.Cell(Insert_Idx,3), LB.Cell(Insert_Idx,4), LB.Cell(Insert_Idx,5), LB.Cell(Insert_Idx,6))

If you are using SQLite, this will work…

5

You are correct !

I came from:


sql_Insert = "INSERT INTO Magazine (Mag_Name, Mag_Title, Mag_Price, " +_
"Mag_Date, Mag_Pages, Mag_Scanner, Mag_Comments) VALUES " +_
"('"  +LB.Cell(Insert_Idx,0) +"','"+LB.Cell(Insert_Idx,1) +"','"+LB.Cell(Insert_Idx,2) +_
"','" +LB.Cell(Insert_Idx,3) +"','"+LB.Cell(Insert_Idx,4) +"','"+LB.Cell(Insert_Idx,5) +_
"','" +LB.Cell(Insert_Idx,6) +"')"

But, I have a ton of standard “'” in my source and SQLite does not like it. I removed some, and at last, I gave up and use the automatic PreparedStatement.
But due to the documentation, I do had to modify the UPDATE example.

6

At data acquiring time, I added a ReplaceAll:


Issue_Names.Append Issue_FI.Name.ReplaceAll("'", "’")

I think I replaced all occurences on the source hard disk, but… I have enough to start again until the project found another I do not have changed because I do not saw it (bad eyes).

7

That should just become:

sql_Insert = "INSERT INTO Magazine (Mag_Name, Mag_Title, Mag_Price, " +_
"Mag_Date, Mag_Pages, Mag_Scanner, Mag_Comments) VALUES " +_
"(?,?,?,?,?,?,?)"

One of the advantages is that it deals with the quotes for you.

8

Yes, thank you, but where I place the data to be saved in the Record / File ?
Data from the ListBox…

9 
if Insert_idx <> -1 then
   sql_Insert = "INSERT INTO Magazine (Mag_Name, Mag_Title, Mag_Price, " +_
      "Mag_Date, Mag_Pages, Mag_Scanner, Mag_Comments) VALUES " +_
      "(?,?,?,?,?,?,?)"
   db.ExecuteSQL(sql_Insert, LB.Cell(Insert_Idx,0), LB.Cell(Insert_Idx,1), LB.Cell(Insert_Idx,2), LB.Cell(Insert_Idx,3), LB.Cell(Insert_Idx,4), LB.Cell(Insert_Idx,5), LB.Cell(Insert_Idx,6))
end if
10

Thank you. I will try later (must go now).

Report tomorrow.

11

Hi Ian,

you sent the correct answer. Thank you.