# SQL Edit Command Doesn't Allow Transaction Group to Find Value **URL:** \ **Category:** Ignition\ **Tags:** ignition80\ **Created:** [April 28, 2022, 11:38am UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980 "2022-04-28T11:38:16Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![james4](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james4/32/49597_2.png) [@james4](https://forum.inductiveautomation.com/u/james4)\ **Post date:** [April 28, 2022, 11:38am UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/1 "2022-04-28T11:38:16Z")
Good morning, 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: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/9/19bf85701881155ad3251b8c2b93dde64df38950.png) 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}’ ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 28, 2022, 11:49am UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/2 "2022-04-28T11:49:14Z")
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? ---
**Author:** ![james4](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james4/32/49597_2.png) [@james4](https://forum.inductiveautomation.com/u/james4)\ **Post date:** [April 28, 2022, 11:52am UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/3 "2022-04-28T11:52:58Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/b/eb5711c6404e59ff71c7f2a06d1912b628faee81.png) 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. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 28, 2022, 11:56am UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/4 "2022-04-28T11:56:30Z")
Huh. I would expect it to work. What if you run a select statement from the Database Query Browser in ignition. Does that work as expected? Clearly I’m guessing here, I would have expected this to work. You may want to get a hold of support and have them take a look. ---
**Author:** ![james4](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james4/32/49597_2.png) [@james4](https://forum.inductiveautomation.com/u/james4)\ **Post date:** [April 28, 2022, 1:41pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/5 "2022-04-28T13:41:12Z")
Yes I did try that and it did work as well. I just tried exporting the table and renaming it when importing but it is doing the same thing as well. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 28, 2022, 1:44pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/6 "2022-04-28T13:44:57Z")
What are your column types? Some things to check: - 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. ---
**Author:** ![james4](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james4/32/49597_2.png) [@james4](https://forum.inductiveautomation.com/u/james4)\ **Post date:** [April 28, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/7 "2022-04-28T14:07:54Z")
All of my column types are strings, or characters as you said. Also all my my setpoints for the transaction group searches are strings. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [April 28, 2022, 2:46pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/8 "2022-04-28T14:46:53Z")
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? ---
**Author:** ![james4](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james4/32/49597_2.png) [@james4](https://forum.inductiveautomation.com/u/james4)\ **Post date:** [April 28, 2022, 2:49pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/9 "2022-04-28T14:49:04Z")
This is what I have set up in the transaction group manager: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/a/eac7e7ab08ffb6715f69263951ab0feb1ca66999.png) But I am not searing based on the index just the combination of the operation number and material number. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [April 28, 2022, 3:02pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/10 "2022-04-28T15:02:15Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 28, 2022, 3:29pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/11 "2022-04-28T15:29:44Z")
> [@MMaynard](#): > > Try removing the single quotes, they aren’t needed. He's using strings. They are needed. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [April 28, 2022, 3:43pm UTC](https://forum.inductiveautomation.com/t/sql-edit-command-doesnt-allow-transaction-group-to-find-value/58980/12 "2022-04-28T15:43:57Z")
Works fine for me without them. 🤷‍♂️ ![CapturePhil](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/3/6311ee8ee3cd63ee78fcd4f86a7be9b6656ba448.png)