bug insert syntax on mssql

Questionsbug insert syntax on mssql
Norman asked 5 years ago

Hi,

i think i found a bug when working with mssql.
EDIT -> OK
DELETE -> OK
INSERT -> BUG. i think its syntax always skip the primary key/first field?
i'm using sql server 2012.

—THE SYNTAX——————————————————
include PHPGRID_LIBPATH."inc/jqgrid_dist.php";
$g = new jqgrid();
$g->select_command = "SELECT EmployeeID,WeekEndDate,RBU,RBT FROM MsSecurityWeekEnd";
$g->table = "MsSecurityWeekEnd";
$out = $g->render("dg1");
—THE SYNTAX——————————————————

—THE ERROR MESSAGE———————————————–
Couldn't execute query. The statement has been terminated. – INSERT INTO MsSecurityWeekEnd (WeekEndDate,RBU,RBT) VALUES ('2014-01-01','admin','2014-01-01')
—THE ERROR MESSAGE———————————————–

—THE DB SCRIPT—————————————————
USE [HRD]
GO

/****** Object: Table [dbo].[MsSecurityWeekEnd] Script Date: 7/2/2014 6:37:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MsSecurityWeekEnd](
[EmployeeID] [varchar](20) NOT NULL,
[WeekEndDate] [datetime] NOT NULL,
[RBU] [varchar](20) NULL,
[RBT] [datetime] NULL,
CONSTRAINT [PK_MsSecurityWeekEnd] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC,
[WeekEndDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
—THE DB SCRIPT—————————————————

thx

3 Answers
Norman answered 5 years ago

Hi again,

this is really the problems with the 1st column on mssql DBTYPE.
i try to switch the field.
$g->select_command = "SELECT WeekEndDate,EmployeeID,RBU,RBT FROM MsSecurityWeekEnd";

then i get the error message :
"Couldn't execute query. The statement has been terminated. – INSERT INTO MsSecurityWeekEnd (EmployeeID,RBU,RBT) VALUES ('4','4','2014-01-01')"

where can i fix the code?

thx

Norman answered 5 years ago

Hi again,

sorry being impatient.
so i've debugged the "jqgrid_dist.php".
block the line 1416 and 1417 :
// skip first column while insert, unless autoid = false
//if ($k == $pk_field && $this->options["colModel"][0]["autoid"] !== false)
// continue;

and it work again. so the conclusion is that "autoid" is falsely detect my field.
just want to know if i'm doing alright for this debug? will it affect other logic?

need your guide to fix the code so it won't affect strangely to other logic.

thx

Abu Ghufran answered 5 years ago

Hello,

It should work fine with commenting these lines.
However, there are few recommendations.

First column of the grid must have unique content.
So you should start with

$g->select_command = "SELECT EmployeeID,WeekEndDate,RBU,RBT FROM MsSecurityWeekEnd";

And second, it asssumes that first column is auto increment so skipped from insert sql.
If you want it to be a part of grid insert, set

$col["autoid"] = false;

Your Answer

2 + 11 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

We are sorry that this post was not useful for you!

Let us improve this post!