Foreign Key constraint error on insert

airmaster

I have a foreign key in a table that I am trying to insert into that registers a error.

Source of information
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblWinLoss_tblCompany". The conflict occurred in database "MyDatabase", table "dbo.tblCompany", column 'ID'. The statement has been terminated.

I am not updating more than one table. I want to update just tblWinLoss.BuilderID, and not touch tblCompany. tblWinLoss.BuilderID is nullable. I have, in my join table, set it as a int? data type.

I have also specified in my controller

.Field(new Field("Builders.ID").Set(false))

Where Builders is the alias for tblCompany, since I use multiple companies.

This insert doesn't seem to work unless I specify a value for tblWinLoss.BuilderID.

allan

Hi,

Can you show me your full controller code please, and also the models if you are using any?

Finally, I'd also need to see the Javascript initialisation for Editor (sorry - lots of info I know, but it will be configuration specific).

Allan

airmaster

Controller

        public ActionResult Join()
        {
            connectionString = "xxx";
            using (var db = new DataTables.Database("sqlserver", connectionString))
            {
                var response = new Editor(db, "tblWinLoss", "ID")
                    .Model<JoinWinLoss>("tblWinLoss")
                    .Model<JoinCompany>("Builders")
                    .Field(new Field("tblWinLoss.ID").Set(false))
                    .Field(new Field("Builders.ID").Set(false))
                    .Field(new Field("tblWinLoss.BuilderID")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                    )
                    .LeftJoin("tblCompany as Builders", "Builders.ID", "=", "tblWinLoss.BuilderID")
                    .Debug(true)
                    .Process(request)
                    .Data();



                return Json(response, JsonRequestBehavior.AllowGet);
            }
        }

View:

<script type="text/javascript" language="javascript" class="init">
            var editor; // use a global for the submit and return data rendering in the examples
            $(document).ready(function () {
                editor = new $.fn.dataTable.Editor({
                    ajax: "/winloss/join",
                    table: "#winloss",
                    fields: [{
                        label: "ID",
                        name: "tblWinLoss.ID",
                        type: "display"
                    }, {
                        label: "Title",
                        name: "tblWinLoss.Title",
                        type: "text"
                    }, {
                        label: "Builder",
                        name: "tblWinLoss.BuilderID",
                        type: "select",
                        placeholder: "-- None/Select --",
                        placeholderDisabled: false
                    }, {
                        label: "tblWinLoss.Bid",
                        name: "tblWinLoss.Bid",
                        type: "hidden"
                    }
                    ]
                });
                var table = $('#winloss').DataTable({
                    dom: '<"wrapper"Bflipt>',
                    pageLength: 10,
                    lengthMenu: [10, 15, 20, 50, 75, 100],
                    ajax: {
                        url: "/winloss/joinx",
                        type: 'POST'
                    },
                    columns: [
                        { data: "tblWinLoss.ID"},
                        { data: "tblWinLoss.Title", editField: "tblWinLoss.Title" },
                        { data: "Builders.CompanyName", editField: "tblWinLoss.BuilderID" }
                    ],
                    order: [1, 'asc'],
                    select: 'single',
                    buttons: [
                        { extend: "create", editor: editor },
                        {extend: "edit", editor: editor},
                        { extend: "remove", editor: editor}                        
                    ]

                });
                table.columns().every(function () {
                    var that = this;

                    $('input', this.header()).on('keyup change clear', function () {
                        if (that.search() !== this.value) {
                            that
                                .search(this.value)
                                .draw();
                        }
                    });
                });
            });

            $(document).ready(function () {
                editor.field('tblWinLoss.Bid').set(false);
            });

    <table id="winloss" class="display responsive " cellspacing="0">
        <thead>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Builder</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Builder</th>
            </tr>
        </tfoot>
    </table>

</body>

</html>

Model

    public class JoinWinLoss
    {
        public int ID { get; set; }
        public string Title { get; set; }
        public int? BuilderID{ get; set; }
        public bool Bid{ get; set; }

    }


    public class JoinCompany
    {
        public int ID { get; set; }
        public string CompanyName { get; set; }
    }

allan

Thank you. For tblWinLoss.ID in the Javascript try:

{
  label: "ID",
  name: "tblWinLoss.ID",
  type: "display",
  submit: false
}

