grid ComboBox editor issue

[DavidSimmonsParticipant]

I have an issue with my ComboBox editor in my grid…Example code below. If you use the mouse and select each choice everything works well. If you are using remoteAutoComplete=true and you edit one column everything work fine. Where I found the issue is when you are adding or editing multiple rows the first row works fine, but beyond that lookups stop performing correctly. The ComboBox opens with no entries or you type one in and press enter but the value entered is the previous rows value.

I think when you are using remoteAutoComplete=true the last lookup value and or results are not being cleared but having a hard time proving this. Is anyone using the ComboBox editor with remoteAutoComplete=true and large data sets? If so I would really appreciate some help.

Lookup datasource;
var ProjectTypeSearch=”;
var ProjectTypeIndex=0;

var ProjectTypeSource = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘Type’ }
],
id: ‘id’,
url: ‘../ProjectType/ProjectTypeJSON.php’,
async: false,
data: {
featureClass: “P”,
style: “full”,
maxRows: 12
}
};

var ProjectTypeDataAdapter = new $.jqx.dataAdapter(ProjectTypeSource, {
formatData: function (data) {
data.name_startsWith = ProjectTypeSearch;
$(“#message”).html(“Search: ” + ProjectTypeSearch);
return data;
}
});

Column;
{ text: ‘Type’, datafield: ‘Type’, width: 150, editable: true, filterable: true, filtertype: ‘list’, columntype: ‘combobox’,
createeditor: function (row, column, editor) {
editor.jqxComboBox({selectedIndex: 0, source: ProjectTypeDataAdapter, remoteAutoComplete: true, autoOpen: false, dropDownHeight: 250, displayMember: “Type”, valueMember: “ID”,
renderer: function (index, label, value) {
var item = ProjectTypeDataAdapter.records[index];
if (item != null) {
var label = item.Type;
return label;
}
return “”;
},
renderSelectedItem: function(index, item) {
var item = ProjectTypeDataAdapter.records[index];
if (item != null) {
var label = item.Type;
return label;
}
return “”;
},
search: function (searchString) {
ProjectTypeSearch = searchString;
ProjectTypeDataAdapter.dataBind();
}
});
editor.bind(‘select’, function (event) {
var item = event.args.item;
if (item) {
var value = item.value;
var label = item.label;
ProjectTypeIndex = value;
}
});
},
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;
}
},

JSON Data Results;
[{“ID”:”1″,”0″:”1″,”Type”:”Airport”,”1″:”Airport”},{“ID”:”2″,”0″:”2″,”Type”:”Test”,”1″:”Test”},{“ID”:”3″,”0″:”3″,”Type”:”Commercial”,”1″:”Commercial”},{“ID”:”4″,”0″:”4″,”Type”:”Government”,”1″:”Government”},{“ID”:”5″,”0″:”5″,”Type”:”Hospital”,”1″:”Hospital”},{“ID”:”6″,”0″:”6″,”Type”:”Hotel”,”1″:”Hotel”},{“ID”:”7″,”0″:”7″,”Type”:”ISD”,”1″:”ISD”},{“ID”:”8″,”0″:”8″,”Type”:”Medical”,”1″:”Medical”},{“ID”:”9″,”0″:”9″,”Type”:”Office Building”,”1″:”Office Building”},{“ID”:”10″,”0″:”10″,”Type”:”Religious”,”1″:”Religious”},{“ID”:”11″,”0″:”11″,”Type”:”Restaurant”,”1″:”Restaurant”},{“ID”:”12″,”0″:”12″,”Type”:”Retail”,”1″:”Retail”},{“ID”:”13″,”0″:”13″,”Type”:”University”,”1″:”University”}]

PHP Prepared Statement SQL;
if (isset($_REQUEST[“name_startsWith”])) {
$searchString = $_REQUEST[“name_startsWith”];
}

if ($searchString > “”) {
$preparedStatement = $conn->prepare(“SELECT ID, Type FROM ProjectType WHERE upper(Type) LIKE Concat(upper(?),’%’) Order by Type limit 100;”);
$preparedStatement->bindParam(1, $searchString, PDO::PARAM_STR);
if ($preparedStatement->execute()) {
$result = $preparedStatement->fetchAll();
echo json_encode($result);
}
} else {
$preparedStatement = $conn->prepare(“SELECT ID, Type FROM ProjectType Order by Type limit 100;”);
if ($preparedStatement->execute()) {
$result = $preparedStatement->fetchAll();
echo json_encode($result);
}
}

