Guriddo » Topic: Grid Update Uses rowID instead of Primary Key

Grid Update Uses rowID instead of Primary Key

Guriddo
Grid Update Uses rowID instead of Primary Key

This topic has 3 replies, 2 voices, and was last updated 1 month, 2 weeks ago by guriddo.support.

Creator

Topic

January 27, 2023 at 11:26 pm #128083

kgrogers

Participant

I have a table that I am trying to update (code follows). When I click on the submit button I am getting an error:

SQLSTATE[23000]: Integrity constraint violation: 1761 Foreign key constraint for table ‘tblLandOwners’, record ‘5’ would lead to a duplicate entry in table ‘tblLandOwnerStatus’, key ‘PRIMARY’

Looking at the jqGrid.log file, the primary key value in the log file always equals the row number from the grid that the selected record is on rather that the primary key (which is in the LandOwnerID column in the database).

Any clue as to what I am doing wrong?

<?php
/*  PHP jqSuite version 5.5.5 */

require_once '/var/www/html/jqSuite/jq-config.php';
// include the jqGrid Class
require_once ABSPATH."php/PHPSuito/jqGrid.php";
// include the driver class
require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php";
// include the datepicker
// require_once ABSPATH."php/jqCalendar.php";

// Connection to the server
$conn = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
// Create the jqGrid instance
$grid2 = new jqGridRender($conn);
$grid2->debug=true;
$grid2->showError = true;
// Write the SQL Query
$grid2->SelectCommand = "
    select lo.LandownerID,
           lo.LandOwner,
           lo.LandOwnerNotes,
           ls.Status,
           case
               when lm.LastName = 'UNASSIGNED' then 'UNASSIGNED'
               else concat(lm.FirstName, ' ', lm.LastName)
           end CurrentlyAssignedTo,
           lo.LandOwnerAddress1,
           lo.LandOwnerAddress2,
           lo.LandOwnerCity,
           lo.LandOwnerState,
           lo.LandOwnerZip,
           lo.HowToContact,
           lo.MailingSalutation,
           lo.AddressedTo
    from tblLandOwners lo,
         tblLpcMembers lm,
         tblLandOwnerStatus ls
    where lo.CurrentlyAssignedTo = lm.LpcMemberID and
          lo.Status = ls.StatusID";

// Set output format to json
$grid2->dataType = 'json';
$grid2->table='tblLandOwners';
$grid2->setPrimaryKeyID('LandOwnerID');
// Let the grid create the model
$grid2->setColModel();


// Set some grid options
$grid2->setGridOptions(array(
    "rowNum"=>23,
    "rowList"=>array(30,40,50),
    "sortname"=>"Landowner",
    "hoverrows"=>true,
    "altRows"=>true,
    "height"=>"600",
    "autowidth"=>true,
    "toppager"=>true,
    "caption"=>"tblLandOwners"
));
// Set the url from where we obtain the data
$grid2->setUrl('tblLandOwners.php');

// Change some property of the field(s)
$grid2->setColProperty("LandownerID", array("editable"=>false,"hidden"=>false));
$grid2->setColProperty("LandOwner", array("required"=>true));
$grid2->setColProperty("LandOwnerNotes", array("label"=>"Landowner Notes","width"=>"200", "searchoptions"=>array("sopt"=>array('cn','bw','bn','nc')),"edittype"=>"textarea", "editoptions"=>array("rows"=>5, "cols"=>80),"editrules"=>array("required"=>false)));
$grid2->setColProperty("Status", array("required"=>true));
$grid2->setColProperty("CurrentlyAssignedTo", array("required"=>true,"editable"=>true));

// Enable filter toolbar searching
$grid2->toolbarfilter = true;
// Enable operation search
$grid2->setFilterOptions(array("searchOperators"=>true));

$grid2->setSelect("Status", "SELECT StatusID, Status as CM FROM tblLandOwnerStatus ORDER BY 2", false, true, false, array(""=>""));
$sql = "
    select LpcMemberID,
           concat(FirstName,' ',LastName)
    from tblLpcMembers
    where MemberInactive = 0
    ORDER BY 2";
$grid2->setSelect("CurrentlyAssignedTo", $sql, false, true, false, array(""=>""));
$grid2->navigator = true;
$grid2->setNavOptions('navigator', array("excel"=>false,"add"=>true,"edit"=>true,"del"=>true,"view"=>true, "search"=>true, "cloneToTop"=>true));
$grid2->setNavOptions('edit',array("height"=>"auto","dataheight"=>"auto","width"=>700,"closeAfterEdit"=>true));
$grid2->setNavOptions('add',array("height"=>"auto","dataheight"=>"auto","width"=>"auto","closeAfterAdd"=>true));
$grid2->setNavOptions('view',array("top"=>30,"left"=>30,"height"=>"auto","dataheight"=>"auto","width"=>800,"labelswidth"=>"20%"));

