We have a lot of changes inside Trirand. You may need to read this post

The forum is moved to another place.. All the existing users and forum posts are moved successfully in our new side guriddo.net

In the future, please post your questions and problems in the new place.

The existing forum will stay and will  be in read-only mode.

 

Kind Regards

Tony

Lets say I have two tables

customer table has cuid, name, gender, cityid
city table has cityid, cityname, citysize

And they are joined together like this

select cuid, name, cityname, citysize
from customer cu
left outer join city ct on cu.cityid = ct.id

1. How can I edit that only customer table is updated?
2. If I want to edit both at the same time, how do I do that instead?

Thanks

Here I am providing my col Model for drop down,

I am binding the dropdown dynamically,

{
name: 'SUPER_CLIENT', index: 'SUPER_CLIENT', align: 'left', width: 150, aligh: 'left', editrules: { required: true }, editable: true,
editoptions: {
aysnc: false,
dataUrl: '../../../Forms/Admin/MasterModule/AdminCommonHandler.ashx?Page=ProcessMaster', //to get the company name and Short Name.
buildSelect: function (data) {
var json = $.parseJSON(data);
var s = '<select>';
s += '<option value="">-------Select-------</option>';
$($.parseJSON(data)).map(function () {
s += '<option value="' + this.SHORT_NAME + '">' + this.COMPANY_NAME + '</option>';
});
return s + "</select>";
}, dataInit: function (elem) { $(elem).width(150); },readonly: 'readonly'
},
editrules: { required: true },
edittype: 'select',
formoptions: { elmsuffix: '' }

},

I am adding/removing the properties to the dropdown list on the basis of selected value from another dropdown.

while adding the new record it binds the dropdown but properties are not getting applied. Please provide me some solution.

Thanks

With Best Regards,

Sachin Vashishth.

I am new to Jqgrid. How do i format "value" below?. When the value is 0 to 0.00 , jqgrid is displaying it as empty when value is 0.

How to check whether the value is zero or empty ?

Snippet

groupingView: {

                groupField: ["column1", "Column2"],
                groupColumnShow: [false, false],
                showSummaryOnHide: true,
                groupSummary: [true],
                groupText: ['', '{0}<span class="GroupFormat"> {value} </span>'] 
            },

 

Appreciate all your help

 

Thanks

It was a syntax error in my MySQL statement.

Rappi

I have used jqgride and i am facing a problem when i want to find avg of a column for Ex-

sumFloat19 = grid.jqGrid('getCol', 'Loadfactor', true, 'avg');

it will give values like 73.4965517241 but i want 73.49, so help me solve this problem.

