Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Not able to Insert Row using a data window

  1. Advice
0
Votes
Undo
  1. Floyd Edwards
    Floyd Edwards
  2. PowerBuilder
  3. Wednesday, 3 April 2019 17:13 PM UTC

Okay here is my issue, 

I am trying to either update a row or insert a row into the database using a data window. I have made sure that all the update properties are set correctly. Everything looks good.. then when I run the app, I get the following error.

 

SQLSTATE = 42000
-Microsoft SQL Server Native Client 10.0
-INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

-No changes made to database.

This is the SQL that the data window created:

INSERT INTO dbo.Year (YearID, SchoolYear, BeginDate, EndDate, ReportDate, CutOffDate, EvaluationStartDate, EvaluationCutOffDate, LoadStartDate, LoadEndDate )
VALUES ( 2020, '2020', '2019-07-01 00:00:00.000','2020-06-30 00:00:00.000','2020-06-30 00:00:00.000','2020-06-30 00:00:00.000','2020-03-01 00:00:00.000','2020-06-30 0:00:00.000','2020-03-01 00:00:00.000','2020-06-30 00:00:00.000')

I checked the DB and the Quoted Identifier is set to True....

When I go into SSMS and execute the SQL, it works.......

So I am thinking I may not have my DB connection set up correctly.... 

 

Any Advice?

is there solution to use font file name located in...
Capture Signature
Responses (4)
Leif Olsson
Leif Olsson Accepted Answer Pending Moderation
  1. Thursday, 25 April 2019 15:17 PM UTC
  2. PowerBuilder
  3. # 1

Hi, I stumbled on the same issue today. Finally I found that our problem is related to that we have started up a notification service from another solution than the PB-product that had the problem. The solution for the PB-product was to add DelimitIdentifier='Yes' to DbParm.

Links that lead me into that direction:
https://stackoverflow.com/questions/14806659/implementing-quoted-identifier-globally
https://stackoverflow.com/questions/10927132/sqldependency-causes-error-in-other-application

Comment
Leif Olsson
  1. Leif Olsson
  2. Friday, 26 April 2019 11:22 AM UTC
Today's discovery. One consequence of adding DelimitIdentifier = 'Yes' in DbParm is also that it affects the result when you pick out select from the datawindow. We need to think further - we modify sql in about 1500 locations in our large PB application ...
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 3 April 2019 20:03 PM UTC
  2. PowerBuilder
  3. # 2

Hi Floyd;

   Can you tell us ...

1) Is this In-Line SQL or from a DataWindow

2) Can you run this SQL through the DB Painter OK?

3) Does your SS login have the authority to insert into this table?

4) Can you post your DBMS SQLCA connection information?

Regards ... Chris

Comment
Floyd Edwards
  1. Floyd Edwards
  2. Wednesday, 3 April 2019 20:46 PM UTC
1) Is this In-Line SQL or from a DataWindow --- DataWindow



2) Can you run this SQL through the DB Painter OK? NO....



3) Does your SS login have the authority to insert into this table? Yes



4) Can you post your DBMS SQLCA connection information? I think I finally found the issue, in my PB ini file where the dbparm is located I had to add both the Quoted_Identifier= True and Ansi_Nulls= True to make it work.



My question now is "Why?" The database has these attributes set to True, but for some reason PB 2017 R3 does not have it set. I don't remember having to do this prior 2017.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 3 April 2019 20:58 PM UTC
Hi Floyd;

Excellent news ... I am super glad that you found the issue with SS!

AFAIK, nothing has changed with the "Quoted_Identifier" setting in PB 12.x from Sybase vs Appeon PB. I wonder though if your SS was changed to set that default to OFF instead of "single quote" (which is the normal default for most DBMS)?

Regards ... Chris
  1. Helpful
There are no comments made yet.
Floyd Edwards
Floyd Edwards Accepted Answer Pending Moderation
  1. Wednesday, 3 April 2019 17:58 PM UTC
  2. PowerBuilder
  3. # 3

PB 2017 R3

Server is MS SQL Server Ver. 12.0.06214.1

I am not sure how to find what version I am on with Native Client.

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 3 April 2019 20:00 PM UTC
Thanks Floyd ... That would be SS 2014 SP3
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 3 April 2019 17:53 PM UTC
  2. PowerBuilder
  3. # 4

Hi, Floyd - You're probably right. So, what are the connection properties you are using in SQLCA? What version of PB? What version of SQL Server? Native Client? You need to give us some information so we can help you.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.