//Trigger toolbar with custom button
$search = <<<SEARCH
jQuery("#searchtoolbar").click(function(){
    jQuery('#grid')[0].triggerToolbar();
    return false;
});
SEARCH;
$grid2->setJSCode($search);

$txtbx = <<<TB
function(formid) {
    jQuery('#v_LandOwnerNotes').css({'height':'80px','white-space':'break-spaces'});
    jQuery('#viewhdgrid2').html('LandownerID '+$('#v_LandownerID span').html());
}
TB;
$grid2->setNavEvent('view','beforeShowForm',$txtbx);
// Enjoy
$grid2->renderGrid('#grid2','#grid2-toppager',true, null, null, true, false);

100

101

102

103

104

105

106

107

108

109

110

<?php

/* PHP jqSuite version 5.5.5 */

require_once '/var/www/html/jqSuite/jq-config.php';

// include the jqGrid Class

require_once ABSPATH."php/PHPSuito/jqGrid.php";

// include the driver class

require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php";

// include the datepicker

// require_once ABSPATH."php/jqCalendar.php";

// Connection to the server

$conn = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);

// Tell the db that we use utf-8

$conn->query("SET NAMES utf8");

// Create the jqGrid instance

$grid2 = new jqGridRender($conn);

$grid2->debug=true;

$grid2->showError = true;

// Write the SQL Query

$grid2->SelectCommand = "

select lo.LandownerID,

lo.LandOwner,

lo.LandOwnerNotes,

ls.Status,

case

when lm.LastName = 'UNASSIGNED' then 'UNASSIGNED'

else concat(lm.FirstName, ' ', lm.LastName)

end CurrentlyAssignedTo,

lo.LandOwnerAddress1,

lo.LandOwnerAddress2,

lo.LandOwnerCity,

lo.LandOwnerState,

lo.LandOwnerZip,

lo.HowToContact,

lo.MailingSalutation,

lo.AddressedTo

from tblLandOwners lo,

tblLpcMembers lm,

tblLandOwnerStatus ls

where lo.CurrentlyAssignedTo = lm.LpcMemberID and

lo.Status = ls.StatusID";

// Set output format to json

$grid2->dataType = 'json';

$grid2->table='tblLandOwners';

$grid2->setPrimaryKeyID('LandOwnerID');

// Let the grid create the model

$grid2->setColModel();

// Set some grid options

$grid2->setGridOptions(array(

"rowNum"=>23,

"rowList"=>array(30,40,50),

"sortname"=>"Landowner",

"hoverrows"=>true,

"altRows"=>true,

"height"=>"600",

"autowidth"=>true,

"toppager"=>true,

"caption"=>"tblLandOwners"

));

// Set the url from where we obtain the data

$grid2->setUrl('tblLandOwners.php');

// Change some property of the field(s)

$grid2->setColProperty("LandownerID", array("editable"=>false,"hidden"=>false));

$grid2->setColProperty("LandOwner", array("required"=>true));

$grid2->setColProperty("LandOwnerNotes", array("label"=>"Landowner Notes","width"=>"200", "searchoptions"=>array("sopt"=>array('cn','bw','bn','nc')),"edittype"=>"textarea", "editoptions"=>array("rows"=>5, "cols"=>80),"editrules"=>array("required"=>false)));

$grid2->setColProperty("Status", array("required"=>true));

$grid2->setColProperty("CurrentlyAssignedTo", array("required"=>true,"editable"=>true));

// Enable filter toolbar searching

$grid2->toolbarfilter = true;

// Enable operation search

$grid2->setFilterOptions(array("searchOperators"=>true));

$grid2->setSelect("Status", "SELECT StatusID, Status as CM FROM tblLandOwnerStatus ORDER BY 2", false, true, false, array(""=>""));

$sql = "

select LpcMemberID,

concat(FirstName,' ',LastName)

from tblLpcMembers

where MemberInactive = 0

ORDER BY 2";

$grid2->setSelect("CurrentlyAssignedTo", $sql, false, true, false, array(""=>""));

$grid2->navigator = true;

$grid2->setNavOptions('navigator', array("excel"=>false,"add"=>true,"edit"=>true,"del"=>true,"view"=>true, "search"=>true, "cloneToTop"=>true));

$grid2->setNavOptions('edit',array("height"=>"auto","dataheight"=>"auto","width"=>700,"closeAfterEdit"=>true));

$grid2->setNavOptions('add',array("height"=>"auto","dataheight"=>"auto","width"=>"auto","closeAfterAdd"=>true));

