INSERT SQL Event Handler

Archived Topics
1

Hello:

I am new to the forums and was hoping someone could clear up an issue for me.

I am building a form in a window and want to write a new record to a database when they click a button.

Attached is the code I’m using.

Insert SQL Event Handler Code.PNG825×663 47.4 KB

It writes to the SQL just fine, but doesn’t show up on my form.

screen.PNG778×414 12.4 KB

I suspect I am handling the system.db.startTransaction, system.db.commitTransaction, and system.db.closeTransaction wrong, as I have open process locks in my SQL, data in the tables, and nothing showing in my form.

Any help would be great.

Thanks!

2

Hi,

Nice form… can you explain what you do with it ?

For your pb, I suspect you only need to make a refresh of the dataset after committing your request ; in your case, the table component data; see appendix C of the help, system.db.refresh(component, propertyName) (http://www.inductiveautomation.com/support/usermanuals/ignition/system_db_refresh.htm).

3

Thank you!

The form replaces an Access database that we are using to track Daily Time Sheet Entries. Once I can accurately insert, update and delete, I will be working on reporting and other analytical functions.

Added the refresh data with no luck.

Event Handler Update.PNG825×663 49.9 KB

The insert is working on the MS SQL Server ( I am seeing data) but it is not refreshing in the query. My research indicates that it won’t show up in Ignition platform until the transaction is closed. I do have locked processes when I look at the database management features in SQL 2005 Management Studio.

Thoughts?

4

Further investigation has revealed that my entries appear in the select query, but not in the table itself, if that helps at all…

5

Your insert query function is not using the transaction. That line would have to be:system.db.runPrepUpdate("insert into TblServiceLog values (?,?,?,?,?,?,?,?,?)", [Tech,Date,CustID,Desc,Class,Bill,Start,End,Entry], tx=txID)I suspect the information is in the database but you are filtering it out with your SELECT query using a WHERE clause. I would first check to make sure it exists in the table by doing a query like this:

SELECT * FROM TblServiceLog

If it is there check to see if your WHERE clause is filtering it out.

6

Thank you gents for your input!

I took Trav’s advice regarding the query, and it wasn’t drawing out the info.

I changed the date property from .date (which sent m.d.y.h.m.a to my table) to .text (which sends just m.d.y), and then the query pulled the info across.

Thanks for the second set of eyes on this!