Join not working

PatricioFink

My join can READ the data and be rendered OK. I can eve the edit the first column (Stock.Cantidad). But i CAN NOT EDIT the second column, Articulos.Cantidad.
So my question is why i cannt edit that column Articulos.Cantidad

--CONTROLLER:--

namespace EstudioVenta.Controllers.Api
{
    public class StockController : System.Web.Http.ApiController
    {
        [Route("api/stock/stock")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]
        public System.Web.Http.IHttpActionResult Stock()
        {
            var request = HttpContext.Current.Request;

            using (
          var db = new Database("sqlserver", "Server =.; Database = EstudioVenta1; Trusted_Connection = True;"))
            {
                var response = new Editor(db, "Stocks")
                    .Model<JoinStockNameModel>()
                    .LeftJoin("Articulos", "Articulos.ID", "=", "Stocks.Articulo_ID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}


    public class JoinStockNameModel
    {
        public class Stocks
        {   
            public int Cantidad { get; set; }
        }

        public class Articulos
        {
            public string Nombre { get; set; }
        }
    }

--Page Script:

<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: "/api/stock/stock",
            table: "#example",
            fields: [{
                name: "Stocks.Cantidad"
            }, {
                name: "Articulos.Nombre"
            }
            ]
        });

        $('#example').on('click', 'tbody td', function (e) {
            editor.inline(this);
        });

        $('#example').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "/api/stock/stock",
                type: 'POST'
            },
            columns: [
                { data: "Stocks.Cantidad" },
                { data: "Articulos.Nombre" }
            ]
        });

    });

</script>

allan

Hi,

Thanks for your question. When you are trying to edit the joined data you need to include the primary key value of the joined table. Otherwise Editor doesn't know what row in the joined table should be altered.

You would do that by adding the primary key to the model and also adding it to the Editor field list. You can use the hidden field type so the value isn't shown to the end user.

Regards,
Allan

PatricioFink

So you mean that i need the Articulos.ID in my JoinStockNameModel and in my page script in order to modify Articulos.Nombre.

Why in the example is not that way? Why there is not Sites.ID in the model neither in the page script?

Datatables example code:

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

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "users")
                    .Model<JoinModel>()
                    .Field(new Field("users.site")
                        .Options("sites", "id", "name")
                        .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                    )
                    .LeftJoin("sites", "sites.id", "=", "users.site")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}

namespace WebApiExamples.Models
{
    public class JoinModel
    {
        public class users
        {
            public string first_name { get; set; }

            public string last_name { get; set; }

            public string phone { get; set; }

            public int site { get; set; }
        }

        public class sites
        {
            public string name { get; set; }
        }
    }
}

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "/api/join",
        table: "#example",
        fields: [ {
                label: "First name:",
                name: "users.first_name"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select"
            }
        ]
    } );

    +
    // Activate an inline edit on click of a table cell
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            onBlur: 'submit'
        } );
    } );

    $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "/api/join",
            type: 'POST'
        },
        columns: [
            {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
            },
            { data: "users.first_name" },
            { data: "users.last_name" },
            { data: "users.phone" },
            { data: "sites.name", editField: "users.site" }
        ],
        
        buttons: [
            { extend: "remove", editor: editor }
        ]
    } );
} );

allan

My example doesn't contain the ID because the information in the joined table is not editable in that example. Only the data on the users table is being modified there.

Regards,
Allan

PatricioFink

Thanks Allan for the answer. I added the field to the controller, model and page script. But 'm certainly doing something wrong, bcs is not working:

namespace EstudioVenta.Controllers.Api
{
    public class StockController : System.Web.Http.ApiController
    {
        [Route("api/stock/stock")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]
        public System.Web.Http.IHttpActionResult Stock()
        {
            var request = HttpContext.Current.Request;

            using (
          var db = new Database("sqlserver", "Server =.; Database = EstudioVenta1; Trusted_Connection = True;"))
            {
                var response = new Editor(db, "Stocks")
                    .Model<JoinStockNameModel>() //<-------JUST ADDED
                    .Field(new Field("Articulos.ID"))
                    .LeftJoin("Articulos", "Articulos.ID", "=", "Stocks.Articulo_ID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}

public class JoinStockNameModel
{
    public class Stocks
    {
        public int Cantidad { get; set; }

        public int Articulo_ID { get; set; }

    }

    public class Articulos
    {
        public int ID { get; set; }  //<-------JUST ADDED
        public string Nombre { get; set; }
    }
}

<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: "/api/stock/stock",
            table: "#example",
            fields: [{
                name: "Stocks.Cantidad"
            }, {
                name: "Articulos.Nombre"
            }
            ]
        });

        editor.add({  //<-------JUST ADDED
            type: "hidden",
            name: "Articulos.ID",
            default: ""
        });

        $('#example').on('click', 'tbody td', function (e) {
            editor.inline(this);
        });

        $('#example').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "/api/stock/stock",
                type: 'POST'
            },
            columns: [
                { data: "Stocks.Cantidad" },
                { data: "Articulos.Nombre" }
            ]
        });

    });

