Per my boss, Ken Vaughn:
There is a particular record in a custom table as seen through a custom tab in Tessitura that refuses to update. The error message is as follows:
Row changed between retrieve and update.
No changes made to database.
UPDATE hos_gen_info SET pmt_recd = 'Y' WHERE id_key = 0 AND customer_no = 3004517 AND voice_type = 'Tenor' AND app_type = 'S' AND resident_status = 'US' AND other_citizen = '' AND recommendation_1 = 'Maitland Peters, Voice Department Chair, Manhattand Sch' AND recommendation_2 = 'Gordon Ostrowski, Director of Opera Studies, Manhattan' AND recommendation_3 = '' AND english = 'Native' AND german = '1' AND italian = '2' AND french = '2' AND spanish = '3' AND sings_languages = 'Latin: 4 years' AND pmt_info = 'Visa' AND pmt_recd = 'N' AND prior_applicant = 'Yes' AND prior_applicant_years = '2008' AND prior_finalist = 'No' AND prior_finalist_years = '' AND other_name_used = '' AND notes1 = 'Manhattan School of Music: Professional Studies (tba 2010), Master of Music (2009). Eastman School of Music: Bachelor of Music (2007)' AND notes2 = 'Teachers: Maitland Peters, Cynthia Hoffmann, Constance Haas, Gisela Goettling. Coaches: Rachelle Jonck, Lucy Yates, Kenneth Merrill, Shane Schag, Thomas Muraco, William Tracy' AND notes3 = 'Teachers: Rhoda Levine, Gretchen Sonstroem. Coaches: Catherine Malfitano, Dona D. Vaughn, Carolyn Marlow.' AND notes4 = 'Richard Rodgers Scholar/Rodegers and Hammerstein Foundation Award (2010), Rodgers and Hammerstein Foundation Award (2008-2009), Howard Hanson Scholar (2003-2007).' AND notes5 = 'Bel Canto at Caramoor Young Artist (2009), OperaWorks (2008)'
It would make sense to go to the id_key and notice that there are over 400 records added to that table with an id_key = 0 (which needs addressing, of course), but whenever any information is updated for that particular record we get that error.
Any ideas?
Thanks in advance, Tessiturians.
BONUS TRIVIA: Julio Iglesias used to play goal for Real Madrid's second team.
I have found this usually means some weird character someplace in the record. However, I don't see any in the statement you posted. What happens if you try to execute that statement in SQL Mgt Studio?
David
I get nothing back. No errors, but no update. If I change the UPDATE part to a SELECT...WHERE statement, I get no rows back, as in that record (as qualified by the where statement) doesn't exist, which at least explains nothing happening. But why the "record has changed between retrieve and update"? And this is the only record that happens on ever. And it happens every single time you try to update one of the custom tabs.
Thanks for your help.
Try taking out the criteria one at a time and find the one that is causing the where to fail. Perhaps that will shed some light on this situation.
Hi Matt,
Good day.
As I can see, only one field is updating here which is "pmt_recd".
1, try this:
select * from hos_gen_info WHERE customer_no = 3004517 AND voice_type = 'Tenor' AND app_type = 'S' AND resident_status = 'US' AND other_citizen = '' AND recommendation_1 = 'Maitland Peters, Voice Department Chair, Manhattand Sch' AND recommendation_2 = 'Gordon Ostrowski, Director of Opera Studies, Manhattan' AND recommendation_3 = '' AND english = 'Native' AND german = '1' AND italian = '2' AND french = '2' AND spanish = '3' AND sings_languages = 'Latin: 4 years' AND pmt_info = 'Visa' AND pmt_recd = 'N' AND prior_applicant = 'Yes' AND prior_applicant_years = '2008' AND prior_finalist = 'No' AND prior_finalist_years = '' AND other_name_used = '' AND notes1 = 'Manhattan School of Music: Professional Studies (tba 2010), Master of Music (2009). Eastman School of Music: Bachelor of Music (2007)' AND notes2 = 'Teachers: Maitland Peters, Cynthia Hoffmann, Constance Haas, Gisela Goettling. Coaches: Rachelle Jonck, Lucy Yates, Kenneth Merrill, Shane Schag, Thomas Muraco, William Tracy' AND notes3 = 'Teachers: Rhoda Levine, Gretchen Sonstroem. Coaches: Catherine Malfitano, Dona D. Vaughn, Carolyn Marlow.' AND notes4 = 'Richard Rodgers Scholar/Rodegers and Hammerstein Foundation Award (2010), Rodgers and Hammerstein Foundation Award (2008-2009), Howard Hanson Scholar (2003-2007).' AND notes5 = 'Bel Canto at Caramoor Young Artist (2009), OperaWorks (2008)'
do you get something from SQL Mgt Studio?
if not, how did this record display on the custom tab?
2, then try this
select * from hos_gen_info WHERE customer_no = 3004517
Find out which one is on the screen. Duplicates?
have fun.
Ben