Creating new entry using editor not working

dynasoft

Hi

I was emailing Allan about some issues recently. I'm using the same form and data as per email msgs and am not able to create a new line in db. Deleting and editing a line work. Validator not triggering either, but it does trigger when trying to edit a record. Tried comenting out 'Field(new Field("id")' but to no avail. Please advise.

            using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
            {
                dtResponse = new Editor(db, "Types")

                .Model<TypeSDBModel>()
                //.Field(new Field("id")
                //    .Set(false)
                //)
                .Field(new Field("TypeName")
                    .Validator(Validation.NotEmpty(new ValidationOpts
                    {
                        Message = "EnsureValueSelected"
                    }))
                )
                .Field(new Field("GroupName")
                    .Validator((val, d, host) => Convert.ToString(val) == "-- " + "SelectOption" + " --" ? lblo.lblEnsureValueSelected : null)
                )
                .Field(new Field("CategoryName")
                    .SetFormatter((val, host) => Convert.ToString(val) == "-- " + "SelectOption" + " --" ? string.Empty : null)
                )
                .Field(new Field("TypeTerm")
                    .SetFormatter(Format.IfEmpty(null))
                )
                .Field(new Field("PeakTime")
                    .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 2))
                    .SetFormatter((val, data) => CommonUtilities.FormatDateForDB(CommonUtilities.FormatDateAddTime(string.Empty, CommonUtilities.ToString(val)), true))
                )
                .Field(new Field("PeakTimeTo")
                    .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 2))
                    .SetFormatter((val, data) => CommonUtilities.FormatDateForDB(CommonUtilities.FormatDateAddTime(string.Empty, CommonUtilities.ToString(val)), true))
                )
                .Field(new Field("WeekEndDay")
                    .GetFormatter((val, data) => MainUtilities.ConvertToDayOfWeek(CommonUtilities.ToInt16(val, -1)))
                    .SetFormatter((val, data) => MainUtilities.ConvertDayOfWeek(CommonUtilities.ToString(val)))
                )
                .Field(new Field("WeekEndTime")
                    .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 2))
                    .SetFormatter((val, data) => CommonUtilities.FormatDateForDB(CommonUtilities.FormatDateAddTime(string.Empty, CommonUtilities.ToString(val)), true))
                )
                .Field(new Field("WeekEndDayTo")
                    .GetFormatter((val, data) => MainUtilities.ConvertToDayOfWeek(CommonUtilities.ToInt16(val, -1)))
                    .SetFormatter((val, data) => MainUtilities.ConvertDayOfWeek(CommonUtilities.ToString(val)))
                )
                .Field(new Field("WeekEndTimeTo")
                    .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 2))
                    .SetFormatter((val, data) => CommonUtilities.FormatDateForDB(CommonUtilities.FormatDateAddTime(string.Empty, CommonUtilities.ToString(val)), true))
                )
                .Where("SupplierIndex", id)
                .TryCatch(false)
                .Process(formData)
                .Data();
            }

dynasoft

Forgot to say i'm not getting any error message

allan

Can you add .Debug(true) before the .Process() can please, and then show me the JSON response from the server when you attempt to create a new record.

Thanks,
Allan

dynasoft

"SELECT [id] as 'id', [TypeName] as 'TypeName', [GroupName] as 'GroupName', [CategoryName] as 'CategoryName', [TypeTerm] as 'TypeTerm', [PeakTimeFrom] as 'PeakTimeFrom', [PeakTimeTo] as 'PeakTimeTo', [WeekEndDayFrom] as 'WeekEndDayFrom', [WeekEndTimeFrom] as 'WeekEndTimeFrom', [WeekEndDayTo] as 'WeekEndDayTo', [WeekEndTimeTo] as 'WeekEndTimeTo', [SupplierIndex] as 'SupplierIndex', [Exported] as 'Exported' FROM [CallTypes] WHERE [SupplierIndex] = @where_0 AND [id] = @where_1 "