</script>

allan

the looks like it should actually work. Are you able to give me a link to the page so I can check it out please? On edit, what is the data that is being submitted to the server?

Allan

PatricioFink

I can give you a link ATM, this is not published yet. What do you mean with the data? The request object? Wich property/s do you need?

allan

If you look at the Ajax request in your browser's "Network" panel, it should have a "headers" section that will show the parameters submitted to the server. It would be interesting to know what they are. It would narrow the issue down to client-side or server-side (i.e. its a server-side issue, if the parameters are all being submitted as expected).

Allan

PatricioFink

Header data
{draw: null, data: [,…], recordsTotal: null, recordsFiltered: null, error: null, fieldErrors: [],…}
data
:
[,…]
0
:
{DT_RowId: "row_1016", Stocks: {Cantidad: 24, Articulo_ID: 1016}, Articulos: {Nombre: "Campera Roja"}}
Articulos
:
{Nombre: "Campera Roja"}
Nombre
:
"Campera Roja"
DT_RowId
:
"row_1016"
Stocks
:
{Cantidad: 24, Articulo_ID: 1016}
Articulo_ID
:
1016
Cantidad
:
24
draw
:
null
error
:
null
fieldErrors
:
[]
files
:
{}
id
:
null
meta
:
{}
options
:
{}
recordsFiltered
:
null
recordsTotal
:
null
upload
:
{id: null}
id
:
null

Is that what you expect?

PatricioFink

{draw: null, data: [,…], recordsTotal: null, recordsFiltered: null, error: null, fieldErrors: [],…}
data
:
[,…]
0
:
{DT_RowId: "row_1016", Stocks: {Cantidad: 24, Articulo_ID: 1016}, Articulos: {Nombre: "Campera Roja"}}
Articulos
:
{Nombre: "Campera Roja"}
Nombre
:
"Campera Roja"
DT_RowId
:
"row_1016"
Stocks
:
{Cantidad: 24, Articulo_ID: 1016}
Articulo_ID
:
1016
Cantidad
:
24
draw
:
null
error
:
null
fieldErrors
:
[]
files
:
{}
id
:
null
meta
:
{}
options
:
{}
recordsFiltered
:
null
recordsTotal
:
null
upload
:
{id: null}
id
:
null

allan

I'd expected something a bit like:

action=edit
data[row_5][first_name]=Airi1
data[row_5][last_name]=Satou
data[row_5][position]=Accountant
data[row_5][office]=Tokyo
data[row_5][extn]=5407
data[row_5][start_date]=2008-11-28
data[row_5][salary]=162700

Allan

PatricioFink

Hey, i just deployed the site so you can please take a better look:

Credentials:
User: allan
Password: allan

http://estudioventa.azurewebsites.net/stock

allan

Thanks for the link. If I edit the Cantidad column I can see the following data being submitted to the server:

action:edit
data[row_3][Stocks][Cantidad]:10

i.e. it is only sending the field that has been edited. Editor will do this by default when using inline editing. It can be changed via the form-options object which you can set using formOptions.inline or the inline() method - e.g.:

editor.inline(this, {
  submit: 'all'
} );

You also need to include the Articulos.ID in the submitted fields - use hidden so it can't ever be seen by the end user.

Allan

PatricioFink

Hey Allan;

Now i get to send the ID from the DT call:

action:edit
data[row_2039][Stocks][Cantidad]:11112
data[row_2039][Articulos][Nombre]:2344
data[row_2039][Articulos][ID]:10

But in the DT.Response.error i'm getting this:

"Could not load file or assembly 'AntiXssLibrary, Version=4.3.0.0, Culture=neutral, PublicKeyToken=d127efab8a9c114f' or one of its dependencies. The system cannot find the file specified."

I asume that this assembly "AntiXssLibrary" is used by the DT assembly, right?

allan

Yes - you can download it from here. It will basically encode the HTML to try and add some security against XSS attacks.

Allan

PatricioFink

Thanks, i added the DDL and its OK.

I'm getting this one now: "Cannot update identity column 'ID'." But i did not try to update the ID column, i was idating the "nombre" column

allan

What is the name of your primary key column? You can set that using the option third parameter to the Editor C# constructor. You should not include the primary key in the fields list of the model (you can if you need to display it to the end user, but if you do, you need to disable the ability to set it).

Allan

PatricioFink

Deleting the ID gives me this:
"The multi-part identifier "Articulos.ID" could not be bound."

action:edit
data[row_24][Stocks][Cantidad]:123
data[row_24][Articulos][Nombre]:asd123123

namespace EstudioVenta.Controllers.Api
{

    internal class JoinStockNameModel
    {
        public class Stocks
        {
            public int Cantidad { get; set; }

            //public int Articulo_ID { get; set; }

        }

