SQL error

Ignition
1

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'.
2

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

3

I would use this:

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

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

5

nmudge I used your statement and it worked perfect. thanks a million. I was pulling my hair out!!
:thumb_left: