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?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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); |
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top