Navigating the data using JDE

JDE gateway accepts following variables that can be sent using both GET and POST methods:


This variable defines a query string to be searched for. When dbBee JDE receives dbBee_qs it will return all records that contain the word(s) or phrase (if the dbBee_qs value is enclosed in quotation marks), in any part of any non-numeric field in the record source used by JDE (Excel sheet or Access table).


This is an SQL WHERE clause, used to extract only those records that fulfill a specified criterion. Since dbBee works directly with Excel and Access files, dbBee_sql must follow Microsoft SQL syntax for WHERE clause.


Maximum number of records to be retuned for single request. The default is all records.


Number of record to start from. Default value is 1. Combined with dbBee_mr this can be used for pagination.

In this article we’ll show practical use of dbBee_mr and dbBee_sr variables to limit the number of records per request and to paginate the records (spreading result out over pages to make it easier to view).

We will use the same code used in previous blog with few minor modifications to existing code and adding some new lines:

var maxrec = 10;
var startrec=1;
var rc=0;
function MakeNavigation(){
var pg=1;
var curpg=parseInt(startrec / maxrec)+(parseInt(startrec / maxrec)==startrec / maxrec?0:1);
NavSel='<select id="SelPage" size="1" onchange="startrec=this.options[this.selectedIndex].value;loadJSON();">';
NavSel+='<option value="'+i+'">Go to page '+(pg++)+'</option>';
document.getElementById("demo").innerHTML += NavSel;
document.getElementById("SelPage").options[document.getElementById("SelPage").selectedIndex].text = "Current page "+curpg; 
function loadJSON(){
$.getJSON(''+startrec+'&dbbee_mr='+maxrec, function (data) {
    var tHTML="The search returned "+data.BooksCount + " records<br/>";
    for(var i in data.Books){
    tHTML += data.Books[i].Image+"<h1>" +
    data.Books[i].Title +
    "</h1><h2>" +
    data.Books[i].Author +
    "</h2><h2>" + 
    data.Books[i].Publisher +
    "</h2><h3>" +
    data.Books[i]['Year Published'] +
    document.getElementById("demo").innerHTML = tHTML;
<div id="demo"></div>

We marked all the new stuff with red, so it is easier to track. First let’s check what we have modified in code we used in previous article:


As you can see, we added dbBee variables dbbee_sr and dbbee_mr to the URL, so dbBee will send us only partial response, starting with record number defined in dbbee_sr and containing maximum of records defined in dbbee_mr.

Now about the new stuff - you’ll notice that we added three global variables: startrec, maxrec and rc and we added one new function MakeNavigation(). We set startrec value to 1, so initial display starts with first record. We set maxrec to 10, so our single page will contain maximum of 10 records. Variable rc is actual record counter and it has to be global because its value is modified by function loadJSON() and used by function MakeNavigation().

Function MakeNavigation() builds drop down select element to be used for navigating through pages.

Line var curpg=parseInt(startrec / maxrec)+(parseInt(startrec / maxrec)==startrec / maxrec?0:1); calculates the number of current page (so we can set dropdown selection box to proper value and change the option text).

Next block uses for loop to create selection box and the code below that sets its value to reflect the current page.

Please note that selection box has hooked onchange event onchange="startrec=this.options[this.selectedIndex].value;loadJSON();" that changes startrec variable to selected value of selection box and executes loadJSON() function to resend the request with new dbBee_sr value.
For live demo please click here.

Now, when we have solved the pagination we only need some sort of search form to have fully featured database search application without a single line of backend programming (that part is taken care of by dbBee).
Creating the search form is covered in our next article.