Problems with primary key in SQL Server

Talk about implementing of customized templates

Moderators: ne_moj, zewa

metier
Expert
Expert
Posts: 56
Joined: Jan 24th, '09, 03:14

Problems with primary key in SQL Server

Postby metier » Jul 31st, '12, 22:29

Hi,

I am using the latest version (7.4.9) of the datagrid.
One of the field (prd_id) in a SQL Server table is defined as a primary key, integer and an auto-increment field.

On the datagrid, I am trying to implement "Edit/Delete" mode for that table and datagrid is complaining/warning that prd_id is not a primary key.

Also, when I click on "Edit" for a particular row ( ex: prd_id is 15 ) , the value of "f_rid" variable is shown as "-1" - this is really stumping me and I don't know where or how to fix this.

BTW, I am using PEAR and PHP PDO for the database connection.

Appreciate your help and thanks in advance.

Cheers.

administrator
Site Admin
Site Admin
Posts: 5943
Joined: Jan 7th, '09, 23:18
Contact:

Re: Problems with primary key in SQL Server

Postby administrator » Aug 1st, '12, 11:25

Did you define table and primary key for Edit mode too?
I mean this part of code:

Code: Select all

$table_name  = 'table_name';
$primary_key = 'primary_key';
$condition   = '';
$dgrid->SetTableEdit($table_name, $primary_key, $condition);

metier
Expert
Expert
Posts: 56
Joined: Jan 24th, '09, 03:14

Re: Problems with primary key in SQL Server

Postby metier » Aug 1st, '12, 16:11

Thanks for the reply.
Yes I did.

metier
Expert
Expert
Posts: 56
Joined: Jan 24th, '09, 03:14

Re: Problems with primary key in SQL Server

Postby metier » Aug 2nd, '12, 02:28

I have the same problem in the "Delete" mode too.

In debug mode, the errors are as follows:
POST: Array ( [f_mode] => delete [fp_mode] => [fp_rid] => [fp_sort_field] => [fp_sort_field_by] => [fp_sort_field_type] => [fp_sort_type] => [fp_page_size] => [fp_p] => [f_page_size] => 50 [f_p] => 1 [f_rid] => [f__operation_randomize_code] => h5l62sv9m467w6286u0a )
GET: Array ( [status] => )
Warnings:
1) Wrong parameter in [id]: Check this field carefully, it may be not a Primary Key!


Here's how I am defining edit/delete modes in the code:

Code: Select all

$modes = array(
   "add"     =>array("view"=>false, "edit"=>false, "type"=>"link"),
   "edit"    =>array("view"=>true, "edit"=>true,  "type"=>"link", "byFieldValue"=>""),
   "cancel"  =>array("view"=>true, "edit"=>true,  "type"=>"link"),
   "details" =>array("view"=>false, "edit"=>false, "type"=>"link"),
   "delete"  =>array("view"=>true, "edit"=>false,  "type"=>"image")
 );
$dgrid->SetModes($modes);


 $multirow_option = true;
 $dgrid->allowMultirowOperations($multirow_option);
 $multirow_operations = array(
   "delete"  => array("view"=>true),
   "details" => array("view"=>true),
    );
 $dgrid->setMultirowOperations($multirow_operations);

Seems like id value is not getting sent over when the checkbox is selected and "Edit or Delete" button is clicked.

Appreciate your help in getting this issue resolved.

administrator
Site Admin
Site Admin
Posts: 5943
Joined: Jan 7th, '09, 23:18
Contact:

Re: Problems with primary key in SQL Server

Postby administrator » Aug 2nd, '12, 07:23

We have to see this issue online with debug turned On - please prepare the page and send us a link.

metier
Expert
Expert
Posts: 56
Joined: Jan 24th, '09, 03:14

Re: Problems with primary key in SQL Server

Postby metier » Aug 3rd, '12, 02:08

Thanks for the response.

Unfortunately, I cannot send you a page as the web site is on intranet.

BTW - can you please give any pointers as to where I can find the methods that get the id value ( when either "delete" or "edit" is clicked )
I tried looking at the javascript "dg.js"- but couldn't figure it out.

