# Exception near "="; Exception Error Number 1

**URL:** <https://forum.xojo.com/t/exception-near-exception-error-number-1/76668>\
**Category:** Databases\
**Created:** [July 29, 2023, 8:03am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668 "2023-07-29T08:03:45Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 29, 2023, 8:03am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/1 "2023-07-29T08:03:45Z")

</div>

Here’s the relevant code:

```auto

// 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](https://forum.xojo.com/uploads/default/original/2X/7/707563265fd6d7e4a6ce74fb5c942ff1542b1d24.png)

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.

---

<div class="post-metadata">

**Author:** ![Thomas\_Roemert](https://forum.xojo.com/user_avatar/forum.xojo.com/thomas_roemert/32/827_2.png) [@Thomas\_Roemert](https://forum.xojo.com/u/Thomas_Roemert)\
**Post date:** [July 29, 2023, 9:56am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/2 "2023-07-29T09:56:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 29, 2023, 10:02am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/3 "2023-07-29T10:02:54Z")

</div>

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:

```auto

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)

```

---

<div class="post-metadata">

**Author:** ![Thomas\_Roemert](https://forum.xojo.com/user_avatar/forum.xojo.com/thomas_roemert/32/827_2.png) [@Thomas\_Roemert](https://forum.xojo.com/u/Thomas_Roemert)\
**Post date:** [July 29, 2023, 10:21am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/4 "2023-07-29T10:21:10Z")

</div>

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

```auto
// 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…

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 29, 2023, 10:27am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/5 "2023-07-29T10:27:04Z")

</div>

> [@Thomas\_Roemert](#):
>
> UPDATE syntax for INSERT

You are correct !

I came from:

```auto

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.

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 29, 2023, 10:44am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/6 "2023-07-29T10:44:06Z")

</div>

At data acquiring time, I added a ReplaceAll:

```auto

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).

---

<div class="post-metadata">

**Author:** ![Ian\_Kennedy](https://forum.xojo.com/user_avatar/forum.xojo.com/ian_kennedy/32/6860_2.png) [@Ian\_Kennedy](https://forum.xojo.com/u/Ian_Kennedy)\
**Post date:** [July 29, 2023, 12:31pm UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/7 "2023-07-29T12:31:30Z")

</div>

> [@Emile\_Schwarz](#):
>
> ```auto
> 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) +"')"
> 
> ```

That should just become:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 29, 2023, 12:34pm UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/8 "2023-07-29T12:34:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Ian\_Kennedy](https://forum.xojo.com/user_avatar/forum.xojo.com/ian_kennedy/32/6860_2.png) [@Ian\_Kennedy](https://forum.xojo.com/u/Ian_Kennedy)\
**Post date:** [July 29, 2023, 12:37pm UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/9 "2023-07-29T12:37:14Z")

</div>

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 29, 2023, 12:38pm UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/10 "2023-07-29T12:38:03Z")

</div>

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

Report tomorrow.

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 30, 2023, 7:44am UTC](https://forum.xojo.com/t/exception-near-exception-error-number-1/76668/11 "2023-07-30T07:44:22Z")

</div>

Hi Ian,

you sent the correct answer. Thank you.
