nowinski
commented
3 years ago Traced to getInsertQuery() in DatasetDataDetailHelper.cs. NULL is added to detailValues when objVal is NULL or when JTokenType is NULL. In this case incoming values are empty strings so we get malformed INSERT
2021-04-22 08:33:56.9508|DEBUG|services.Controllers.CDMSController| INSERT INTO SGS_Redd_Detail (ActivityId,ByUserId,EffDt,RowStatusId,RowId,QAStatusId,Count,Latitude,Longitude,ReddSpecies,WPTComments,WPTName,WPTType) VALUES ( 59760,1,'4/22/2021 8:33:56 AM',0,4,1,0,,,'S_STH','','NA','New Redd')
So, add NULL to detailValues when JTokenType is String and the string is empty
Hey Joe –
I wanted to send you this issue to see if you are able to reproduce it on your end. We have a biologist who is making a large quantity of edits, but not always in a way that is ‘correct’ from an Effective-Dating standpoint. Based on what I have seen in SSMS, to make things identical with the data sheets, new rows with new information are added, and the original rows will be duplicated to new lines (i.e. creating an identical record, but with a new RowID), and then deleting the original records – I believe this is to match the order of things on the data sheet. I think in the case of creating these duplicate records he is performing a copy-paste. In some of these activities he has edited, he was able to save it at the time of those major edits (adding new records, duplicating the existing ones and removing the original records) it won’t allow any more edits, and sends me to line 570 of the ActivityController. I believe you have a copy of our database, so if you’re curious to see what he’s done to these activities, check out ActivityID 41306 and 41308 with the EffDt filter removed so you see edits. There may be additional activities with this behavior I don’t know about yet.
I’ve been performing this testing in our SGS_Redd dataset for JCAPE M&E (home dataset of 41306 & 41308). Here’s the odd behavior I found.
• I search for an activity with several detail records. For this iteration I will use activity 60303. o This has two detail records, no errors. All good. o
• If I click Add Row x2, and then select the original two records and do a copy-paste into the 2 new rows, it pastes as expected. Whether I delete the original two rows or not, I am unable to save. o
• If I reset the page so I am starting fresh, I click Add Row x2 again, but this time I hand-enter all the values identical to the first two records. o
• Regardless of whether I remove the original two rows or not… o
As I run through this, it seems like it’s the copy+paste operation. I have gone into some activities with 50+ detail records and tried to copy+paste one record, creating 1 duplicate, and it’s not allowing save. Always pointing me to: ervices.Controllers.ActivityController.UpdateDatasetActivities_SQL(JObject jsonData) in C:\cdms-dev\cdms-be-public\services\Controllers\ActivityController.cs:line 570
I’m curious if you also see this behavior. It sure seems associated with the copy+paste action. If you want a better description of what I believe the odd editing behaviors are that created this, please give me a call. I don’t feel like I am describing it very well via text.
Thanks!
Tyler Stright Fisheries Biologist Nez Perce Tribe – DFRM (208) 634 – 5290 Ext: 3314