I've also seen this happen when using MS SQL Server versions earlier than
7.0 and including a MONEY datatype column in the updateable columns list of
a DW. Earlier MS SQL Server versions store and manipulate MONEY data to 4
decimal places, but will only ever return 2 decimal places with a SELECT
statement. PB will identify the column as either decimal(4) or decimal(2),
but will only ever be given 2 decimal places by the server.
So if you retrieve a row where the money column's value is 1.4989 in the
database (i.e. some other process generated this value, e.g. a calculation
in a stored procedure or trigger), SQL Server will send this column as 1.49
(or 1.50 - I can't remember if it rounds or truncates). If this column is
updateable, and you are including updateable columns in the update where
clause, then when you try to update this row, part of the where clause will
be 'colname = 1.49'. However, because the real value is 1.4989, the record
is not found and PowerBuilder then generates the 'row changed between
retrieve and update' error.
To check for this if you do have a MONEY datatype in your DW, copy the DW's
SQL statement into iSQL, and convert the money column(s) to a float or a
decimal, and see how many decimal places are there for the offending row.
There are a couple of solutions - use decimal datatypes instead of money.
Or, as mentioned earlier, change the DW's update where clause settings. Or,
even more drastically, upgrade to a newer version of MS SQL Server (or a
different DBMS ;-)>
Cheers
Dunc
> -----Original Message-----
> From: Daniel Coppersmith [SMTP:[EMAIL PROTECTED]]
> Sent: 30 August 2000 13:32
> To: rajendras; PFCSIG
> Subject: Re: PFCSIG Datawindow Error while updating
>
> Not to belabor the point, but it is not just key fields. If the
> datawindow update property says to include modified (or modifiable)
> columns in the where clause, you need to be concerned about those changing
> as well.
>
> ----- Original Message -----
> From: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ;
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ;
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ;
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> Sent: Tuesday, August 29, 2000 11:01 PM
> Subject: RE: PFCSIG Datawindow Error while updating
>
>
> This problem occurs when u have retrieved a record for a given key
> say for e.g. invoice_no and the invoice no. is changed by u or by some
> other user prior to your save.
>
> First of all the key fields should never be allowed to be changed.
> In case u are allowing then in Rows-> Update properties change the setting
> of update to 'KEY VALUES' (But this is a little slow then the other
> settings).
>
> Frankly speaking insure that the Key fields are not getting changed.
>
>
> Regards,
> Rajendra Shetty
> (Programmer/Analyst)
>
> Mastek Ltd.
> (CS - IIS Dept.)
> Unit 106, SDF-4,
> SEEPZ, Andheri (E),
> Mumbai - 400 096.
> India.
> Phones : +91-22-829 0635 Ext : 1050
>
>
>
> -----Original Message-----
> From: Daniel Coppersmith [ <mailto:[EMAIL PROTECTED]>]
> Sent: Tuesday, 29 August, 2000 8:24 AM
> To: Griffith, Daniel M.; 'Sawant Prasad'; [EMAIL PROTECTED]
> Subject: Re: PFCSIG Datawindow Error while updating
>
>
> To add to Dan's ideas, it can also happen if you use setItemStatus
> to set a
> row or columns status to a new value (i.e. notModified! or
> DataModified!).
> Dan's SQL Spy idea will be your best bet. If you are getting
> nothing but
> ?'s where there should be values in the SQL, turn off Binding when
> you
> connect to the database. It will slow down the SQL but for testing
> it's
> worth it.
>
> D
>
> ----- Original Message -----
> From: Griffith, Daniel M. <[EMAIL PROTECTED]>
> To: 'Sawant Prasad' <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, August 29, 2000 7:53 AM
> Subject: RE: PFCSIG Datawindow Error while updating
>
>
> > This doesn't sound like a PFC question, but a basic PB one.
> But, it
> > might help you to enable the PFC SQLSpy to determine the WHERE
> clause that
> > is being executed.
> >
> > It sounds like the DataWindow Update Properties are not set
> consistently
> > with the way your application is behaving. Basically, when PB
> performs
> the
> > Update() function, it executes a SQL UPDATE statement. The DW
> Update
> > Properties specify which columns to include in the WHERE clause
> for that
> > UPDATE.
> >
> > It is there to help you detect when two people are attempting
> to update
> > the same row of data. Image two people do retrieves of the same
> row.
> Some
> > time later, the first person updates the data. Some time after
> that, the
> > other person attempts to update the data. The WHERE clause
> determines
> which
> > row to update, and depending on the settings, the UPDATE statement
> may
> fail
> > because the row is not found. That leads to the 'row changed
> between
> > retrieve and update' message.
> >
> > I'm assuming you currently have the update properties set to
> include all
> > columns.
> > Your options include:
> > 1. Change the WHERE clause to include only modified columns.
> This way,
> > you will only encounter the message when two people edit the same
> columns
> in
> > the same row. (With all columns, you get the message even if the
> persons
> > update different columns on the same row.)
> > 2. Change the WHERE clause to include only key columns. This
> way, you
> > will only encounter the message when the first person deletes the
> row that
> > the second person later tries to update. Note that many people
> think that
> > setting it to 'key columns' eliminates the problem--it does not.
> >
> > It is also possible that embedded SQL in your code is updating
> the row
> > before the DataWindow's update function is executed. This would be
> the
> same
> > as if another user had updated the data.
> > Another possibility, less common, is that you retrieve the
> data, some
> > column(s) that are in the UPDATE WHERE clause change in the
> DataWindow,
> your
> > code performs a ResetUpdate(), again modifies the column(s) in the
> WHERE
> > clause and *then* the Update() function is called. After a
> ResetUpdate(),
> > PB will use the values in the DW's "Original" buffer in the WHERE
> clause.
> > Because your code changed them, they no longer match what's on the
> server,
> > which again leads to the UPDATE statement failing.
> > Actually, it's not common in general, but I do see it when
> developers
> > call the Update() function with no parameters, instead of
> Update(false,
> > false) and then managing the flags themselves.
> > Another somewhat common defective technique that leads to this
> is
> setting
> > a row's attributes after adding a new row, then generating a
> unique
> primary
> > key id, then calling resetUpdate() without writing the "blank" row
> to the
> > server. This would prevent a "do you want to save prompt" from
> appearing
> > unless the user changes something. The problem is that once the
> user
> > changes something, and the Update() function is called, PB treats
> it like
> an
> > UPDATE, instead of an INSERT. And since the row was never written
> to the
> > server, the UPDATE fails (even if you have the WHERE clause
> properties set
> > to key only).
> >
> > More information on this can be found in the PowerBuilder
> online books
> > and training materials.
> >
> > Have a nice day,
> > --dang
> >
> >
> > -----Original Message-----
> > From: Sawant Prasad [ <mailto:[EMAIL PROTECTED]>]
> > Sent: Tuesday, August 29, 2000 05:19
> > To: [EMAIL PROTECTED]
> > Subject: PFCSIG Datawindow Error while updating
> >
> >
> > Can u please help me.
> > When i am updating a datawindow i get the following error.
> >
> > Datawindow Error
> > Row changed between retrieve and update.
> >
> > Can anyone provide me the solution at the earliest.
> >
> > This error is not regular. It comes rarely.
> >
> > Thanking you in advance
> >
> > Prasad Sawant
> > email - [EMAIL PROTECTED]
> > > [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE
> COMMANDS,
> ADDRESS
> > > A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE:
> help
> pfcsig
> > > SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
> >
>
> > [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE
> COMMANDS, ADDRESS
> > A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help
> pfcsig
> > SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
>
Barclays Private Banking is a division of Barclays Bank Plc.
Registered in England, no. 1026167 and authorised in the
U.K. under the Banking Act 1987. Registered Office: 54
Lombard Street, London, EC3P 3AH. Investment services
provided in the U.K. by Barclays Private Bank Limited,
regulated by the Securities and Futures Authority. Registered
no. 1957770 at the above address. The availability of
products and services may be limited by the applicable laws
and regulations in certain jurisdictions.
********************************************************************
Internet communications are not secure and therefore the Barclays Group does
not accept legal responsibility for the contents of this message. Any views
or opinions presented are solely those of the author and do not necessarily
represent those of the Barclays Group.
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS, ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