[Peter StoevKeymaster]

Hi David,

As the ComboBox Editor by default is working in a different way and you customize the editor’s behavior, you will have to override the built-in logic in the “initeditor” callback, too. That is because, there is built-in logic which gets the cell’s value and tries to update the editor selection.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

So is there a correct way to use the ComboBox Editor in the data grid. I have having issues finding documentation guide me in the correct way.

[Peter StoevKeymaster]

Hi David,

The correct way is demonstrated in the Editing samples available online i.e without changing the editor’s built-in behavior. By using “createeditor” or “initeditor” callbacks you can override the Grid’s built-in cell editing behavior and that is a custom implementation. So the Grid tries to automatically select a ComboBox item, and if you want to override this behavior, you should implement the “initeditor” callback and set the widget’s selection.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

So are you saying that within a grid the comboBox editor using a JSON data source is not supported. The examples only http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm?(classic)#demos/jqxgrid/customizededitors.htm shows ComboBox used and that is no using a JSON data source.

I would think this is the most important feature of a data grid in the real world.

Is anyone doing something like this that would like to work together on a fix please contact me…. I would gladly share my work with them…

[Peter StoevKeymaster]

Hi David,

1. I have not written anything like that.
2. For overriding the widget’s selection, you should override the “initeditor” callback, too.
3. If you have an issue with a widget, then please post a complete example so we will be able to test and debug it locally and also provide step by step instructions on how to reproduce it. Having part of a code does not help us and so we will not be able to help, too.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

This is code I am using and I have 10000 products that I am looking up. Again, it works perfect on the first row. Something is not clearing before the next row and that seem to be where my problem occurs. Let me know if you need anything more. I appreciate you help…

$(document).ready(function () {
var theme = “energyblue”;
var selectedID;
var jqxgrid=”#jqxgrid”;

var ProductSearch=”;
var ProductIndex=0;

var ProductSource = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘Product’ }
],
id: ‘id’,
url: ‘../Product/ProductJSON.php’,
async: false,
data: {
featureClass: “P”,
style: “full”,
maxRows: 12
}
};

var ProductDataAdapter = new $.jqx.dataAdapter(ProductSource, {
formatData: function (data) {
data.name_startsWith = ProductSearch;
return data;
}
});

var source = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘Project’ },
{ name: ‘ProductID’ },
{ name: ‘Product’ },
{ name: ‘Description’ },
{ name: ” }
],
id: ‘ID’,
url: “../BidDetailNew/BidDetailNewData.php”,
addrow: function (rowid, commit) {
var data = “insert=true&”;
$.ajax({
dataType: ‘json’,
url: ‘../BidDetailNew/BidDetailNewData.php’,
data: data,
cache: false,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(false);
}
});
},
deleterow: function (rowid, commit) {

},
updaterow: function (rowid, rowdata, commit) {
},
sort: function () {
$(jqxgrid).jqxGrid(‘updatebounddata’);
}
};

