Focus the first input in any bootstrap modal

If you’re accepting user input in a bootstrap modal, or you just want to be more friendly to your keyboard-based users, here’s how to focus the first input element on any bootstrap modal in a global context.

$(document).on('shown.bs.modal', function (e) {
     $(e.target).find(":input").not(".close").filter(":visible:first").focus();
});

A few things are happening here:

  • A delegate event listener is placed on the document (preferably in your jQuery document ready function)
  • The event shown.bs.modal is triggered when a bootstrap modal is opened
  • We find the modal element triggering this function with e.target
  • We exclude the (X) close button from being selected
  • We focus the first visible input element (text, button, etc.) 

The nice thing about doing it this way is it applies to all popup modals across the board. It’s an easy one-size-fits-all solution for large applications!

Drag n Drop Queue Ordering with a Kendo UI Grid

There are a bunch of examples for drag and drop Kendo grids floating around the web, but this is the only reordering solution that has worked for me with an in-cell editable grid. It features an automatically updating priority queue column with the row index number.

First let’s look at the main reordering functionality, and then complete the user experience with some extra bits for updating the queue.

// Enable drag n drop reordering on this grid - can be done on document.ready
$("#my_grid").data("kendoGrid").table.kendoSortable({
    filter: ">tbody >tr",
    hint: $.noop,
    cursor: "move",
    ignore: "TD, input",
    placeholder: function (element) {
        return element.clone().addClass("k-state-hover").css("opacity", 0.65);
    },
    container: "#my_grid tbody",
    change: grid_reorder
});


// Reordering event triggered by user releasing drag n drop element
function grid_reorder(e) {

    // Get data from drag n drop html element
    var grid = $("#my_grid").data("kendoGrid");
    var dataItem = grid.dataSource.getByUid(e.item.data("uid"));

    // Copy data into new grid row location
    grid.dataSource.remove(dataItem);
    grid.dataSource.insert(e.newIndex, dataItem);

    // Get rid of delete request caused by dataSource.remove
    for (var i = 0; i < grid.dataSource._destroyed.length; i++) {
        if (grid.dataSource._destroyed[i].uid == dataItem.uid) {
            grid.dataSource._destroyed.splice(i, 1);
        }
    }

    // Update ordering of all grid rows
    for (var i = 0; i < grid.dataSource.data().length; i++) {
        grid.dataSource.data()[i].set("PriorityOrder", i + 1);
    }
}

Setting up drag n drop on the grid is accomplished above using kendoSortable. Beyond that, Kendo leaves reordering the associated grid data up to the user from what I could gather. My solution removes and inserts the row in its new location, and then removes the subsequent delete request from the grid’s private _destroyed array to avoid false deletes. I then update the PriorityOrder column for all rows, as this is the queue that is shown to the user.

Next we need to take care of updating the priority queue when the user inserts a new row. The grid’s Edit event is triggered by Kendo when the add button is clicked, giving focus to the first cell and opening it for editing.

function grid_editcell(e) {
    // Do not allow user to set priority, but keep it editable so it can be updated by drag n drop
    if (e.container.find("input[name=PriorityOrder]").length > 0) {
        e.sender.closeCell(e.container);

        // If user is adding a new row update entire queue order
        if (e.model.isNew()) {
            for (var i = 1; i < e.sender.dataSource.data().length; i++) {
                e.sender.dataSource.data()[i].set("PriorityOrder", i + 1);
            }
        }
    }
}

Don’t forget about the user removing a row…

function grid_removerow(e) {
    // Remove row then update entire queue order
    this.removeRow($(e.target).closest("tr"));
    var grid = $("#my_grid").data("kendoGrid");

    for (var i = 0; i < grid.dataSource.data().length; i++) {
        grid.dataSource.data()[i].set("PriorityOrder", i + 1);
    }
}

Now let’s take a look at a (simplified) grid definition in Razor code, as I’m using ASP MVC wrappers to generate the grid. If you’re not using wrappers you should be able to make out the important bits.