my 2nd problem is i want pagination but not want to pass dynamic value, not like that      rowList:[100,200,300],

 

 

  $("#lstReport").jqGrid('setGridParam', {url:'<%=request.getContextPath()%>/indiconsreport.do?method=condIndividual&fromdate='+fromdate+"&todate="+todate+"&region="+region+"&depot="+depot+"&condid="+condid,
                    datatype: "xml",
                    page: 1}).trigger("reloadGrid");
                jQuery("#lstReport").jqGrid({

                    url:'<%=request.getContextPath()%>/indiconsreport.do?method=condIndividual&fromdate='+fromdate+"&todate="+todate+"&region="+region+"&depot="+depot+"&condid="+condid,
                    datatype: "xml",
                    shrinkToFit : true,
                    pager: jQuery('#pgLstCustTx'),
                    colNames: ['Depot Code(A)','Duty_Date(B)','Conductor Name(C)','Collection Time(D)','RouteName (E)','WaybillNo (F)','ActualKM (G)','ScheduleKM (H)','NetIncome (I)','BaseFare (J)','Toll (K)','Bridge (L)','BasicFare (M=J+K-L)','BusTypeCode (N)','SeatCapacity (O)','VehicleNo (P)','IPKM (Q=M/G)','FarePerKM (R)','FCI (S=R*O)','LoadFactor (T=S/Q)'],
                    colModel: [
                        {name:'depot_cd',index:'depot_cd',width:100,searc:false,align:"left",editable:true,editoptions:{readonly: 'readonly'},key:true},
                        {name:'DUTY_DT',index:'DUTY_DT',width:100,searc:false,align:"left",editable:true,editoptions:{readonly: 'readonly'},key:true},
                        {name:'condName',index:'condName',width:100,searc:false,align:"left",editable:true,editoptions:{readonly: 'readonly'},key:true},
                        {name:'collection_tm',index:'collection_tm',width:100,search:false,editable:true,align:"right",editoptions:{readonly: 'readonly'},key:true},
                        {name:'route_nm',index:'route_nm',width:120,align:"left",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'waybill_no',index:'waybill_no',width:80,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'actual_km',index:'actual_km',width:80,search:false,align:"center",editable:true,editoptions:{readonly: 'readonly'},key:true},
                        {name:'schedule_km',index:'schedule_km',width:50,search:false,editable:true,align:"center",editoptions:{readonly: 'readonly'},key:true},
                        {name:'netincome',index:'netincome',width:70,align:"right",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'base_fare',index:'base_fare',width:90,align:"right",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'toll_amount',index:'toll_amount',width:60,align:"right",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'bridge',index:'bridge',width:60,align:"right",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'basicfare',index:'basicfare',width:150,align:"right",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'bus_type_cd',index:'bus_type_cd',width:50,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'seat_capacity',index:'seat_capacity',width:130,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'vehicle_no',index:'vehicle_no',width:80,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'ipkm',index:'ipkm',width:100,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                         {name:'farePerKm',index:'farePerKm',width:120,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'fci',index:'fci',width:100,align:"center",search:false,editable:true,editrules:{required: 'true'}},
                        {name:'Loadfactor',index:'Loadfactor',width:130,sorttype:'int',jsonmap:"Loadfactor",align:"center",search:false,editable:true,editrules:{required: 'true'}}
                    ],

                    rowNum: 1000,
                    scroll: false,
                    mtype: "post",
                    rownumbers: true,
                    rowList:[100,200,300],
                    width: $(window).width() - 20,
                    height: 250,
                    caption: "Conductor Individual Report",
                    footerrow: true,
                    rowNum: 1500,
                    loadonce: true,
                    gridComplete: function(){
                        $("#lstReport").setGridParam({datatype: 'local'});
                    },
                    sortname: 'depot_cd',
                    sortorder: "desc",
                    loadComplete: function(data) {
                        var grid = $('#lstReport');
                        grid.jqGrid('setCaption', 'Conductor Individual Report');
                        sumFloat4 = grid.jqGrid('getCol', 'depot_cd', false, 'sum');
                        sumFloat17 = grid.jqGrid('getCol', 'DUTY_DT', false, 'sum');
                        sumFloat7 = grid.jqGrid('getCol', 'collection_tm', true, 'sum');
                        sumFloat8 = grid.jqGrid('getCol', 'route_nm', true, 'sum');
                        sumFloat10 = grid.jqGrid('getCol', 'waybill_no', true, 'sum');
                        sumFloat5 = grid.jqGrid('getCol', 'actual_km', true, 'sum');
                        sumFloat6 = grid.jqGrid('getCol', 'schedule_km', true, 'sum');
                        sumFloat11 = grid.jqGrid('getCol', 'netincome', true, 'sum');
                        sumFloat12 = grid.jqGrid('getCol', 'basicfare', true, 'sum');
                        sumFloat13 = grid.jqGrid('getCol', 'bus_type_cd', true, 'sum');
                        sumFloat15 = grid.jqGrid('getCol', 'seat_capacity', true, 'sum');
                        sumFloat16 = grid.jqGrid('getCol', 'vehicle_no', true, 'sum');
                        sumFloat18 = grid.jqGrid('getCol', 'ipkm', true, 'avg');
                        sumFloat17 = grid.jqGrid('getCol', 'fci', true, 'avg');
                        sumFloat19 = grid.jqGrid('getCol', 'Loadfactor', true, 'avg');
 
 
                        grid.jqGrid('footerData', 'set', { depot_cd: 'Total', DUTY_DT : '', condName:'', collection_tm: '', route_nm: '', waybill_no: ''
                            , actual_km: sumFloat5,schedule_km: sumFloat6,netincome: sumFloat11, basicfare: sumFloat12, bus_type_cd: ''
                            , seat_capacity: sumFloat15,vehicle_no: '',ipkm:sumFloat18,fci:sumFloat17, Loadfactor:sumFloat19});
                    }

I just assume that you use datatype:local for that? Its hard to tell without your code

Have you set the editUrl correctly? - Even if jqGrid does not send anything to a server in local-mode it still needs to know what to do for editing.

editurl: "clientArray" -> tells jqGrid to write changes into the local array you give it.

 

I've followed the examples and the array used to populate jqGrid appears unchanged but I see the changes do appear in the on-screen grid. So I'm stumped. I really need to understand where jqGrid stores its data/changes in its object model so I can extract the data.

Any ideas?

I am new with jqgrid and I don't know how it works. I am a PHP/MySQL/Html programmer........

My Problem is, how can I write the data to a MySQL Database?

I read the data from database and the grid show it, but when I click add/edit/del nothing is saved.

My mainfile:

<?php

require_once("models/config.php");
include("template/header.php");

?>
<script type="text/javascript">
$(function () {
$("#list").jqGrid({
url: "adm_land_action.php",
datatype: "xml",
mtype: "POST",
colNames: ["id", "Aufenthaltsort"],
colModel: [
{ name: "id", width: 50, search:false, editable: false },
{ name: "land", width: 800, editable: true, search:true, editoptions:{size:25}, edittype:"text", sorttype:'string', searchoptions:{sopt:['cn','eq','bw','ew']}}
],
pager: "#pager",
rowNum: 10,
//rowList: [10, 20, 30],
sortname: "land",
sortorder: "asc",
loadonce:false,
viewrecords: true,
gridview: true,
autoencode: true,
editurl: "adm_land_edit.php",
caption: "Aufenthaltsorte"
})
jQuery("#list").jqGrid('filterToolbar',{searchOperators : true});

jQuery("#list").jqGrid('navGrid','#pager',
{search:false,edit:true,add:true,del:true}, //options
{height:280,reloadAfterSubmit:true}, // edit options
{height:280,reloadAfterSubmit:true}, // add options
{reloadAfterSubmit:true}, // del options
{} // search options
);

$("#add").click(function(){
jQuery("#list").jqGrid('editGridRow',"new",{height:280,reloadAfterSubmit:true})});

$("#edit").click(function(){
jQuery("#list").jqGrid('editGridRow',"edit",{height:280,reloadAfterSubmit:true})});

$("#del").click(function(){
jQuery("#list").jqGrid('editGridRow',"del",{height:280,reloadAfterSubmit:true})});
});
</script>

<div class="container">
<header>
<?php include("template/sub_header.php"); ?>
</header>
<h1>Aufenthaltsorte</h1>
<?php if(isUserLoggedIn()) {
include('template/tm_menu.php');
} else {
echo "<meta http-equiv='refresh' content='0; URL=index.php'>";
exit;
}
?>
<article class="content">

<?php if(isUserLoggedIn()) {
echo "

<table id='list'></table>
<div id='pager'></div>
";

} ?>
</article>

<?php include("template/footer.php"); ?> 

My file to read the database adm_land_action.php

<?php
//include the information needed for the connection to MySQL data base server.
// we store here username, database and password
require_once("models/config.php");

// to the url parameter are added 4 parameters as described in colModel
// we should get these parameters to construct the needed query
// Since we specify in the options of the grid that we will use a GET method
// we should use the appropriate command to obtain the parameters.
// In our case this is $_GET. If we specify that we want to use post
// we should use $_POST. Maybe the better way is to use $_REQUEST, which
// contain both the GET and POST variables. For more information refer to php documentation.
// Get the requested page. By default grid sets this to 1.
$page = $_POST['page'];

// get how many rows we want to have into the grid - rowNum parameter in the grid
$limit = $_POST['rows'];

// get index row - i.e. user click to sort. At first time sortname parameter -
// after that the index from colModel
$sidx = $_POST['sidx'];

// sorting order - at first time sortorder
$sord = $_POST['sord'];

// if we not pass at first time index use the first column for the index or what you want
if(!$sidx) $sidx =1;

// connect to the MySQL database server
$db = mysql_connect($db_host, $db_user, $db_pass) or die("Connection Error: " . mysql_error());

// select the database
mysql_select_db($db_name) or die("Error connecting to db.");

// calculate the number of rows for the query. We need this for paging the result
$result = mysql_query("SELECT COUNT(*) AS count FROM ".$db_prefix."land");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];

// calculate the total pages for the query
if( $count > 0 && $limit > 0) {
$total_pages = ceil($count/$limit);
} else {
$total_pages = 0;
}

// if for some reasons the requested page is greater than the total
// set the requested page to total page
if ($page > $total_pages) $page=$total_pages;

// calculate the starting position of the rows
$start = $limit*$page - $limit;

// if for some reasons start position is negative set it to 0
// typical case is that the user type 0 for the requested page
if($start <0) $start = 0;

// the actual query for the grid data
$SQL = "SELECT id, land FROM ".$db_prefix."land ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error());

// we should set the appropriate header information. Do not forget this.
header("Content-type: text/xml;charset=utf-8");

$s = "<?xml version='1.0' encoding='utf-8'?>";
$s .= "<rows>";
$s .= "<page>".$page."</page>";
$s .= "<total>".$total_pages."</total>";
$s .= "<records>".$count."</records>";

// be sure to put text data in CDATA
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$s .= "<row id='". $row['id']."'>";
$s .= "<cell>". $row['id']."</cell>";
$s .= "<cell>". $row['land']."</cell>";
$s .= "</row>";
}
$s .= "</rows>";

echo $s;
?>

And my file to write to database adm_land_adit.php

<?php
//include the information needed for the connection to MySQL data base server.
// we store here username, database and password
require_once("models/config.php");

$id = $_POST['id']; 

$land = $_POST['land'];

// connect to the MySQL database server
$db = mysql_connect($db_host, $db_user, $db_pass) or die("Connection Error: " . mysql_error());

// select the database
mysql_select_db($db_name) or die("Error connecting to db.");

// the actual query for the grid data
$SQL = "update ".$db_prefix."land set land=$land where id=$id";
$result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error());