$grid2->setNavOptions('view',array("top"=>30,"left"=>30,"height"=>"auto","dataheight"=>"auto","width"=>800,"labelswidth"=>"20%"));

//Trigger toolbar with custom button

$search = <<<SEARCH

jQuery("#searchtoolbar").click(function(){

jQuery('#grid')[0].triggerToolbar();

return false;

});

SEARCH;

$grid2->setJSCode($search);

$txtbx = <<<TB

function(formid) {

jQuery('#v_LandOwnerNotes').css({'height':'80px','white-space':'break-spaces'});

jQuery('#viewhdgrid2').html('LandownerID '+$('#v_LandownerID span').html());

}

TB;

$grid2->setNavEvent('view','beforeShowForm',$txtbx);

// Enjoy

$grid2->renderGrid('#grid2','#grid2-toppager',true, null, null, true, false);

Creator

Topic

Viewing 3 replies - 1 through 3 (of 3 total)

Author

Replies

January 30, 2023 at 11:27 am #128085

guriddo.support

Participant

Hello,

It seems to me that you have a typo: In the select command you have

$grid2->SelectCommand = "
    select lo.LandownerID,
           lo.LandOwner,
           lo.LandOwnerNotes,
           ls.Status,
           case
               when lm.LastName = 'UNASSIGNED' then 'UNASSIGNED'
               else concat(lm.FirstName, ' ', lm.LastName)
           end CurrentlyAssignedTo,
           lo.LandOwnerAddress1,
           lo.LandOwnerAddress2,
           lo.LandOwnerCity,
           lo.LandOwnerState,
           lo.LandOwnerZip,
           lo.HowToContact,
           lo.MailingSalutation,
           lo.AddressedTo
    from tblLandOwners lo,
         tblLpcMembers lm,
         tblLandOwnerStatus ls
    where lo.CurrentlyAssignedTo = lm.LpcMemberID and
          lo.Status = ls.StatusID";
 
// Set output format to json

$grid2->SelectCommand = "

select lo.LandownerID,

lo.LandOwner,

lo.LandOwnerNotes,

ls.Status,

case

when lm.LastName = 'UNASSIGNED' then 'UNASSIGNED'

else concat(lm.FirstName, ' ', lm.LastName)

end CurrentlyAssignedTo,

lo.LandOwnerAddress1,

lo.LandOwnerAddress2,

lo.LandOwnerCity,

lo.LandOwnerState,

lo.LandOwnerZip,

lo.HowToContact,

lo.MailingSalutation,

lo.AddressedTo

from tblLandOwners lo,

tblLpcMembers lm,

tblLandOwnerStatus ls

where lo.CurrentlyAssignedTo = lm.LpcMemberID and

lo.Status = ls.StatusID";

// Set output format to json

but in the set primary Key you do

...
$grid2->setPrimaryKeyID('LandOwnerID');
..

...

$grid2->setPrimaryKeyID('LandOwnerID');

Try to set it like this:

..
$grid2->setPrimaryKeyID('LandownerID');
...

$grid2->setPrimaryKeyID('LandownerID');

...

Edit: Just in case you can force the primary key in script using the key=>true feature in colMode

...
$grid2->setColProperty("LandownerID", array("key"=>true,  "editable"=>false,"hidden"=>false));
...

...

$grid2->setColProperty("LandownerID", array("key"=>true, "editable"=>false,"hidden"=>false));

...

Kind Regards,

Will

This reply was modified 1 month, 3 weeks ago by guriddo.support. Reason: adding key=>true as option solution

Guriddo Support Team

February 2, 2023 at 1:04 am #128094

kgrogers

Participant

Will:

Good grief, all that because I forgot to capitalize an ‘m’!

All I had to do was correct the select statement for the proper case.

Thank you for your quick reply.

Ken

February 3, 2023 at 1:42 pm #128097

guriddo.support

Participant

Hello,

To overcome such problems you can set PDO to use lower case or upper case.

By example if you do :

// Connection to the server
$conn = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);
// Set the case in which to return column_names.
$conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);

// Connection to the server

$conn = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD);

// Set the case in which to return column_names.

$conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);

then no mater how you write your column names, but this depend on the needs and should be used with care.

Kind Regards,

Will

Guriddo Support Team

Author

Replies

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.

Registered Users: 12,205
Forums: 7
Topics: 10,938
Replies: 23,114
Topic Tags: 321

14 Users Online

Users: 9 Guests, 5 Bots

Stay connected with us in your favorite flavor!

Grid Update Uses rowID instead of Primary Key

Search Forum

Recent Replies

Forum Statistics

UserOnline