If that doesn't fix it (I think it should) could you show me the full JSON response from the server when you do an edit or insert please?

Thanks,
Allan

airmaster

That didn't work. I don't really have a problem with the Primary key, its with the BuilderID. I think I know what the problem is, and here is my debug query.

DECLARE @T TABLE ( insert_id int )
INSERT INTO  [tblWinLoss]  ( [BuilderID], [Title] )
OUTPUT INSERTED.ID as insert_id   
INTO @T VALUES (  @BuilderID,  @Title );
SELECT insert_id FROM @T

So, if you look at the debug value of tblWinLoss.BuilderID, its "", and when you run the insert

DECLARE @T TABLE ( insert_id int )
INSERT INTO  [tblWinLoss]  ( [BuilderID], [Title] )
OUTPUT INSERTED.ID as insert_id   
INTO @T VALUES (  '',  @Title );
SELECT insert_id FROM @T

You get the error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblWinLoss_tblCompany". The conflict occurred in database "CompetitiveSQL", table "dbo.tblCompany", column 'ID'. The statement has been terminated.

But, if you actually had a NULL value, the insert works like it should.

DECLARE @T TABLE ( insert_id int )
INSERT INTO  [tblWinLoss]  ( [BuilderID], [Title] )
OUTPUT INSERTED.ID as insert_id   
INTO @T VALUES (  null,  @Title  );
SELECT insert_id FROM @T

So, how do we get it to put a null value instead of a empty string if no value is selected? It is a nullable integer, as specified in the model.

allan

That looks similar, but not quite, to the SQL that the Editor .NET libraries create. Where are you getting those statements from - is it the SQL Server query log?

Can you show me the JSON returned from the server after a create submit as that should include the SQL Editor generates (since you have .Debug(true))?

Thanks,
Allan

airmaster

That is the SQL from debug true. Also the error returned is above.

airmaster

Just to clarify, the first one is the SQL from the debug(true) statement, which I got from the watch inside VS. The others are me showing what the value is from the binding @BuilderID = '' and what actually works, which is a NULL value.

I am not sure how to grab the JSON as its returned to the browser. I didn't see much in the network tab of the browser.

airmaster

Is this what you are looking for?

Persist Logs
Disable cache
    
    
    
    
    
    
    
    
200
    POST    
localhost:64039 joinx   xhr json    643 B   643 B   
7087 ms
One request
643 B / 643 B transferred
Finish: 7.11 s
    
JSON    
draw    null
data    []
recordsTotal    null
recordsFiltered null
error   The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblWinLoss_tblCompany". The conflict occurred in database "CompetitiveSQL", table "dbo.tblCompany", column 'ID'. The statement has been terminated.
fieldErrors []
id  null
meta    {}
options {}
files   {}
upload  {…}
id  null
debug   […]
0   {…}
Query   INSERT INTO [tblWinLoss] ( [BuilderID], [Title] ) VALUES ( @BuilderID, @Title )
Bindings    […]
0   {…}
Name    @BuilderID
Value   
Type    null
1   {…}
Name    @Title
Value   This is a test
Type    null
cancelled   []
Response payload    
1

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_tblWinLoss_tblCompany\". The conflict occurred in database \"CompetitiveSQL\", table \"dbo.tblCompany\", column \u0027ID\u0027.\r\nThe statement has been terminated.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debug":[{"Query":"INSERT INTO  [tblWinLoss]  ( [BuilderID], [Title] ) VALUES (  @BuilderID,  @Title )","Bindings":[{"Name":"@BuilderID","Value":"","Type":null},{"Name":"@Title","Value":"This is a test","Type":null}]}],"cancelled":[]}

airmaster

I notes the type is set to NULL, not sure what that is, but the value not being NULL is the problem.

allan

That was perfect - thank you. Change:

Field(new Field("tblWinLoss.BuilderID")

to be (i.e. add the setFormatter):

Field(new Field("tblWinLoss.BuilderID")
  .SetFormatter(Format.IfEmpty(null))

The reason that is required is that Editor submits values as plain http parameters, and there is no typing information. So there is no way to send null (we could send 'null', but that might be a string value!). We can use a set formatter on specific fields where we know an empty value is not going to be written into the db such as here.

Documentation for that is available here.

Regards,
Allan

airmaster

Perfect! So much to learn.