CODEWITHSUNDEEP
powerbuilder
Frankie
Frankie

Reputation: 2265

PowerBuilder DataWindow using insert when update is needed

The issue I'm having is: I have a DataWindow that on window open checks to see if any rows exist and if not, I manually insert a row. At some point, a row is being inserted into the table before I call update on the DataWindow. When I call update, a row already exists and my updates cannot be saved because it is using insert instead of update. Is there a way to alleviate this?

dw_dates.SetTransObject(SQLCA);
dw_dates.Retrieve(gs_facility_pfi, is_pcn);    

if(dw_dates.rowcount() = 0) then
    int row;
    row = dw_dates.InsertRow(1);    
    dw_dates.setitem(row, 'patient_ctrl_num', is_pcn);
    dw_dates.setitem(row, 'pfi_num', gs_facility_pfi);  
end if

Upvotes: 1

Views: 8222

Answers (4)

paxrex
paxrex

Reputation: 41

So what you're saying is that some other user has inserted the row. Then you must do another check just before you call update() on datawindow. The best solution is to use stored procedure update and to check there if row exists and decide if you will do INSERT or UPDATE. But then again, if you do UPDATE you will overwrite someone else's data.

Upvotes: 0

keshava
keshava

Reputation: 1

Not sure if one would be using "row = dw_dates.InsertRow(1); ", if you want a dummy row then try using row = dw_dates.InsertRow(0); This would insert the row at the end. Also you may want to use the SetRow or ScrolltoRow in-order to make it the current row.

Upvotes: 0

Terry
Terry

Reputation: 6215

PowerBuilder isn't going to know automagically whether or not there is a row there with the same key. The DataWindow generates INSERT or UPDATE statements based on the row status (see GetItemStatus() in help for details).

It's not really clear what your requirement is. If you want to either insert a new record or overwrite the existing record, I'd suggest a stored procedure update from the DataWindow that determines if whether the INSERT or UPDATE is required and acts accordingly. If you want this client to always INSERT a new record, and adjust the primary key values appropriately so you won't overwrite or conflict with existing data, I'd suggest look to see whether your DBMS supports identity or sequence values for your key column, and look at the Identity Column feature on the DataWindow's Update Properties dialog.

Good luck,

Terry.

Upvotes: 1

Jason V
Jason V

Reputation: 895

You need to answer a business question first. Do you want to lose the other person's update?

If you are getting the DW msg: Rows changed between Retrieve and Update... that is a valid msg. If you look in your datawindow, you'll notice by default the columns used by the Update are all of the editable columns. You can change that to just the primary key. This would allow your update to "work", but it will also overwrite the other person's changes.

It sounds like your PK is too generic. You might want to look at a unique, sequence driven approach.

Jason

Upvotes: 0

Related Questions