@(Html.Kendo().Grid<MyProj.ExampleClass>()
    .Name("my_grid")
    .Columns(columns =>
    {
        columns.Bound(p => p.PriorityOrder).Width("75px")
                .ClientTemplate("<span class='fa fa-arrows drag-handle'></span> &nbsp; #=PriorityOrder#");
        columns.Bound(p => p.Amount).Format("{0:C2}");
        columns.Bound(p => p.UpdateDate).Format("{0:MM/dd/yyyy}");
        columns.Command(command => command.Custom("Delete").Click("grid_removerow"))
                .Title("Action").Width("60px");
    })
    .Editable(editable => editable.Enabled(true).Mode(GridEditMode.InCell))
    .Events(events => events
        .Edit("grid_editcell")
    )
    .DataSource(dataSource => dataSource.Ajax().Batch(true)
        .Model(model =>
        {
            model.Id(key => key.ExampleKey);
            model.Field(f => f.PriorityOrder).DefaultValue(1);
            model.Field(f => f.UpdateDate).Editable(false);
        })
        .Create(create => create.Action("Grid_Create", "Home"))
        .Read(read => read.Action("Grid_Read", "Home"))
        .Update(update => update.Action("Grid_Update", "Home"))
        .Destroy(destroy => destroy.Action("Grid_Delete", "Home"))
    )
)

I have Font Awesome displaying the classic Windows “move” arrow icon as a drag handle in the PriorityOrder queue column as a client template.

When a grid row is inserted, the grid_editcell event is triggered, which causes the queue to refresh. I found that it was necessary to keep the PriorityOrder column editable, so the values could be visually refreshed. To prevent the user from manually changing the queue, any cell in the PriorityOrder column is closed immediately upon click.

Lastly each row has a custom delete button, which triggers the queue refresh when the row is removed.

Kendo grid drag 'n' drop reordering
Kendo Grid with Queue
Kendo Drag 'n' Drop Reordering
Kendo Drag ‘n’ Drop Reordering

Switching to MySQL from MongoDB in Node.js

There are a lot of resources on the web on how to set up a Node.js / Express server with MongoDB for basic CRUD operations. I usually deal with relational data however, and prefer something like MySQL. So I switched this Node Cellar sample app from Mongo to MySQL using the express-myconnection NPM package.

Install and require the mysql and express-myconnection packages in your project using NPM. The MySQL connection can be set up in your Node server along with Express. This assumes you already have an Express server set up.

var app = express();

var connection  = require('express-myconnection'),
    mysql = require('mysql');

app.use(

    connection(mysql,{
        host     : 'example.com',
        user     : 'youruser',
        password : 'YOURPASSWORD',
        port	 : 3306,
        database : 'yourdb'
    },'request')

);

Below are the MySQL CRUD operations, with the MongoDB equivalent commented out for comparison. Following along with the Node Cellar sample app, this replaces most of wines.js. Note the addition of the next parameter to return errors to the Node server.

exports.findById = function(req, res, next) {
    var id = req.params.id;
    console.log('Retrieving wine: ' + id);
    /*
    db.collection('wines', function(err, collection) {
        collection.findOne({'_id':new BSON.ObjectID(id)}, function(err, item) {
            res.send(item);
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("SELECT * FROM Wines WHERE _id = ?", id, function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	       } else {
	       		console.log(rows[0]);
	       }
	        
		  res.send(rows[0]);
        });
     });
};

exports.findAll = function(req, res, next) {
	console.log('Retrieving all wines');
	/*
    db.collection('wines', function(err, collection) {
        collection.find().toArray(function(err, items) {
            res.send(items);
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("SELECT * FROM Wines", function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	         }
			 res.send(rows);
        });
     });
};

exports.addWine = function(req, res, next) {
    var wine = req.body;
    console.log('Adding wine: ' + JSON.stringify(wine));
    /*
    delete wine._id;  
    db.collection('wines', function(err, collection) {
        collection.insert(wine, {safe:true}, function(err, result) {
            if (err) {
                res.send({'error':'An error has occurred'});
            } else {
                console.log('Success: ' + JSON.stringify(result[0]));
                res.send(result[0]);
            }
        });
    });*/
     
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("INSERT INTO Wines SET ? ", wine, function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	        } else {
	       		console.log("Data inserted with Id: " + rows.insertId);
	        }
          wine._id = rows.insertId;
		  res.send(wine);
        });
     });
}