?>

It have only the edit function in it.

When I click on edit, the editform pop up and when I edit the entry and click on Save, the grid reloads, the form is showing again and the data is not changed.

What is wrong?

Have I don't understand the script and the functions?

Please help a newbie

Greetz Rappi

I'm using jqgrid for a lot of things, and it's awesome. But I have a particular requirement related with visited links. I need to apply a specific styles for visited links which are inside Grids (jqgrid), and I create this links using Custom Formatter:

...

{ name: 'Comments', width: ((pageWidth - iconsWidth) * (14.75 / 100)), index: 'Id', sortable: false, formatter: viewCommentsButton, "resizable": false }

....

...

...

The Issue is when you visited any link of these...you don't see any kind of style applied over them even when is set on global.css:

a.link:visited {
color: #2774AA!important;
border-bottom-color: #EEEEEE;
}

So, please Can Someone help me with this Issue?

 

Best Regards,

 

Ricardo Guerrero.

Anyway, I've built all my models and controllers and have grids working for the pages on which I'm going to use them.  I'm able to display, edit, add, and delete records as expected and I've been successful in wiring up edit forms that use my controllers to populate dropdown options.  All of that is working great.

The problem I have is when I display each record in the main grid.  I have two tables, each with corresponding models and controllers.  Table 1 is Seasons (id, name, start date, end date, frequency).  Table 2 is Frequency (id and description).  Basically the frequency is a lookup for values of weekly, bi-weekly, monthly, etc.  The FrequencyId primary key is a foreign key in the Season table.

