Create item with SQL Server INSTEAD OF INSERT

SuiteProG

Hello,

I have a SQL table "T_LIST_DATA" and I have link datatables editor.

DB lines are read correctly.

But when I create item whis basic form (show picture), line are create in my DB but no show on my datatables.

My table have an "INSTEAD OF INSERT" trigger :

CREATE TRIGGER T_LIST_DATA_INSERT ON T_LIST_DATA
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO T_LIST_DATA([Label], [Key], [Version], [IsOnline], [Type], [Position], [IsAccessLimited], [IsInProjectTab], [IsReadOnly])
    SELECT [Label], [Key], [Version], [IsOnline], [Type], (SELECT MAX(Position) FROM T_LIST_DATA) + 1, [IsAccessLimited], [IsInProjectTab], [IsReadOnly]
    FROM inserted
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION
END
GO

My table :

CREATE TABLE [dbo].[T_LIST_DATA] (
    [Id]              INT           IDENTITY (1, 1) NOT NULL,
    [Label]           NVARCHAR (50) NOT NULL,
    [Key]             NVARCHAR (50) NOT NULL,
    [Version]         INT           CONSTRAINT [DF_T_LIST_DATA_Version] DEFAULT ((1)) NOT NULL,
    [IsOnline]        BIT           CONSTRAINT [DF_T_LIST_DATA_InOnline] DEFAULT ((1)) NOT NULL,
    [Type]            NVARCHAR (50) NULL,
    [Position]        INT           NOT NULL,
    [IsAccessLimited] BIT           CONSTRAINT [DF_T_LIST_DATA_IsAccessLimited] DEFAULT ((0)) NOT NULL,
    [IsInProjectTab] BIT NOT NULL DEFAULT ((0)), 
    [IsReadOnly] BIT NOT NULL DEFAULT ((0)), 
    CONSTRAINT [PK_T_LIST_DATA] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [U_LIST_DATA_POSITION] UNIQUE ([Position])
);

Request when create object :

action=create&data%5b0%5d%5bPosition%5d=&data%5b0%5d%5bId%5d=&data%5b0%5d%5bLabel%5d=test&data%5b0%5d%5bKey%5d=test&data%5b0%5d%5bType%5d=&data%5b0%5d%5bVersion%5d=&data%5b0%5d%5bIsOnline%5d=false&data%5b0%5d%5bIsAccessLimited%5d=false&data%5b0%5d%5bIsReadOnly%5d=false

Response of customEditor.Process(request).Debug(true).Data()

Thanks for your help

allan

The fact that debug in the response is null suggests that there is no SQL being executed by the Editor libraries.

Is your controller expecting GET parameters (rather than POST)?

Allan

SuiteProG

Hi Allan,

Thanks for your reply.

With new test, I have sometimes debug not null like this :

Query is :

DECLARE @T TABLE ( insert_id int ); INSERT INTO [T_LIST_DATA] ( [Key], [Type], [IsOnline], [IsAccessLimited], [IsReadOnly] ) OUTPUT INSERTED.Id as insert_id INTO @T VALUES ( @Key, @Type, @IsOnline, @IsAccessLimited, @IsReadOnly ); SELECT insert_id FROM @T

Thanks,

allan

Can you show me your controller please? I'm surprised the Editor libraries generate any SQL code like that.

Allan

SuiteProG

Hi Allan,

I have create a more simply sample of my problem

I use Editor NET Framework Demo (download on github) with this StaffController :

public class StaffController : ApiController
    {
        [Route("api/staff")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult Staff()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "datatables_demo")
                    .Model<StaffModel>()
                    .Field(new Field("first_name")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("last_name"))
                    .Field(new Field("extn")
                        .Validator(Validation.Numeric())
                    )
                    .Field(new Field("age")
                        .Validator(Validation.Numeric())
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    .Field(new Field("salary")
                        .Validator(Validation.Numeric())
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    .Field(new Field("start_date")
                        .Validator(Validation.DateFormat(
                            Format.DATE_ISO_8601,
                            new ValidationOpts { Message = "Please enter a date in the format yyyy-mm-dd" }
                        ))
                        .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                        .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_8601))
                    )
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }

I added this trigger on my DB :

CREATE TRIGGER TEST_TRIGGER ON [datatables_demo]
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO [datatables_demo](
      [first_name]
      ,[last_name]
      ,[position]
      ,[email]
      ,[office]
      ,[start_date]
      ,[age]
      ,[salary]
      ,[seq]
      ,[extn]
      ,[Test])
    SELECT
      [first_name]
      ,[last_name]
      ,[position]
      ,[email]
      ,[office]
      ,[start_date]
      ,[age]
      ,[salary]
      ,[seq]
      ,[extn]
      ,[Test]
    FROM inserted
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION
END
GO

When trigger is active, new item are added in my db but I don't see in my datatables.

I thinks this sample is clearer

Thanks,

allan

Yes, that clarifies things - thanks! Because its not Editor that is actually doing the SQL insert it can't get the primary key for the new row in the table, thus won't be able to read it.

I can't immediately thing of any way to make that work I'm afraid. I'm unsure why you need it though - Editor will do a rollback if an error occurs from the PHP.

Allan

SuiteProG

Hi Allan,

I had a problem similary with Entity Framework and I resolved with

SELECT id FROM [datatables_demo] where @@ROWCOUNT > 0 AND Id = SCOPE_IDENTITY() at end of my trigger.

I work for EF but not for datatables. My new error is : " Cannot retrieve inserted id - no primary key was found".

Is there a solution with select at end of my trigger ?

My complete new Trigger :

INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO [datatables_demo](
      [first_name]
      ,[last_name]
      ,[position]
      ,[email]
      ,[office]
      ,[start_date]
      ,[age]
      ,[salary]
      ,[seq]
      ,[extn]
      ,[Test])
    SELECT
      [first_name]
      ,[last_name]
      ,[position]
      ,[email]
      ,[office]
      ,[start_date]
      ,[age]
      ,[salary]
      ,[seq]
      ,[extn]
      ,[Test]
    FROM inserted

    SELECT id FROM [datatables_demo] where @@ROWCOUNT > 0 AND Id = SCOPE_IDENTITY()


    IF @@ERROR <> 0
        ROLLBACK TRANSACTION
END

Thanks

allan

I don't believe there is I'm afraid since there isn't a primary key and Editor scans the table to attempt to find which column is the pkey.

Allan