Get KoolPHP UI with 30% OFF!

Inline editing sometimes works and sometimes it doesn't. Mostly it doesn't.

Blaine Sherman
I have a fairly simple grid I'm using to retrieve contact information from our SQL Server. The SELECT works exactly as expected. It populates my grid. I have an EDIT column that allows you to edit two fields, the remainder of the columns are read-only. The problem I'm having is sometimes an INSERT or UPDATE works, but most the time it doesn't. I'll click on the edit icon, the fields become available, I type something in the field(s) and click the okay/save icon. The grid appears to refresh, but there is no data in those fields. Almost as if the event that UPDATES or INSERTS is not being fired.
The code is fairly simple, the INSERT and UPDATE queries are simple. It's just that I can try this 20 times, and it will only work 2 or 3 of the times. There are other times it will work great and there are no problems for 10-20 INSERT/UPDATES, then for no reason it stops working again. I'm SELECTing from one table using a JOIN to another table, but my INSERTS/UPDATES go the second table using an ID field from the first.
I've had to start rebuilding this in ASP.NET using another control, but this should be working and I'd like to see how I can diagnose and fix. Is there some sort of event or something that I can fire that would allow me to confirm if it truly is firing, or if there is another problem. Maybe like a Javascript popup that shows "UPDATE/INSERT has been fired using this <show query>" or something like that?
Posted Jun 27, 2017 Kool
Anthony Amolochitis
What is input that works and input that does not work?
Posted Jun 27, 2017 Kool
Blaine Sherman
Sometimes when I click the edit button and fill out the 2 fields and click the save button, it inserts as expected into the database.But 90% of the times I do that and click the save icon, the two fields remain blank and the data is not inserted into the database. Though I'm editing the nickname and contact group, I'm inserting those two fields along with the CntctKey field into another table than I use for select. I hope that makes sense.

But every now and then it works, but rarely. It's just not consistent. As I was saying earlier, I can try maybe 20 times and get a couple to insert after saving. Sometimes I can insert a record, then go back and edit it (update) and it works the first time, but I immediately try it again a few seconds later and it ignores the update. That's why I'm concerned the event is even firing. I'm not change any code. It's doing this without anything, but maybe a browser refresh, that's it.
Posted Jun 27, 2017 , edited Jun 27, 2017 Kool
Anthony Amolochitis
I guess my question is can the same nickname / control group fail the first time, then insert the second time?
Posted Jun 27, 2017 Kool
Blaine Sherman
Sometimes. Sometimes it takes ten tries.
Okay, I was going to test for this reply. Now it's working every time. I've been struggling with this for a week. I'm going to kick the tires on this and see if I can break it again. I have no idea why this wasn't working 10 minutes ago at all, and now I've done maybe 20 of them and everyone worked. I'm even able to edit exiting fields that I added earlier.
I think I'll put a trace on this table on our SQL server and see what's going on at the database.
I will report back and try and see what changed.
Posted Jun 27, 2017 Kool -
Blaine Sherman
Okay, I think I got it figured out and found a pattern using SQL Server Profiler. This grid is ONLY running the UPDATE trigger, not the INSERT trigger. I assume this has something to do with the SELECT query coming from Table#1 and the INSERT/UPDATE queries going into Table#2.
SELECT query:
SELECT VendKey,VendID,VendName,CntctKey,PrimaryContactEmailAddr,Nickname,ContactGroup,BuyerKey,BuyerName,OwnerID,OwnerName FROM vdvVendorsEmailByBuyerAndOwner

INSERT query:
INSERT into custContact (CntctKey,CntctNickname,CntctGroup) Values ('@CntctKey','@Nickname','@ContactGroup')

UPDATE query:
UPDATE custContact set CntctNickname='@Nickname', CntctGroup='@ContactGroup' WHERE CntctKey='@CntctKey'

The only editable fields in the grid are Nickname and Contact Group. Those fields should be INSERTED or UPDATED with the Contact Key from Table#1 into Table#2. If those two fields were empty before editing, it MUST do an INSERT into Table#2 since there is no record to update in Table#2. However, the Grid is triggering the UPDATE query, so it fails (with no error in the Grid).
If one or both fields are already in the Grid, I'm able to edit the fields and when I click save it triggers the UPDATE query, as expected, and it updates the record in the database.
So the bottom line is how I have the grid built using Table#1 for the SELECT and Table#2 for for the INSERT/UPDATE, it will always fire the UPDATE query and never the INSERT.
My fix was to build a Stored Procedure in SQL and use that for the UPDATE query.
EXEC mmsInsertNickname '@CntctKey', '@Nickname', '@ContactGroup'

This Stored Procedure checks if the record exists and if it doesn't it inserts, if it does, it updates.
ALTER PROCEDURE [dbo].[mmsInsertNickname]
(
    @CntctKey int,
    @CntctNickname Varchar(100),
    @CntctGroup Varchar(100)
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM custContact
        WHERE CntctKey=@CntctKey
    )
    BEGIN
        UPDATE custContact set CntctNickname=@CntctNickname, CntctGroup=@CntctGroup
        WHERE CntctKey=@CntctKey
    END
ELSE
    BEGIN
        INSERT into custContact (CntctKey,CntctNickname,CntctGroup) Values (@CntctKey,@CntctNickname,@CntctGroup)
    END
END

I struggled with this for many days. I was frustrated enough to start coding using a new control in ASP.NET, but now that this is resolved, I don't have to rebuild the entire thing.
I wish this was better documented so I didn't have to struggle with this.
Posted Jun 28, 2017 , edited Jun 28, 2017 Kool -
Anthony Amolochitis
Good job on tracing the error. You can also use custom event handles that give you more control with your grids sql events.
I can run as many updates, selects, inserts within a transaction, or not, in one save click event.
I have to say the grid is completely flexible, and the insert edit forms are really good as well.
There are code samples I input on the forum if you need them as well.
https://www.koolphp.net/forum/threads/82.1/koolgrid-with-an-event-handler-class--basic-crud-ops-included-in-the-class.html
Posted Jun 28, 2017 Kool
Blaine Sherman
I'll check that out. Thank you!
Posted Jun 28, 2017 Kool