var self = this;
var pagerrenderer = function () {
var datainformation = $(jqxgrid).jqxGrid(‘getdatainformation’);
var paginginformation = datainformation.paginginformation;

var container = $(“

“);

var table = $(“”);
table.appendTo(container);

var tableRow = $(“”);
tableRow.appendTo(table);

var tableLeft = $(“”);
tableLeft.appendTo(tableRow);

var tableCenter = $(“”);
tableCenter.appendTo(tableRow);

var tableRight = $(“”);
tableRight.appendTo(tableRow);

var pagingLeftSpacer = $(““);
pagingLeftSpacer.appendTo(tableLeft);
self.pagingLeftSpacer = pagingLeftSpacer;

var addButton = $(“

“);
addButton.find(‘span’).addClass(‘ui-icon ui-icon-plus’);
addButton.width(16);
addButton.jqxTooltip({ content: ‘Add Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
addButton.appendTo(tableLeft);

var deleteButton = $(“

“);
deleteButton.find(‘span’).addClass(‘ui-icon ui-icon-trash’);
deleteButton.width(16);
deleteButton.jqxTooltip({ content: ‘Delete Selected Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
deleteButton.appendTo(tableLeft);

var refreshButton = $(“

“);
refreshButton.find(‘span’).addClass(‘ui-icon ui-icon-refresh’);
refreshButton.width(16);
refreshButton.jqxTooltip({ content: ‘Refresh Grid!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
refreshButton.appendTo(tableLeft);

var leftButton = $(“

“);
leftButton.find(‘span’).addClass(‘ui-icon ui-icon-triangle-1-w’);
leftButton.width(20);
leftButton.jqxTooltip({ content: ‘Previous Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
leftButton.appendTo(tableCenter);

var pagingLabel = $(“

“);
pagingLabel.text(1 + paginginformation.pagenum + ‘ of ‘ + Math.ceil(datainformation.rowscount / paginginformation.pagesize) + ‘ Pages ‘ + datainformation.rowscount + ‘ Records’);
pagingLabel.appendTo(tableCenter);
self.pagingLabel = pagingLabel;

var rightButton = $(“

“);
rightButton.find(‘span’).addClass(‘ui-icon ui-icon-triangle-1-e’);
rightButton.width(20);
rightButton.jqxTooltip({ content: ‘Next Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
rightButton.appendTo(tableCenter);

var pagingRightSpacer = $(““);
pagingRightSpacer.appendTo(tableLeft);
self.pagingRightSpacer = pagingRightSpacer;

var closeButton = $(“

“);
closeButton.find(‘span’).addClass(‘ui-icon ui-icon-close’);
closeButton.width(16);
closeButton.jqxTooltip({ content: ‘Close Window!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
closeButton.appendTo(tableRight);

var handleStates = function (event, button, className, add) {
button.bind(event, function () {
if (add == true) {
button.find(‘div’).addClass(className);
}
else button.find(‘div’).removeClass(className);
});
}

addButton.click(function (event) {
var rowcount = $(jqxgrid).jqxGrid(‘getdatainformation’).rowscount;
$(jqxgrid).jqxGrid(‘addrow’, rowcount++, ”);
});

deleteButton.click(function (event) {
if(selectedID != null || selectedID > 0){
$(jqxgrid).jqxGrid(‘deleterow’, selectedID);
}else{
$(“#message”).html(“You Must Select a Row First!”);
}
});

refreshButton.click(function (event) {
$(jqxgrid).jqxGrid(‘updatebounddata’);
});

closeButton.click(function (event) {
$(“#message”).html(“Close Window!”);
window.close();
});

rightButton.click(function () {
$(jqxgrid).jqxGrid(‘gotonextpage’);
});

leftButton.click(function () {
$(jqxgrid).jqxGrid(‘gotoprevpage’);
});

if (theme != ”) {
handleStates(‘mousedown’, rightButton, ‘icon-arrow-right-selected-‘ + theme, true);
handleStates(‘mouseup’, rightButton, ‘icon-arrow-right-selected-‘ + theme, false);
handleStates(‘mousedown’, leftButton, ‘icon-arrow-left-selected-‘ + theme, true);
handleStates(‘mouseup’, leftButton, ‘icon-arrow-left-selected-‘ + theme, false);
handleStates(‘mouseenter’, rightButton, ‘icon-arrow-right-hover-‘ + theme, true);
handleStates(‘mouseleave’, rightButton, ‘icon-arrow-right-hover-‘ + theme, false);
handleStates(‘mouseenter’, leftButton, ‘icon-arrow-left-hover-‘ + theme, true);
handleStates(‘mouseleave’, leftButton, ‘icon-arrow-left-hover-‘ + theme, false);
}

return container;
}

$(jqxgrid).bind(‘pagechanged’, function () {
var datainformation = $(jqxgrid).jqxGrid(‘getdatainformation’);
var paginginformation = datainformation.paginginformation;
var pagenum = paginginformation.pagenum;
var pagesize = paginginformation.pagesize;
var pagescount = paginginformation.pagescount;
self.pagingLabel.text(1 + paginginformation.pagenum + ‘ of ‘ + Math.ceil(datainformation.rowscount / paginginformation.pagesize) + ‘ Pages ‘ + datainformation.rowscount + ‘ Records’);
});

var dataAdapter = new $.jqx.dataAdapter(source);

$(jqxgrid).jqxGrid({
theme: theme,
width: ‘99.9%’,
height: 600,
source: dataAdapter,
scrollbarsize: 10,
sortable: true,
sorttogglestates: 1,
altrows: true,
virtualmode: false,
pageable: true,
pagesize: 20,
pagerrenderer: pagerrenderer,
enablehover: false,
enabletooltips: false,
autoheight: false,
autosavestate: false,
autoloadstate: false,
columnsresize: true,
columnsreorder: false,
autoresizecolumns: ‘all’,
editable: true,
selectionmode: “singlecell”,
filterable: true,
showfilterrow: false,
statusbarheight: 26,
showstatusbar: true,
showaggregates: true,
columns: [
{ text: ‘ID’, dataField: ‘ID’, width: 60, hidden: false, pinned: true, editable: false},
{ text: ‘Project’, dataField: ‘Project’, width: 30, pinned: true, hidden: true, editable: false, filterable: false },
{ text: ‘ProductID’, dataField: ‘ProductID’, width: 80, hidden: true, editable: true, filterable: false,
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;
}
},
{ text: ‘Product’, datafield: ‘Product’, width: 180, editable: true, filterable: true, filtertype: ‘textbox’, columntype: ‘combobox’,
createeditor: function (row, column, editor) {
editor.jqxComboBox({selectedIndex: 0, source: ProductDataAdapter, remoteAutoComplete: true, remoteAutoCompleteDelay: 200, autoOpen: false, dropDownHeight: 200, displayMember: “Product”, valueMember: “ID”,
renderer: function (index, label, value) {
var item = ProductDataAdapter.records[index];
if (item != null) {
var label = item.Product;
return label;
}
return “”;
},
renderSelectedItem: function(index, item) {
var item = ProductDataAdapter.records[index];
if (item != null) {
var label = item.Product;
return label;
}
return “”;
},
search: function (searchString) {
ProductSearch = searchString;
ProductDataAdapter.dataBind();
}
});
editor.bind(‘select’, function (event) {
var item = event.args.item;
if (item) {
var value = item.value;
var label = item.label;
ProductIndex = value;
}
});
},
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;

}
},
{ text: ‘Description’, dataField: ‘Description’, width: 300, hideable: true, editable: true, filterable: false },
{ text: ”, dataField: ”, width: ‘100%’, editable: false, filterable: false }
]
});

$(jqxgrid).bind(‘cellvaluechanged’, function (event) {
var rowid = args.rowindex;
var column = args.datafield;
var value = args.newvalue;
var oldvalue = args.oldvalue;
var data = new Object();

var id = $(jqxgrid).jqxGrid(‘getcellvalue’, rowid, “ID”);

if(column === “Product”) {
$(jqxgrid).jqxGrid(‘setcellvalue’, rowid, “ProductID”, ProductIndex);
return;
}

data.update=’true&’;
data.id=id;
data.column=column;
data.value=value;

if(value !== oldvalue ){
$.ajax({
dataType: ‘json’,
url: ‘../BidDetailNew/BidDetailNewData.php’,
cache: false,
data: data,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(false);
}
});
}
});

$(jqxgrid).bind(‘rowclick’, function (event) {
var args = event.args;
var row = args.rowindex;
selectedID = $(jqxgrid).jqxGrid(‘getcellvalue’, row, “ID”);
$(“#message”).html(“Selected RowID: ” + selectedID);
});
});

PHP SQL;
if (isset($_REQUEST[“name_startsWith”])) {
$searchString = $_REQUEST[“name_startsWith”];
$preparedStatement = $conn->prepare(“SELECT ID, Product FROM Product WHERE upper(Product) LIKE Concat(upper(?),’%’) Limit 100;”);
$preparedStatement->bindParam(1, $searchString, PDO::PARAM_INT);
if ($preparedStatement->execute()) {
$result = $preparedStatement->fetchAll();

echo json_encode($result);
}
} else {
$preparedStatement = $conn->prepare(“SELECT ID, Product FROM Product Limit 100;”);
if ($preparedStatement->execute()) {
$result = $preparedStatement->fetchAll();

echo json_encode($result);
}
}

[DavidSimmonsParticipant]

Ok here is an example using one of your examples and where I got the idea to do what i am doing. Your example is from http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxcombobox/index.htm#demos/jqxcombobox/remotesearch.htm I am need something like this in a data grid. I think have not been communicating the problem well, so hopefully this will help.

As far as datagrid functionality goes, I think this is the most common function a data grid needs is to be able to lookup key values from support tables or foreign keys tables with some kind of comboBox. I am looking for any help from anyone. I will share code, ideas or anything to help come up with a solution for this.

In this example I just want to lookup the Name which seem to be the state and store it with the local database. This just and example. But try to imagine having to enter 20 rows a of data each with a lookup.

In my real code the first lookup works great. I have over 10,000 items to evaluate in the lookup. The second starts to fail. What I don’t understand is does the data grid editor re-initalize the data adapter for each data grid row that is edited? This seem to be the area where my issue occurs. On the second lookup the drop down box my not populate or the lookup never occurs.

I realize I my not be explaining this in most clear way. Help me explain my question better. I think this issue is most important….

Please help… Contact me direct if needed…

$(document).ready(function () {
var theme = “energyblue”;
var jqxgrid=”#jqxgridTest”;

var selectedID;

var testSearch=”;
var testIndex=0;

var testSource = {
datatype: “jsonp”,
datafields: [
{ name: ‘countryName’ },
{ name: ‘name’ },
{ name: ‘population’, type: ‘float’ },
{ name: ‘continentCode’ },
{ name: ‘adminName1’ }
],
url: “http://ws.geonames.org/searchJSON”,
data: {
featureClass: “P”,
style: “full”,
maxRows: 12
}
};

var testDataAdapter = new $.jqx.dataAdapter(testSource,{
formatData: function (data) {
data.name_startsWith = testSearch;
return data;
}
});

var source = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘State’ },
{ name: ‘StateCode’ },
{ name: ‘Created’ },
{ name: ‘CreatedBy’ },
{ name: ‘Updated’ },
{ name: ‘UpdatedBy’ },
{ name: ” }
],
id: ‘ID’,
url: “../exampleTest/TestData.php”,
addrow: function (rowid, commit) {
var data = “insert=true&”;
$.ajax({
dataType: ‘json’,
url: ‘../exampleTest/TestData.php’,
data: data,
cache: false,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(false);
}
});
},
deleterow: function (rowid, commit) {
var response=confirm(“Are you sure you want to delete this record!”);
if (response==true) {
var data = “delete=true&” + $.param({ id: selectedID });
$.ajax({
dataType: ‘json’,
url: ‘../exampleTest/TestData.php’,
cache: false,
data: data,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
commit(false);
$(jqxgrid).jqxGrid(‘updatebounddata’);
}
});
}

},
updaterow: function (rowid, rowdata, commit) {

},
sort: function () {
$(jqxgrid).jqxGrid(‘updatebounddata’);
}
};

var self = this;
var pagerrenderer = function () {
var datainformation = $(jqxgrid).jqxGrid(‘getdatainformation’);
var paginginformation = datainformation.paginginformation;

var container = $(“

“);

var table = $(“”);
table.appendTo(container);

var tableRow = $(“”);
tableRow.appendTo(table);

var tableLeft = $(“”);
tableLeft.appendTo(tableRow);

var tableCenter = $(“”);
tableCenter.appendTo(tableRow);

var tableRight = $(“”);
tableRight.appendTo(tableRow);

var pagingLeftSpacer = $(““);
pagingLeftSpacer.appendTo(tableLeft);
self.pagingLeftSpacer = pagingLeftSpacer;

var addButton = $(“

“);
addButton.find(‘span’).addClass(‘ui-icon ui-icon-plus’);
addButton.width(16);
addButton.jqxTooltip({ content: ‘Add Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
addButton.appendTo(tableLeft);

var deleteButton = $(“

“);
deleteButton.find(‘span’).addClass(‘ui-icon ui-icon-trash’);
deleteButton.width(16);
deleteButton.jqxTooltip({ content: ‘Delete Selected Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
deleteButton.appendTo(tableLeft);

var refreshButton = $(“

“);
refreshButton.find(‘span’).addClass(‘ui-icon ui-icon-refresh’);
refreshButton.width(16);
refreshButton.jqxTooltip({ content: ‘Refresh Grid!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
refreshButton.appendTo(tableLeft);

var leftButton = $(“

“);
leftButton.find(‘span’).addClass(‘ui-icon ui-icon-triangle-1-w’);
leftButton.width(20);
leftButton.jqxTooltip({ content: ‘Previous Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
leftButton.appendTo(tableCenter);

var pagingLabel = $(“

“);
pagingLabel.text(1 + paginginformation.pagenum + ‘ of ‘ + Math.ceil(datainformation.rowscount / paginginformation.pagesize) + ‘ Pages ‘ + datainformation.rowscount + ‘ Records’);
pagingLabel.appendTo(tableCenter);
self.pagingLabel = pagingLabel;

var rightButton = $(“

“);
rightButton.find(‘span’).addClass(‘ui-icon ui-icon-triangle-1-e’);
rightButton.width(20);
rightButton.jqxTooltip({ content: ‘Next Record!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
rightButton.appendTo(tableCenter);

var pagingRightSpacer = $(““);
pagingRightSpacer.appendTo(tableLeft);
self.pagingRightSpacer = pagingRightSpacer;

var closeButton = $(“

“);
closeButton.find(‘span’).addClass(‘ui-icon ui-icon-close’);
closeButton.width(16);
closeButton.jqxTooltip({ content: ‘Close Window!’, position: ‘mouse’, name: ‘Tooltip’, theme: theme });
closeButton.appendTo(tableRight);

var handleStates = function (event, button, className, add) {
button.bind(event, function () {
if (add == true) {
button.find(‘div’).addClass(className);
}
else button.find(‘div’).removeClass(className);
});
}

addButton.click(function (event) {
var rowcount = $(jqxgrid).jqxGrid(‘getdatainformation’).rowscount;
$(jqxgrid).jqxGrid(‘addrow’, rowcount++, ”);
});

deleteButton.click(function (event) {
if(selectedID != null || selectedID > 0){
$(jqxgrid).jqxGrid(‘deleterow’, selectedID);
}else{
$(“#message”).html(“You Must Select a Row First!”);
}
});

refreshButton.click(function (event) {
$(jqxgrid).jqxGrid(‘updatebounddata’);
});

closeButton.click(function (event) {
$(“#message”).html(“Close Window!”);
window.close();
});

rightButton.click(function () {
$(jqxgrid).jqxGrid(‘gotonextpage’);
});

leftButton.click(function () {
$(jqxgrid).jqxGrid(‘gotoprevpage’);
});

if (theme != ”) {
handleStates(‘mousedown’, rightButton, ‘icon-arrow-right-selected-‘ + theme, true);
handleStates(‘mouseup’, rightButton, ‘icon-arrow-right-selected-‘ + theme, false);
handleStates(‘mousedown’, leftButton, ‘icon-arrow-left-selected-‘ + theme, true);
handleStates(‘mouseup’, leftButton, ‘icon-arrow-left-selected-‘ + theme, false);
handleStates(‘mouseenter’, rightButton, ‘icon-arrow-right-hover-‘ + theme, true);
handleStates(‘mouseleave’, rightButton, ‘icon-arrow-right-hover-‘ + theme, false);
handleStates(‘mouseenter’, leftButton, ‘icon-arrow-left-hover-‘ + theme, true);
handleStates(‘mouseleave’, leftButton, ‘icon-arrow-left-hover-‘ + theme, false);
}

return container;
}

$(jqxgrid).bind(‘pagechanged’, function () {
var datainformation = $(jqxgrid).jqxGrid(‘getdatainformation’);
var paginginformation = datainformation.paginginformation;
var pagenum = paginginformation.pagenum;
var pagesize = paginginformation.pagesize;
var pagescount = paginginformation.pagescount;
self.pagingLabel.text(1 + paginginformation.pagenum + ‘ of ‘ + Math.ceil(datainformation.rowscount / paginginformation.pagesize) + ‘ Pages ‘ + datainformation.rowscount + ‘ Records’);
});

var dataAdapter = new $.jqx.dataAdapter(source);

$(jqxgrid).jqxGrid({
theme: theme,
width: ‘99.9%’,
height: 600,
source: dataAdapter,
scrollbarsize: 10,
sortable: true,
sorttogglestates: 1,
altrows: true,
virtualmode: false,
pageable: true,
pagesize: 20,
pagerrenderer: pagerrenderer,
enablehover: false,
enabletooltips: false,
autoheight: false,
autosavestate: false,
autoloadstate: false,
columnsresize: true,
columnsreorder: false,
autoresizecolumns: ‘all’,
editable: true,
selectionmode: “singlecell”,
filterable: true,
showfilterrow: false,
statusbarheight: 26,
showstatusbar: true,
showaggregates: true,
handlekeyboardnavigation: function(event) {
$.getScript(“../javascript/keyboardNavigation.js”, function(){ keyboardNavigation(event, selectedID); });
},
columns: [
{ text: ‘ID’, dataField: ‘ID’, width: 60, hidden: false, pinned: true, editable: false},
{ text: ‘State’, datafield: ‘State’, width: 180, editable: true, filterable: true, filtertype: ‘textbox’, columntype: ‘combobox’,
cellbeginedit: function (row, datafield, columntype) {
//if (row == 2)
//return false;
},
createeditor: function (row, column, editor) {
editor.jqxComboBox({selectedIndex: 0, source: TestDataAdapter, remoteAutoComplete: true, remoteAutoCompleteDelay: 200, autoOpen: false, dropDownHeight: 200, displayMember: “Name”, valueMember: “Name”,
renderer: function (index, label, value) {
var item = TestDataAdapter.records[index];
if (item != null) {
var label = item.Name;
return label;
}
return “”;
},
renderSelectedItem: function(index, item) {
var item = TestDataAdapter.records[index];
if (item != null) {
var label = item.Name;
return label;
}
return “”;
},
search: function (searchString) {
TestSearch = searchString;
TestDataAdapter.dataBind();
}
});
editor.bind(‘select’, function (event) {
var item = event.args.item;
if (item) {
var value = item.value;
var label = item.label;
TestIndex = value;
}
});
},
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;

},
cellendedit: function (row, datafield, columntype, oldvalue, newvalue) {

}
},

{ text: ‘StateCode’, dataField: ‘StateCode’, width: 80, hidden: true, pinned: true, editable: false, filterable: false },
{ text: ‘Created’, dataField: ‘Created’, width: 160, editable: false, filterable: false },
{ text: ‘CreatedBy’, dataField: ‘CreatedBy’, width: 160, editable: false, filterable: true, filtertype: ‘textbox’ },
{ text: ‘Updated’, dataField: ‘Updated’, width: 160, editable: false, filterable: false },
{ text: ‘UpdatedBy’, dataField: ‘UpdatedBy’, width: 160, editable: false, filterable: true, filtertype: ‘textbox’ },
{ text: ”, dataField: ”, width: ‘50%’, editable: false, filterable: false }
]
});

$(jqxgrid).bind(‘cellvaluechanged’, function (event) {
var rowid = args.rowindex;
var column = args.datafield;
var value = args.newvalue;
var oldvalue = args.oldvalue;
var data = new Object();

var id = $(jqxgrid).jqxGrid(‘getcellvalue’, rowid, “ID”);

if(column === “Test”) {
$(jqxgrid).jqxGrid(‘setcellvalue’, rowid, “TestID”, TestIndex);
return;
}

data.update=’true&’;
data.id=id;
data.column=column;
data.value=value;

if(value !== oldvalue ){
$.ajax({
dataType: ‘json’,
url: ‘../exampleTest/TestData.php’,
cache: false,
data: data,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(false);
}
});
}
});

$(jqxgrid).bind(‘rowclick’, function (event) {
var args = event.args;
var row = args.rowindex;
selectedID = $(jqxgrid).jqxGrid(‘getcellvalue’, row, “ID”);
$(“#message”).html(“Selected RowID: ” + selectedID);

});

});

[Peter StoevKeymaster]

Hi David,

I do not understand. Why do you re-bind the Grid when the value of a cell is changed?

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

I was trying to find a way to handle foreign keys. The field ProductID is hidden and field Product as displayed on the grid is the Product Name from the Product Table so as the user selects the Product I set the index value or the ID of the Product from the Product Table. So when the field Product is edited it is ignored and the field name is change to ProductID which is all I real store. When I wrote this I was still learning jqWidgets and there may be a better way to handle foreign keys today but I haven’t found one.

$(jqxgrid).bind(‘cellvaluechanged’, function (event) {
var rowid = args.rowindex;
var column = args.datafield;
var value = args.newvalue;
var oldvalue = args.oldvalue;
var data = new Object();

var id = $(jqxgrid).jqxGrid(‘getcellvalue’, rowid, “ID”);

if(column === “Product”) {
$(jqxgrid).jqxGrid(‘setcellvalue’, rowid, “ProductID”, ProductIndex);
return;
}

data.update=’true&’;
data.id=id;
data.column=column;
data.value=value;

if(value !== oldvalue ){
$.ajax({
dataType: ‘json’,
url: ‘../BidDetailNew/BidDetailNewData.php’,
cache: false,
data: data,
success: function (data, status, xhr) {
$(“#message”).html(“Transaction: ” + xhr.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
$(“#message”).html(“Transaction: ” + jqXHR.responseText);
$(jqxgrid).jqxGrid(‘updatebounddata’);
commit(false);
}
});
}
});

In my table Invoice, the field Product is the ID of the Product from the Product table…

My SQL , SELECT Product ProductID, (SELECT Product FROM Product WHERE product.id=invoice.product) Product …

My Product DataSource;*****
var ProductSearch=”;
var ProductIndex=0;

var ProductSource = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘Product’ }
],
id: ‘id’,
url: ‘../Product/ProductJSON.php’,
async: false,
data: {
featureClass: “P”,
style: “full”,
maxRows: 12
}
};

var ProductDataAdapter = new $.jqx.dataAdapter(ProductSource, {
formatData: function (data) {
$(“#message”).html(ProductSearch);
data.name_startsWith = ProductSearch;
return data;
}
});

My Grid Source;******
var source = {
datatype: “json”,
datafields: [
{ name: ‘ID’ },
{ name: ‘ProductID’ },
{ name: ‘Product’ },
{ name: ” }
],
My Grid Columns;*******
{ text: ‘ProductID’, dataField: ‘ProductID’, width: 80, hidden: true, editable: true, filterable: false,
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;
}
},
{ text: ‘Product’, datafield: ‘Product’, width: 180, editable: true, filterable: true, filtertype: ‘textbox’, columntype: ‘combobox’,
cellbeginedit: function (row, datafield, columntype) {
//if (row == 2)
//return false;
},
createeditor: function (row, column, editor) {
editor.jqxComboBox({selectedIndex: 0, source: ProductDataAdapter, remoteAutoComplete: true, remoteAutoCompleteDelay: 200, autoOpen: false, dropDownHeight: 200, displayMember: “Product”, valueMember: “ID”,
renderer: function (index, label, value) {
var item = ProductDataAdapter.records[index];
if (item != null) {
var label = item.Product;
return label;
}
return “”;
},
renderSelectedItem: function(index, item) {
var item = ProductDataAdapter.records[index];
if (item != null) {
var label = item.Product;
return label;
}
return “”;
},
search: function (searchString) {
ProductSearch = searchString;
ProductDataAdapter.dataBind();
}
});
editor.bind(‘select’, function (event) {
var item = event.args.item;
if (item) {
var value = item.value;
var label = item.label;
ProductIndex = value;
}
});
},
cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
if (newvalue == “”) return oldvalue;

},
cellendedit: function (row, datafield, columntype, oldvalue, newvalue) {
ProductSearch=””;
ProductIndex=0;
}
},

[Peter StoevKeymaster]

Hi David,

jqxGrid already has built-in mechanism for handling foreign keys: gridkeyvaluecolumn.htm.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[DavidSimmonsParticipant]

This came out after I had started. Can you tell me from your example when you change the ComboBox are you only update the ID or foreign key the current table?

[DavidSimmonsParticipant]

Is there a way to get the updated field name or last updated field name on update?

updaterow: function (rowid, rowdata, commit) {

alert(rowid);
alert(rowdata.datafield);
},

[DavidSimmonsParticipant]

Having some trouble converting your example from XML to JSON. Will JSON work and can I use ComboBox with autoComplete instead of the List box?

[DavidSimmonsParticipant]

Would you please consider updating your php examples? Most of your examples in the php area are in json type. The example you sent
jqxGrid already has built-in mechanism for handling foreign keys: gridkeyvaluecolumn.htm is an xml example and I am having difficulties translating it to json and getting it to work.

[Author]

Posts