"DECLARE @T TABLE ( insert_id bigint ); INSERT INTO [CallTypes] ( [TypeName], [GroupName], [CategoryName], [TypeTerm], [PeakTimeFrom], [PeakTimeTo], [WeekEndDayFrom], [WeekEndTimeFrom], [WeekEndDayTo], [WeekEndTimeTo] ) OUTPUT INSERTED.ID as insert_id INTO @T VALUES ( @TypeName, @GroupName, @CategoryName, @TypeTerm, @PeakTimeFrom, @PeakTimeTo, @WeekEndDayFrom, @WeekEndTimeFrom, @WeekEndDayTo, @WeekEndTimeTo ); SELECT insert_id FROM @T"

allan

One thing I don't see in the INSERT there is a SupplierIndex, but you have a WHERE condition on that column when selecting. So it seems to me that the row is probably being inserted okay (if you check the db directly, is that the case?) but without the corresponding SupplierIndex it can't be reselected due to the condition.

Allan

dynasoft

Thanks. The records are indeed in the db. Not sure why they're not showing when reading the table. Will test further.

dynasoft

Hi, I've added this:

                .Field(new Field("SupplierIndex")
                    .Set(true)
                    .SetFormatter((val, data) => id)
                )

But the SupplierIndex keeps coming up as 0 in the db but id is > 0. Any idea? Thanks

dynasoft

Also, when adding this Validator is never shown back to the user.

                .Field(new Field("GroupName")
                    .Validator((val, d, host) => Convert.ToString(val) == "-- " + lblo.lblSelectOption + " --" ? lblo.lblEnsureValueSelected : null)
                )

dynasoft

Finally, I need to run some sql after your code. How can I do CRUDs to another table?

allan

Could you try this please:

.Field(new Field("SupplierIndex")
    .Set(true)
    .SetValue(id)
)

I suspect the issue is that it isn't being submitted from the client-side so the set formatter isn't being run (it doesn't have a value). Field.SetValue() will give it a value.

Finally, I need to run some sql after your code. How can I do CRUDs to another table?

A server-side event is the way to do this.

Allan

dynasoft

Thanks. The data I need for the sql code is the value of TypeName before the user changes it and the new value. How can I store the old value and use it when your call is called again for the update routines? Thanks a lot.

allan

I'm not quite following I'm afraid. Do you want to use the data that was submitted in your own SQL statement in the event handler? The data is available in the parameters passed into the event handler.

Allan

dynasoft

We have other tables than the CallTypes table where call types are used. If the user decides to change the name of a call type, this impacts these other tables and the change of value needs to be reflected there as well The problem is these other tables do not use the CallTypes table's ID, just the call type name.

dynasoft

We need a mix of the PreEdit event and the PostEdit event. PreEdit to store the old value or get it from the client somehow, and PostEdit to execute that secondary sql. In standard sql this is what we would run:

UPDATE MyOtherTable SET CallType = 'newCallType' WHERE CallType = 'oldCallType'

allan

The PostEdit event should be all you need - it has a Value property passed into its second parameter which you can use to access the submitted data (equally there is a Row property which contains the new row data):

editor.PostEdit += (sender, e) => {
  e.Values["properyName"] // do something
}

(I should warn that its possible I've muddled my C# syntax with JS there! I think that's right, but I've not written C# in a few weeks now and don't have my Windows machine to hand at the moment, but that's the idea!).

Allan

dynasoft

Hi, tried to use PostEdit to get the value before the edit as I tried to explain above but only get the new value entered by user. PreEdit also gives the new value. Still need a way to get the value before the edit, ie the original value so I can run the sql query or use it using your system.
Even reading e.Data on any of the events always returns the new data being submitted. Please advise.

allan

Oh I see! Sorry, I misunderstood. Editor (client-side) doesn't send the previous value, so you'd need to use a PreEdit event to query the database for the row in question to get its data (current values). The current values aren't automatically populated in any of the Editor parameters when doing an edit (as they aren't needed).

Allan

dynasoft

Hi. Thanks. Thats is what I realised I had to do. Thanks for clarifying it.