I pre-made a recipe list for a customer from the office but when I got on-site it looks like I was given some wrong information. At the office I used a custom made recipe editor to add fields into SQL with transaction groups. I added ~300 recipes with operation numbers, material number, names, etc. The problem I found is that lets say operation number “400” was in fact “0400” on their system.
So, instead of re-making all recipes with the new operation numbers I went in to SQL and edited all rows and added the “0” in front of every operation number. Now when I go to load the recipe from ignition, it can no longer find the recipe even though the operation number matches to what is in the SQL Database:
This only happens on any part that I had to edit with SQL. If I load a part that I didn’t edit (one that was already 4 characters long) it loads just fine.
Has anyone ran across this issue before? I am minor when it comes to SQL programming, but it almost seems like I have to re-initialize the database for Ignition to read it again. I can save the exact same part information into the database and it will find it after, but then I show two recipes with the same values. My search for my transaction group is the following:
OP_NUM_EDIT_SP = ‘{[default]Collins/Oven_4/DATABASE/OP_NUM_EDIT_SP}’ AND MATERIAL_NUM_EDIT_SP = ‘{[default]Collins/Oven_4/DATABASE/MATERIAL_NUM_EDIT_SP}’
What does the query you used to make the edit in SQL look like?
Is it possible that you used a transaction but didn’t confirm the changes? Perhaps the transaction is still open and your changes are in some kind of limbo?
If you do a select from the DB Management Suite does it find the data as you expect? If you try to Select the data based on the old Operation Number does that work?
This is the statement I used and then edited all rows in operation number column. I used the pre-caned EDIT TOP 200 Rows then just changed the 200 to 1000
I made the changes and closed the SQL studio.
It does find the data as expected. The old operation number does not come up.
In some environments, an extra leading zero on an integer causes it to be interpreted as octal instead of decimal.
To reliably use leading zeros, the column types must be characters, not numbers. And all of the properties and parameters along your query chain must also be strings.
Might just be a typo but it looks like you are selecting the ndx field twice but one is recipes and the other is recipe?
oven4_recipes_ndx and oven4_recipe_ndx?
I just created a DB to OPC group as a test and it is working fine with a similar setup.
One thing I am noticing is you have single quotes around the tag in the Where clause.
Try removing the single quotes, they aren’t needed.