When my edit/add form is populated, I use the frequency controller to return an html string of options and that part works great.  It even populates the correct selection on edit.  When the grid is displaying each Season row, though, the Frequency field shows the ID.  I have been searching for a solution and trying many different ways to code this for a couple days now and nothing is working.  I used "formatter: select" and that just blanked out the field on load and I've recently been trying to use a custom formatter so that I can pass the cell value back to my controller method.  That's where I'm stuck.  How do I setup the custom formatter to return a string from the controller instead of just returning the literal string.  I've tried to use a url property instead of what you see below but haven't had any luck there, either.

Also, am I doing this the right way?  Am I making it too complicated?  Is there an easy way to do what I'm trying to do here?

Thanks!

 

My Seasons ViewModel:

$(function () {
$("#grid").jqGrid({
url: "/AdminSeasons/GetSeasons",
datatype: 'json',
mType: 'Get',
colNames: ['SeasonId', 'Season Name', 'Start Date', 'End Date', 'Bowl Frequency', 'Handicap Base Score', 'Handicap Percentage', 'Day Of The Week'],
colModel: [
{ key: true, name: 'SeasonId', index: 'SeasonId', hidden: true },
{ key: false, name: 'SeasonName', index: 'SeasonName', editable: true },
{
editable: true, width: 120, name: "SeasonStartDate", search: false, formatter: "date",
formatoptions: { srcformat: "m/d/Y h:i:s A", newformat: "Y-m-d" }
},
{
editable: true, width: 120, name: "SeasonEndDate", search: false, formatter: "date",
formatoptions: { srcformat: "m/d/Y h:i:s A", newformat: "Y-m-d" }
},
{
editable: true, width: 120, name: "FrequencyId", search: false, edittype: "select", formatter: frequencyFormatter,
editoptions: { dataUrl: "/AdminFrequency/FrequencyList" }
},
{
editable: true, width: 120, name: "HandicapBaseScore", search: false, edittype: "select",
editoptions: { dataUrl: "/AdminSeasons/HtmlFormattedBaseScoreList" }
},
{
editable: true, width: 120, name: "HandicapPercentage", search: false, edittype: "select",
editoptions: { dataUrl: "/AdminSeasons/HtmlFormattedPercentageList" }
},
{
editable: true, width: 120, name: "DayOfTheWeekId", search: false, edittype: "select", formatter: "select",
editoptions: { dataUrl: "/AdminDayOfTheWeek/DayOfTheWeekList" }
}],
pager: jQuery("#pager"),
rowNum: 10,
rowList: [10, 20, 30, 40],
height: '100%',
viewRecords: true,
caption: 'Manage Seasons',
emptyRecords: 'No records to display',
jsonReader: {
root: 'rows',
page: 'page',
total: 'total',
records: 'records',
repeatitems: false,
id: '0'
},
autowidth: true,
multiselect: false
}).navGrid('#pager', { edit: true, add: true, del: true, search: false, refresh: true },
{
// edit options
zIndex: 100,
url: '/AdminSeasons/Edit',
closeOnEscape: true,
closeAfterEdit: true,
recreateForm: true,
//afterComplete: function (response) {
// if (response.responseText) {
// alert(response.responseText);
// }
//}
},
{
// add options
zIndex: 100,
url: '/AdminSeasons/Create',
closeOnEscape: true,
closeAfterAdd: true,
//afterComplete: function (response) {
// if (response.responseText) {
// alert(response.responseText);
// }
//}
},
{
// delete options
zIndex: 100,
url: '/AdminSeasons/Delete',
closeOnEscape: true,
closeAfterDelete: true,
recreateForm: true,
msg: 'Are you sure you want to delete this season?',
//afterComplete: function (response) {
// if (response.responseText) {
// alert(response.responseText);
// }
//}
});

function frequencyFormatter(cellvalue, options, rowObject) {
return '/AdminFrequency/FrequencyDescriptById/' + cellvalue;
//debugger;
//return frequency;
}
});

when you say use server side code what do you mean?

How can we assign dynamic width and height for jqgrid,

Also please specify how we can set he td width of jqgrid so that it accommodates the complete text passed into it.

Thanks in Advance.

How can I color the values inside columns if they contain negative or positive values?

say for positive I want the color to be green and negative I want it to be Red.

1) Using a custom formatter on the group field (following the next example that oleg kindly provided)

http://www.ok-soft-gmbh.com/jq.....atter1.htm

2) Using formatDisplayField according to the following example:

http://www.trirand.com/blog/?p.....d-callback

Everything works as expected (I can add some text for example), but if I set hideFirstGroupCol: true the format function is no longer called.

What can I do to get the desired behaviour?

Thanks in advance!

I`m looking for solution to populate dropdowns of filtertoolbar not with values from table, but from GET-request to server. 

Request can provide data in any form. After selection of value of one dropdown new get request should be produced and all other dropdowns should be repopulated. Multiple selection is allowed.

 

Thank you in advance

I have a grid with checkboxes. I want that on the custom button(which I created) click, the selected rows along with data should be copied to the clipboard, so that the data can be reused.

All help is appreciated.