I have a Wonderware standalone HMI that is gathering process data from our production line and storing it in a MySQL database. Initially, I experimented with MS Access. The issue arises when using two different insert options. When utilizing the SQLInsert function, everything functions smoothly without errors. However, when employing the SQLInsertPrepare and SQLInsertExecute functions, I encounter an "item cannot be found in the requested ordinal" error. The script for a successful insert without any hitches includes: ResultCodePreparL1O = SQLInsert(Quad_Connection, "STR1_Overview_Report", "STR1_Overview"); ResultCodePreparL1B = SQLInsert(Quad_Connection, "STR1_Bronx_Report", "STR1_Bronx"); ResultCodePreparL1N = SQLInsert(Quad_Connection, "STR1_NDT_Report", "STR1_NDT"); On the other hand, the script causing errors during insert preparation and execution is as follows: ResultCodePreparL1O = SQLInsertPrepare(Quad_Connection, "STR1_Overview_Report", "STR1_Overview", STR1_OV_State); ResultCodeInsertL1O = SQLInsertExecute(Quad_Connection, "STR1_Overview", STR1_OV_State); ResultCodeEndL1O = SQLInsertEnd(Quad_Connection, STR1_OV_State); Despite using different scripts, the errors are consistent in both MySQL and Access databases. Presumably, the issue lies in the table name and bindlist. I find it perplexing how the straight insert command works smoothly while the execute command presents an error. Additionally, the prepare and end commands do not generate any errors; it is specifically the InsertExecute command that triggers the error.
Hello tgulch, I am encountering a similar problem with Intouch 2023 R2 on Windows Server 2022 using SQL Express. While SQLInsert is functioning correctly and inserting data into the table without any issues, SQLSelect and SQLUpdate are not working properly. Have you been able to find a solution to this issue?
It sounds like your issue might not be with the databases, but rather the interaction between your script and the Wonderware system. Ensure that the bindlist in your SQLInsertPrepare matches the column names in your table exactly, including any capitalization. Also, double-check that your STR1_OV_State holds the expected values; sometimes rogue whitespace or case sensitivity can throw these sort of functions. Remember, SQLInsert function uses actual data string while SQLInsertExecute uses a prepared statement style, hence errors might pop up if the underlying data does not match the format or type expected by the binded placeholders. One final suggestion, if the table has many columns and you only want to insert into specific ones, make sure to specify these columns directly in your SQLInsertPrepare function. This way, you minimize the possibility of wrong assignments that may lead to such ordinal errors.
It sounds like you have done some comprehensive troubleshooting, thanks for providing such detailed information. This error often occurs due to a mismatch within the cardinality of your data and your chosen parameter's place holder. The SQLInsertPrepare and SQLInsertExecute commands require an exact correlation between each field and each value's position in the table. So, it could be worth revisiting your bindlist parameter and checking whether they exactly match the number and order of fields in your "STR1_Overview" table. Another potential screw might be type mismatch, ensure that your STR1_OV_State variable's data type matches with your table's respective column. And lastly, check if all the fields you're interacting with exist. If these considerations don't resolve the issue, you may need more specific debugging methods.
It appears that the problem lies specifically with the SQLInsertExecute function. In addition to the table name and bindlist, the appropriate order in which these functions should be used could be puzzling. If I get it correctly, you may need to make sure the corresponding SQLInsertPrepare function has been called successfully before you call SQLInsertExecute. Another detailed element to check is the 'STR1_OV_State' argument you're passing into SQLInsertExecute. It should match the bindlist generated in SQLInsertPrepare. Also, just to rule out all possibilities, ensure that the 'Quad_Connection' is open and functioning before performing the insert operation. Do share any errors you receive in the process.
✅ Work Order Management
✅ Asset Tracking
✅ Preventive Maintenance
✅ Inspection Report
We have received your information. We will share Schedule Demo details on your Mail Id.
Answer: Answer: The issue could be related to the table name and bindlist used in the SQLInsertPrepare and SQLInsertExecute functions. Double-check the table name and bindlist parameters to ensure they are correctly specified.
Answer: Answer: The discrepancy in error occurrence between SQLInsert and SQLInsertPrepare/SQLInsertExecute functions could be due to how the insert commands handle table name and bindlist parameters. Review the implementation of these functions to identify any discrepancies causing the error.
Answer: Answer: When dealing with SQL insert operations in Wonderware Intouch with MySQL or MS Access databases, ensure consistency in table names, bindlist parameters, and command syntax across different functions to avoid errors like the "item cannot be found in the requested ordinal" issue.
Join hundreds of satisfied customers who have transformed their maintenance processes.
Sign up today and start optimizing your workflow.