        public class Articulos
        {
            public string Nombre { get; set; }

            //public int ID { get; set; }
        }
    }

    public class StockController : System.Web.Http.ApiController
    {
        [Route("api/stock/stock")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]
        public System.Web.Http.IHttpActionResult Stock()
        {
            var request = HttpContext.Current.Request;


            using (
                //var db = new Database("sqlserver", "Server=tcp:rpk9w4eg8k.database.windows.net,1433;Database=EstudioVenta1;User ID=tr3shold1!@rpk9w4eg8k;Password=ARRIBENIOS123!;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"))
                var db = new Database("sqlserver", "Server =.; Database = EstudioVenta1; Trusted_Connection = True;"))
            {
                var response = new Editor(db, "Stocks", "Articulos.ID")
                    .Model<JoinStockNameModel>()
                    .LeftJoin("Articulos", "Articulos.ID", "=", "Stocks.Articulo_ID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}

<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 () {

        $("#codigo").focus();

        editor = new $.fn.dataTable.Editor({
            ajax: "/api/stock/stock",
            table: "#example",
            fields: [{
                name: "Stocks.Cantidad"
            }, {
                name: "Articulos.Nombre"
            }
            
            ]
        });

        $('#example').on('click', 'tbody td', function (e) {
            editor.inline(this, {
                submit: 'all'
            });
        });

        //$('#example').on('click', 'tbody td', function (e) {
        //    editor.inline(this);
        //});

        $('#example').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "/api/stock/stock",
                type: 'POST'    
            },
            columns: [
                { data: "Stocks.Cantidad" },
                { data: "Articulos.Nombre" }
            ]
        });

    });

</script>

There is no example like this in the examples package right? where i can see a joined table where its allow to edit data in both tables.

allan

new Editor(db, "Stocks", "Articulos.ID") - you can't use a primary key from a different column on the table being edited. You need to use the column name of the primary key in the Stocks table.

If you want to be able to edit data in the Articulos table you need to also include the primary key for that table in the model (or Fields) and submit it to the server as a hidden field (hidden).

Allan

PatricioFink

I don't know what to do anymore. i tried every posible combination i can imagine.
And i still can not get this working :/

This is what i have now:

<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 () {

        $("#codigo").focus();

        editor = new $.fn.dataTable.Editor({
            ajax: "/api/stock/stock",
            table: "#example",
            fields: [{
                name: "Stocks.Cantidad"
            }, {
                name: "Articulos.Nombre"
            }
            
            ]
        });

        editor.add({
            type: "hidden",
            name: "Articulos.ID",
            default: "0"
        });

        $('#example').on('click', 'tbody td', function (e) {
            editor.inline(this, {
                submit: 'all'
            });
        });

        //$('#example').on('click', 'tbody td', function (e) {
        //    editor.inline(this);
        //});

        $('#example').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "/api/stock/stock",
                type: 'POST'    
            },
            columns: [
                { data: "Stocks.Cantidad" },
                { data: "Articulos.Nombre" }
            ]
        });

    });

</script>

namespace EstudioVenta.Controllers.Api
{

    internal class JoinStockNameModel
    {
        public class Stocks
        {
            public int Cantidad { get; set; }

            public int Id { get; set; }

        }

        public class Articulos
        {
            public string Nombre { get; set; }

            public int ID { get; set; }
        }
    }

    public class StockController : System.Web.Http.ApiController
    {
        [Route("api/stock/stock")]
        [System.Web.Http.HttpGet]
        [System.Web.Http.HttpPost]
        public System.Web.Http.IHttpActionResult Stock()
        {
            var request = HttpContext.Current.Request;


            using (
                     var db = new Database("sqlserver", "Server =.; Database = EstudioVenta1; Trusted_Connection = True;"))
            {
                var response = new Editor(db, "Stocks", "Stocks.ID")
                    .Model<JoinStockNameModel>()
                         //.Field(new Field("Articulos.ID"))
                    .LeftJoin("Articulos", "Articulos.ID", "=", "Stocks.Articulo_ID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}

Database Diagram:

http://s31.postimg.org/9nnwxcv7f/Capture.jpg

This is what i get...

action:edit
data[row_2058][Stocks][Cantidad]:123
data[row_2058][Articulos][Nombre]:aadghjgj
data[row_2058][Articulos][ID]:5

C# DT object Error: "Cannot update identity column 'ID'."

Please get me the most straight answer and tell exactly what i should be doing, bcs i can figure this out...

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Hi,

Thanks for your post. Frustratingly it looks like SQL Server doesn't allow the identify column to be updated - MSDN thread.

Could you try adding:

new Field('Articulos.ID').Set( false )

to your Editor initialisation to tell it to not write that field please. I think it should still be able to read the value for the Where condition in the update allowing the above to work with SQL Server.

Allan

PatricioFink

OMG this is finally working... thanks Allan :D :D

allan

Great - thanks for letting me know. Good to hear we finally got it sorted out!

Allan