Appreciate your help.

metier
Expert
Expert
Posts: 56
Joined: Jan 24th, '09, 03:14

Re: Problems with primary key in SQL Server

Postby metier » Aug 3rd, '12, 18:48

I downgraded datagrid version to 6.4.1 and now the "Delete" mode seems to be working. The "Edit" mode still does not work. It gets the "checked" ID and passes it to the query but no results.

The "Edit" mode shows a page with no fields. I am attaching a snapshot of the page.

Here's what it looks like in debug mode, when I click on "Edit" for one row.

Retrieve data for current mode:
select sql (total: 28) select id,col1,col2,col3 from testable WHERE 1=1 ORDER BY 1 ASC

Retrieve data for current mode:
select sql (total: 1) SELECT testable.* FROM testable WHERE id = '121' ORDER BY id DESC

POST: Array ( [prs_mode] => edit [prs_rid] => 121 [prs_page_size] => 20 [prs_p] => 1 )
GET: Array ( )
Warnings:
1) Wrong parameter in [id]: Check this field carefully, it may be not a Primary Key!

Now , the "Delete" works but the transaction is not getting COMMITTED to the database.
I tried adding commit to one of the methods called "DeleteRow" in datagrid.class.php file, but that did not help.
What is the syntax to pass a COMMIT query in datagrid? I tried " $this->dbHandler->query("COMMIT") and also mssql_query("COMMIT") and also $this->dbHandler->commit() "

Here's the snippet of the function:
protected function DeleteRow($rid){
$req_operation_randomize_code = $this->GetVariable("_operation_randomize_code", true);
if(!$this->CheckF5CaseValidation($req_operation_randomize_code)) return false;
if(!$this->CheckSecurityCaseValidation("delete", "delete", "deleting")) return false;

$file_fileds_array = array();
$this->rids = explode("-", $rid);
$keys_list = "";
$sql = "DELETE FROM ".$this->tblName." WHERE ".$this->primaryKey." IN ('-1' ";
foreach ($this->rids as $key){
$key = (count($this->rids) > 1) ? $this->DecodeParameter($key) : $key;
$keys_list .= ($keys_list != "") ? ",".$key : $key;
$sql .= ", '".$key."' ";
}
$sql .= ");";

$dSet = null;
if(!$this->isDemo){
$this->PrepareFileFields($file_fileds_array, $sql);
$dSet = $this->dbHandler->query($sql);
$comm = 'COMMIT';
$this->dbHandler->query($comm);
//$this->dbHandler->commit();
}
$affectedRows = $this->dbHandler->affectedRows();
//mssql_query("COMMIT");
if($affectedRows > 0){
//mssql_query("COMMIT");
echo "Successfully deleted\n";
$this->actMsg = ($this->arrDgMessages['delete'] != "") ? $this->arrDgMessages['delete'] : $this->lang['deleting_operation_completed'];
if(isset($_SESSION)) { $_SESSION[$this->uniquePrefix.'_operation_randomize_code'] = $req_operation_randomize_code; }
Attachments
edit_mode.PNG
the resulting page after "Edit" link is clicked
edit_mode.PNG (12.96 KiB) Viewed 1883 times

administrator
Site Admin
Site Admin
Posts: 5943
Joined: Jan 7th, '09, 23:18
Contact:

Re: Problems with primary key in SQL Server

Postby administrator » Aug 5th, '12, 08:32

We have to debug the script on your special settings, but it's too hard when we cannot access the code.

Select sql (total: 1) SELECT testable.* FROM testable WHERE id = '121' ORDER BY id DESC
means that the script is working ok. You have to check what happens in DrawColumnar() method.
Why it doesn't draw columns.

metier
Expert
Expert
Posts: 56
Joined: Jan 24th, '09, 03:14

Re: Problems with primary key in SQL Server

Postby metier » Aug 6th, '12, 02:13

Thanks Admin for your help.


Return to “Add/Edit/View Mode Templates Implementing”