# SQL error

**URL:** <https://forum.inductiveautomation.com/t/sql-error/8049>\
**Category:** Ignition\
**Created:** [February 24, 2014, 5:02pm UTC](https://forum.inductiveautomation.com/t/sql-error/8049 "2014-02-24T17:02:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![HALW80](https://avatars.discourse-cdn.com/v4/letter/h/47e85d/32.png) [@HALW80](https://forum.inductiveautomation.com/u/HALW80)\
**Post date:** [February 24, 2014, 5:02pm UTC](https://forum.inductiveautomation.com/t/sql-error/8049/1 "2014-02-24T17:02:41Z")

</div>

Maybe someone could point out to me where I am going wrong?

I have the following table created:  
CREATE TABLE REPORT\_WTW2  
(  
DateofReport varchar(10),  
SiteName varchar(50),  
Weather varchar(10),  
Caretaker varchar (100),  
);

Using a button on a window I have the following script:  
date1 = event.source.parent.getComponent(‘Label Day’).text  
date1 = date1 + “-”  
date1 = date1 + event.source.parent.getComponent(‘Label 7’).text  
date1 = date1 + “-”  
date1 = date1 + event.source.parent.getComponent(‘Label 9’).text

site1 = str(event.source.parent.getComponent(‘Dropdown Site’).selectedStringValue)  
weather = str(event.source.parent.getComponent(‘Dropdown’).selectedStringValue)  
caretaker = str(event.source.parent.getComponent(‘Dropdown Care’).selectedStringValue)

system.db.runUpdateQuery(“INSERT INTO REPORT\_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (%s, %s, %s, %s)” % (date1, site1, weather, caretaker))

I am getting the following error:  
Traceback (most recent call last):

File “event:actionPerformed”, line 12, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO REPORT\_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (19-02-2014, testsitename, Dry, John McCarney), , , false)

```
caused by Exception: Error executing system.db.runUpdateQuery(INSERT INTO REPORT_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (19-02-2014, testsitename, Dry, John McCarney), , , false)
caused by GatewayException: SQL error for "INSERT INTO REPORT_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (19-02-2014, testsitename, Dry, John McCarney)": Incorrect syntax near 'McCarney'.
caused by SQLServerException: Incorrect syntax near 'McCarney'.
```

---

<div class="post-metadata">

**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\
**Post date:** [February 24, 2014, 5:35pm UTC](https://forum.inductiveautomation.com/t/sql-error/8049/2 "2014-02-24T17:35:49Z")

</div>

Looks to me like you will need single quotes around your values, SQl is trying to interpret the values as SQL commands.

Maybe do something like:  
system.db.runUpdateQuery(“INSERT INTO REPORT\_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (’%s’, ‘%s’, ‘%s’, ‘%s’)” % (date1, site1, weather, caretaker))

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [February 24, 2014, 5:56pm UTC](https://forum.inductiveautomation.com/t/sql-error/8049/3 "2014-02-24T17:56:53Z")

</div>

I would use this:

```auto
system.db.runPrepUpdate("INSERT INTO REPORT_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (?,?,?,?)", [date1, site1, weather, caretaker])
```

---

<div class="post-metadata">

**Author:** ![JGJohnson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jgjohnson/32/29582_2.png) [@JGJohnson](https://forum.inductiveautomation.com/u/JGJohnson)\
**Post date:** [February 24, 2014, 7:05pm UTC](https://forum.inductiveautomation.com/t/sql-error/8049/4 "2014-02-24T19:05:10Z")

</div>

[quote=“nmudge”]I would use this:

`
system.db.runPrepUpdate("INSERT INTO REPORT_WTW2 (DateofReport, SiteName, Weather, Caretaker) VALUES (?,?,?,?)", [date1, site1, weather, caretaker])
`[/quote]

Nice! I hadn’t noticed that function before. This definitely looks like a better solution.

---

<div class="post-metadata">

**Author:** ![HALW80](https://avatars.discourse-cdn.com/v4/letter/h/47e85d/32.png) [@HALW80](https://forum.inductiveautomation.com/u/HALW80)\
**Post date:** [February 25, 2014, 8:35am UTC](https://forum.inductiveautomation.com/t/sql-error/8049/5 "2014-02-25T08:35:57Z")

</div>

nmudge I used your statement and it worked perfect. thanks a million. I was pulling my hair out!!  
 ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:")