Getting the database generated ID when adding row

[MarcParticipant]

[Version info :
– jquery-1.11.0.min.js
– jqwidgets-ver3.2.1
– MVC 3 .Net 4 with Visual Studio 2010 on XP SP3
– SQL Server 2008 R2]

Hi,

First, sorry if this topic has been asked. I searched the Grid forum, but didn’t find any post about it.

I’m new to jqWidgets, and have started experiencing with the Grid. I would like to use a Grid to CRUD data from/to an SQL Database.
The various documentation and available samples generally insert a record with RecordID = rowid.
More often, the ID is DB generated, in order to guarantee uniqueness in multi-users environments.

I thought that passing a “null” id to the grid would do the trick, but the addrow function seems to insist to convert the null to the rowid.

I also saw in the addrow documentation that the functionality is quoted, meaning that it has been anticipated, but I can’t figure out how.

    addrow: function (rowid, rowdata, position, commit) {
        // synchronize with the server - send insert command
        // call commit with parameter true if the synchronization with the server was successful. 
        // and with parameter false if the synchronization has failed.
        // you can pass additional argument to the commit callback which represents the new ID if it is generated from a Database. Example: commit(true, idInDB) where "idInDB" is the row's ID in the Database.
        commit(true);

Here is the code. The purpose is to create a row in an “Activity” table, by calling an HTTP Post C# function (Activities/Add) url.

• First, a simple generaterow function.

    var generaterow = function (id) {
      var row = {};
      row["ActivityID"] = id;
      row["Name"] = "Test record"
      row["Comments"] = "Generated by Javascript"
      row["Keywords"] = "JS TEST"
      return row;
    }

• Then the addrow function in the source property :

                addrow: function (rowid, rowdata, position, commit) {
                  // synchronize with the server - send insert command
                  $.ajax({
                    cache: false,
                    dataType: 'json',
                    url: 'Activities/Add',
                    data: rowdata,
                    type: "POST",
                    success: function (data, status, xhr) {
                      // insert command is executed.
                      commit(true);
                    },

• And the Create button event :

    // create new row.
    $("#addrowbutton").bind('click', function () {
      //    var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
      var datarow = generaterow(null);
      $("#jqxgrid").jqxGrid('addrow', null, datarow);
    });

Unexpectedly, this :

• Creates a row with ActivityID = the row id.
• Fails to insert the record in the database (but this is likely due to SQL server settings, that forbid non-null ID’s).

My questions are :
– How to insert a record with null ID, so that it gets generated by the database ?
– How to get back the ID generated by the database, to display it in the grid ?

Thanks and regards

[Peter StoevKeymaster]

Hi Marc,

There’s an option to call “commit” with 2 parameters, true and the new ROW ID. This means that when you make your Ajax call in the “addrow” function, your DB should generate the new ROW ID and should return it to the client. Then in the Ajax’s success callback, you will call commit(true, newID);

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[MarcParticipant]

Hi Peter,

Thanks for your timely answer.

I first fixed the issue with passing the “null” ID. In C#, more precisely the Entity Framework, which is .Net Entity-Relational-Mapping layer, the ID was defined as int, which in C# does not accept null values. The correct type had to be int? (not required int), which basically means ‘int or null’. Now, the record is correctly inserted in the database with null ID, which then lets the database autogenerate the ID. The new ID is sent back to C# in Activity.ActivityID. This works fine. But I’m not yet quite clear about how to implement your answer.

Here is my C# code, directly derived from a sample in jqwidget site :

    [HttpPost]
    public ActionResult Add(Activity Activity)
    {
      if (ModelState.IsValid)
      {
        db.Activities.Add(Activity);
        db.SaveChanges();

        return Json("true");
      }
      return Json("false");
    }

So, I still see three questions :

• How do I pass back the ID to the JS client-side ? Neither return Json(Activity.ActivityID); nor return Json("true",Activity.ActivityID) work.
  The first one does not set newId in the commmit(true, newId) callback.
  The second one generates an error server-side in C# : invalid parameter.
  But maybe this is a question to the MS guys ?
• Assuming I eventually correctly set back the commit(true, newId) callback, how do I use it to fix the ActivityId in the grid ? Would it be something like : commit(true, newId){$("#jqxgrid").jqxGrid('setcellvalue', <rowid>, 'ActivityID', newId);}
• And finally, where do I get the rowid in the above code ? Is it somewhere in the data parameter of the success: function ? Otherwise where else ?

Sorry if these questions from a newbie are obvious, they are not yet for me 🙂

Thank you and best regards.

[Peter StoevKeymaster]

Hi Marc,

“commit” is a callback function which confirms or denies the changes. You don’t have to call “setcellvalue” after “commit”. This sample will show you how to return data from the Server to the Client: http://www.jqwidgets.com/jquery-widgets-documentation/documentation/asp.net-integration/asp.net-grid-server-side-paging-sorting-filtering-mvc3.htm

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[MarcParticipant]

Hi Peter,

Thank you again.

I had your link in my list of readings, I will check it in details.

From a first reading, it also uses, but with different parameters, the Json() MVC .Net function, in the C# controller, to return json data to the client. What I seem to be missing now, actually, is a proper documentation of this function. Despite extensive googgle searches, I could not find any so far. But I guess I should now post a question in MS forums, not here.

Thanks again for your help, I’ll check your link, and see if I can sort it out.

Best regards.

[MarcParticipant]

Hi,

Here is the detailed procedure, for those who might be interested :

1. Server-side, in the CSharp controller, the last line in the HTTP POST Add method should be :

return Json(Activity.ActivityID, JsonRequestBehavior.AllowGet);

2. Client-side, in the grid ‘source’ code, addrow property, the second parameter of the commit callback function has to be named ‘data’. Data is an object that matches whatever you sent from the server, that is in the first parameter of the Json() function above. So the commit function call is:

commit(true, data);

Everything works fine then.

Thanks Peter for your help.

Best regards.

[Author]

Posts