exports.updateWine = function(req, res, next) {
    var id = req.params.id;
    var wine = req.body;
    delete wine._id;
    console.log('Updating wine: ' + id);
    console.log(JSON.stringify(wine));
    /*
    db.collection('wines', function(err, collection) {
        collection.update({'_id':new BSON.ObjectID(id)}, wine, {safe:true}, function(err, result) {
            if (err) {
                console.log('Error updating wine: ' + err);
                res.send({'error':'An error has occurred'});
            } else {
                console.log('' + result + ' document(s) updated');
                res.send(wine);
            }
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("UPDATE Wines SET ? WHERE _id = ?", [wine,id], function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	        } else {
	       		console.log("Data updated, rows affected: " + rows.affectedRows);
	        }
		  res.send(wine);
        });
     });
}

exports.deleteWine = function(req, res, next) {
    var id = req.params.id;
    console.log('Deleting wine: ' + id);
    /*
    db.collection('wines', function(err, collection) {
        collection.remove({'_id':new BSON.ObjectID(id)}, {safe:true}, function(err, result) {
            if (err) {
                res.send({'error':'An error has occurred - ' + err});
            } else {
                console.log('' + result + ' document(s) deleted');
                res.send(req.body);
            }
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("DELETE FROM Wines WHERE _id = ?", id, function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	        } else {
	       		console.log("Data deleted, rows affected: " + rows.affectedRows);
	        }
		  res.send(req.body);
        });
     });
}

Easy Reset Button for Form Values with Kendo UI and jQuery

Here is a short and sweet all-JavaScript way to create a reset button on a form with a mixture of Kendo UI controls and HTML controls.

var form_cancel_values = [];

// On form load - Save current values of form controls into an array
function readCancelValues() {
    form_cancel_values = $('#UserForm').serializeArray();
}

// User clicked cancel - Reset all form fields to value in array
function recallCancelValues() {
    $("#UserForm [type='checkbox']").prop("checked", false);

    $.each(form_cancel_values, function () {
        if (kendo.widgetInstance($('#' + this.name))) {
            kendo.widgetInstance($('#' + this.name)).value(this.value);
        } else {
            $('#' + this.name).val(this.value).prop("checked", true);
        }
        $('#' + this.name).blur(); // clear Kendo validator tooltip
    });
}

When recalling the cancellation values, first all checkboxes in the form are cleared. Any checked checkboxes will be re-checked later. This is due to the default HTML behavior of checkboxes- they are not defined unless checked.

Using kendo.widgetInstance here allows us to set the value of the Kendo control, regardless of the type of control. That way we don’t concern ourselves with whether it is a DatePicker, ComboBox, etc.

The final touch is to trigger the blur (lose focus) event on each control, so that any “This field is required” Kendo validator tooltips are hidden.

Getting Foreign Keys both ways in SQL Server

I’m posting this one here for myself. Whenever I get lost finding foreign keys in a SQL Server database I’ll come back to this for a neat and tidy list of all foreign key constraints on a table.

This bit of SQL will go both ways for any given table. First it’ll get you a list of columns referencing other tables, then secondly a list of other tables referencing your table. Just supply it with a table name and you’re good to go.

DECLARE @TABLE_NAME VARCHAR(30) = 'Customer'

-- Foreign keys referencing other tables
SELECT	object_name(f.parent_object_id) ParentTable,
		col_name(fc.parent_object_id,fc.parent_column_id) ColumnName,
		object_name(f.referenced_object_id) RefTable,
		f.name ConstraintName
FROM	sys.foreign_keys f
JOIN	sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
WHERE	f.parent_object_id = object_id(@TABLE_NAME)


-- Foreign keys refrencing this as a parent table
SELECT	object_name(f.parent_object_id) ParentTable,
		col_name(fc.parent_object_id,fc.parent_column_id) ColumnName,
		object_name (f.referenced_object_id) RefTable,
		f.name ConstraintName
FROM	sys.foreign_keys AS f
JOIN	sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
JOIN	sys.tables t ON t.object_id = fc.referenced_object_id
WHERE	object_name (f.referenced_object_id) = @TABLE_NAME

I put this together based off of this StackOverflow answer.

Also check out the command sp_help 'TableName' for a bunch of useful information on a table, including foreign keys.

Multi-page PDF Printing for Kendo UI Grids

The examples on Telerik’s site are a little sparse for PDF printing. Sure, it’s easy to print a Kendo grid to PDF, but let’s say you want to include other sections of the web page, or add a header, without creating an entirely separate “printable view”.

It took a lot of experimenting to arrive at the simple solution below. What all does it do?

  • Prints any section of the HTML document, including grids
  • Automatically determines page breaks for large grids
  • Scales the PDF contents to look good on a standard piece of paper
  • Displays a header and footer on each page with page numbers

    All that power is packed into this JavaScript function:

        function page_grid_render_pdf() {
            kendo.drawing.drawDOM($("#printable_area"),
                {
                    paperSize: [1100, 1430], // Scaling in pt - 8.5"x11" page ratio
                    landscape: true,
                    margin: { left: "0mm", top: "15mm", right: "0mm", bottom: "10mm" },
                    template: $("#pdf-page-template").html()
                }).then(function (documentgroup) {
    
                    // Generate PDF file
                    kendo.drawing.pdf.saveAs(documentgroup, "KendoPDF.pdf");
                });
        }
    

    You’ll also need to use the following section as the header and footer template.

    <script type="x/kendo-template" id="pdf-page-template">
        <div class="pdf-page-template">
            <div class="pdf-page-header">
                <span style="font-size: 140%;">
                    PDF Printing with Kendo UI
                </span>
                <div style="float: right">Page #:pageNum# of #:totalPages#</div>
            </div>
            <div class="pdf-page-footer">
                <div style="float: right">Page #:pageNum# of #:totalPages#</div>
            </div>
        </div>
    </script>
    

    When you call the JavaScript printing function, it will render everything in <div id="printable_area"> My printable area contains user information and a grid with user data.

    The paper size is set very large to take advantage of your browser or PDF viewer’s built-in fit to page functionality when printing. When I set paper size to the standard “Letter” size, my Kendo grid was enormous, and only a few rows fit on each page. Using a large paper size prints it out as it appears on your screen.

    I set the side margins to zero, while leaving space for the header and footer, since again your browser or PDF viewer will add its own margins.

    You may notice I am not using any of the page-break options mentioned in the Kendo PDF documentation. I tried them all and this automatic version seems to work the best with large grids, and it doesn’t require any manual page breaks added to grid rows.

    Another tip is to include the Pako compression library that comes with Kendo UI: pako_deflate.min.js Kendo will automatically use it when included, and it reduced my PDF file size ten times over!

  • Making an image uploader with Kendo UI and MVC

    The Telerik documentation for the Kendo MVC file uploader only covers the basics for this control. Here is a more complete example on how you might set up an image uploader, with error handling, and saving the image data.

    The Razor syntax for my upload control using MVC server wrappers looks like this:

    @(Html.Kendo().Upload()
        .Name("ImageUpload")
        .Messages((m => m.Select("Select image file...")))
        .Multiple(false)
        .Events(events =>
        {
            events.Error("image_file_failure");
            events.Select("image_file_select");
            events.Success("image_display_refresh");
            events.Upload("image_file_upload");
        })
        .Async(a => a
            .Save("ImageSave", "Home")
            .AutoUpload(true)
        )
        .HtmlAttributes(new { accept = "image/*" })
    )
    

    The HtmlAttribute above sets the default type of file the user can select from their system. The user can change it to “All Types” though, so we’ll need to validate their choice is a file type we can accept.

    The JavaScript events below add extra data parameters to the upload (image_file_upload), validate the file type (image_file_select), and handle errors from the server (image_file_failure).

        function image_file_upload(e) {
            e.data = { UserId: @Model.UserId };
        }
    
        function image_file_select(e) {
            var acceptedFiles = [".jpg", ".jpeg", ".png", ".gif", ".bmp"];
            var isAcceptedImageFormat = ($.inArray(e.files[0].extension, acceptedFiles)) != -1;
    
            if (!isAcceptedImageFormat) {
                e.preventDefault();
                errorMessage = "Image file must be one of the following types: JPG, JPEG, PNG, GIF, BMP";
                alert(errorMessage);
            }
        }
    
        function image_file_failure(e) {
            var _error;
            if (e.XMLHttpRequest.status == 500) {
                _error = "Server Error: " + $(e.XMLHttpRequest.responseText).filter('title').text();
            } else {
                _error = e.XMLHttpRequest.responseText;
            }
    
            errorMessage = "There was an error uploading the image file. " + _error;
            alert(errorMessage);
        }
    

    In my case I would receive a 500 server error when the file size exceeded several megabytes, and the server would return an entire error page in XMLHttpRequest.responseText. I’m reporting the title of that error page to the user with jQuery.

    The MVC controller that accepts the uploaded file needs a HttpPostedFileBase parameter with the same name as your Kendo Upload control, in this case ImageUpload.

            public ActionResult ImageSave(HttpPostedFileBase ImageUpload, int UserId)
            {
                var _Model = new ImageModel();
                return Content(_Model.Save(ImageUpload, UserId));
            }
    

    The Kendo Upload control is expecting an empty string to be returned to it upon success. Any other text means an error has occurred.

    Now the image data can be converted into the image format of your choice with the System.Drawing.Imaging library, and copied into a byte stream for inserting into your database. Here is the Save method that does that in my model.

            public string Save(HttpPostedFileBase ImageUpload, int UserId)
            {
                var _Result = ""; // Kendo uploader expects empty string on success
    
                if (ImageUpload != null)
                {
                    try
                    {
                        // Read image file
                        Image _Image = Image.FromStream(ImageUpload.InputStream);
    
                        // Encode image data as Gif image byte array
                        MemoryStream _ImageMemoryStream = new MemoryStream();
                        _Image.Save(_ImageMemoryStream, ImageFormat.Gif);
                        byte[] _ImageBytes = _ImageMemoryStream.ToArray();
    
                        // Insert byte array data in database
                        var _Success = SavePhoto(UserId, _ImageBytes);
    
                        if (!_Success)
                        {
                            _Result = "Error saving image to database";
                        }
                    }
                    catch (Exception ex)
                    {
                        _Result = "Error reading image data: " + ex.Message;
                    }
                }
                else
                {
                    _Result = "Upload Error: Empty file data";
                }
    
                return _Result;
            }
    

    Get ID when a custom command is clicked in Kendo grids

    Quick code tip for getting the row data from a custom command button in Kendo UI grids. This is useful for intercepting a delete event or any custom actions you have set up on a Kendo grid.

        function grid_deleterow(e) {
            var row = $(e.currentTarget).closest("tr");
            var data = $("#grid").data("kendoGrid").dataItem(row);
            var id = data.EntityId;
        }
    

    In the JavaScript function above, id will contain the value of the EntityId column for the row of the custom button clicked. This is great because you don’t have to make your grid selectable for this to work.

    Here is how the event would be triggered when using the Kendo MVC wrappers:

            @(Html.Kendo().Grid<EntityModel>()
                 .Name("test_grid")
                 .Columns(columns =>
                 {
                     columns.Bound(p => p.Field1);
                     columns.Command(command => command.Custom("Delete").Click("grid_deleterow"));
                 })
               )
    

    Displaying inactive foreign keys in Kendo UI grids with In-Cell editing

    Foreign key columns in Kendo UI are pretty great. With in-grid editing, you can use them to display a choice of categories, people, and so on. At some point you may need to mark a category as inactive or disabled, so that the user can no longer select that foreign key as a valid choice. So how do you display old deactivated foreign keys in your Kendo grid for record keeping purposes?

    By selecting the foreign key text value into our grid as a hidden column, we can use that to resolve any deactivated foreign keys. Here is the JavaScript function that will display the text equivalent of the foreign key, and use the hidden column as a backup source for inactive foreign keys.

        var collection;
        function getforeignkeytext(rowData) {
    
            if (!collection) {
                var grid = $("#grid").data("kendoGrid");
                var foreignKeys = grid.options.columns[2].values; // Set the FK column index
                collection = {};
                for (var i in foreignKeys) {
                    collection[foreignKeys[i].value] = foreignKeys[i].text;
                }
            }
    
            var result = collection[rowData.CategoryId];
            if (result == undefined) {
                result = rowData.CategoryName;
            }
            return result;
        }
    

    For our grid to use this, use a client template for the foreign key column set to use the function above. I am using the MVC server wrappers in this example.

    columns.ForeignKey(p => p.CategoryId, Model.CategoryDropdown, "Value", "Text")
           .ClientTemplate("#=getforeignkeytext(data)#");
    

    Why go through all that trouble when something simple like .ClientTemplate("#=CategoryName#") would display the hidden column value? That solution won’t correctly display an edited value, before the user saves the grid when using GridEditMode.InCell.

    So what about when editing the cell? It would be super nice if the dropdown also magically contained the inactive foreign key as a choice only for that cell. That would really complete the user experience. With a second JavaScript function we can take care of that.

        function editforeignkey(e) {
            var valueToAddToDropdown = e.model.CategoryId;
            var textToAddToDropdown = e.model.CategoryName;
    
            var dropdown = e.container.find('[data-role=combobox]').data();
    
            if (typeof dropdown != "undefined") {
                var valueExists = false;
                for (var i = 0; i < dropdown.kendoComboBox.dataSource._data.length; i++) {
                    if (dropdown.kendoComboBox.dataSource._data[i].Value == valueToAddToDropdown) {
                        valueExists = true;
                    }
                }
                if (!valueExists) {
                    dropdown.kendoComboBox.dataSource.add({ Text: textToAddToDropdown, Value: valueToAddToDropdown });
                }
            }
        }
    

    The function above is triggered on the Kendo grid’s edit event. It attempts to find the foreign key value in the cell’s dropdown, and adds it if necessary. The value will be automatically selected.

    There does appear to be one limitation. If the user changes an inactive foreign key value to an active one, and the cell loses focus, then the inactive foreign key can no longer be reselected, unless all grid changes are cancelled.

    With this technique you can provide users with a cohesive experience for Kendo grids containing historical foreign key data.

    Sorting Kendo foreign key columns by text in MVC

    The Kendo UI MVC wrappers make it very easy to have a dropdown in a grid column as a foreign key. You may notice that the column sorts by the id though, and not the text value that would make sense to the user.

    The Kendo documentation has a great example of custom binding in MVC for custom sorting, filtering, and paging.

    I wanted something lightweight to use that didn’t require rewriting all of a grid’s sorting functionality. Here is how I translate the sort type in a grid’s Read method in the controller.

            public JsonResult Coco_Read([DataSourceRequest]DataSourceRequest request)
            {   
                // Sort foreign key columns by text not id
                foreach (SortDescriptor sortDescriptor in request.Sorts)
                {
                    switch (sortDescriptor.Member)
                    {
                        case "CocoTypeId":
                            request.Sorts[request.Sorts.IndexOf(sortDescriptor)].Member = "CocoTypeName";
                            break;
                    }
                }
    
                return Json(Model.GetCoco().ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
            }
    

    My solution replaces any sort for the foreign key column CocoTypeId with the hidden text equivalent column CocoTypeName. This is the easiest technique I could come up with for sorting from what Telerik recommends.