How can I build an SQL query command string with submethod variables?

Hamilton (Venus)
1

Hello Everyone!
Im trying to do something a little unconventional (at least according to the information I was able to find about it…), and Im trying to build a library that will update an Access database with real-time information about the instrument for an equipment dashboard.

Im having trouble setting the values of the database though. I am able to hardcode the values to be updated, but that really kneecaps the utility of my library. I need my SQL query to accept variable values, but I can’t seem to get the syntax right. Im not even sure if it’s possible.

For example, i have a .mdb file ‘Database1.mdb’ with a table 'Table1" and 1 column ‘AssayStep’ that Ive successfully connected to.
I am able to modify the database with command:

UPDATE Table1 SET AssayStep = (inputValueFromMainMethod) WHERE ID = 1

where inputValueFromMainMethod is a string variable passed in from the calling method.

If I replace that variable with a hardcoded string value, it seems to work, but I get a ‘missing required parameters’ error if I try to use the variable (I have also tried containing it in square & curly brackets - no good.).

I even went as far as trying to build out my query by concatenating a string variable, but it didnt like that at all either. If anyone has ideas or examples, you’d be a hero!

2

Can you please trace out your attempted command strings and post them here? Any and all usage of variables to construct queries in VENUS is supported. This is likely a syntax issue.

SQL queries using string matches require that the queried string value be surrounded by single quotes (').

Check this SQL statement guide from the Hamilton materials share which provides plenty of syntax examples for using SQL queries in VENUS.

-Nick

3

Do you think an API would a better suit for this? I’m using HSLHttp and HttpJSON to update a database with data from the equipment.

Maybe you can do a better job managing variables learning more about HSL code. Here some thread about it: Ways to learn Hamilton HSL code? - #2 by O_O

1 Like