Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I use datawindow update function to insert data into table after i get id max but some time the follwoing happen:

id  | field a | field b | field c |
1       4          6         7
2       4          6         7

I check may code and debug without insert repeat

i can't avoid the insertion of duplicate rows I am using powerbuilder 11 ,and SQLAnywhere 12

asked 09 Feb '14, 06:30

eraqaty's gravatar image

accept rate: 0%

edited 10 Feb '14, 05:14

Volker%20Barth's gravatar image

Volker Barth


Show us the PowerBuilder code; please copy and paste the exact code, do not edit it.

(09 Feb '14, 10:00) Breck Carter
Replies hidden

Besides that, may there be an INSERT trigger that may duplicate a row internally?

(10 Feb '14, 04:41) Volker Barth
i_mess = f_confirm("Question?", "Do you wish to save your changes?", b_new, b_print )
    CASE 1
        l_doc_no = wf_save()//to get max id
        IF l_doc_no <= 0  Then 
            s_mass = "Error In Save Function 190"
            GoTo InError
        End IF
        IF dw_1.Update( True, False ) = 1 Then          
            Commit Using SqlCa ;            
            IF SQLCA.SQLCode <> 0 THEN
                s_mass = ""
                GoTo InError
                //MessageBox ("Saved", "Receipt  Number "  + String( l_doc_no ) )
                ls_msg[1] = String( l_doc_no )
                inv_msg.of_messagebox('000014', ls_msg)
                If b_print Then
                    This.Event ue_save_print(l_doc_no)
                End If  
                If b_new Then
                    This.Event Post u_add()
                End If
            END IF
            If ids_LGMod.Update() > 0 Then
                Commit Using Sqlca;
            End If
            s_mass = ""
            GoTo InError
        END IF
    CASE 2
(10 Feb '14, 04:43) eraqaty
Replies hidden

I am sure that no triggers on this table

(10 Feb '14, 04:45) eraqaty

@eraqaty: I have formatted your code - it's quite easy within a comment by enclosing your code with a <pre> ... </pre> tag pair. (It's even simpler for questions/anwers by using the "CODE" button from the edit toolbar...)

(10 Feb '14, 05:17) Volker Barth

Please show us the code for wf_save() and u_add(), and explain how ids_LGMod is used.

The second argument in the dw_1.Update( True, False ) call overrides the resetflag default value of True, setting it to False, which means that the row update flags will not be reset even if the call works... which means a second call to Update will do the same work over again. This is VERY unusual; 99.99% of the world's calls to Update look like this: dw_1.Update().

The code you have posted is quite complex... there are calls to several other methods (wf_save(), u_add(), ids_LGMod.Update()) for which the code is not shown.

Given the history of PowerBuilder and SQL Anywhere, the probability that this inserting-duplicate-rows-problem is caused by a bug in either product is 0.000001%.

It is much more likely that the application code contains another Update() call that is inserting the duplicate... or that the code shown is being executed twice, perhaps via accidental recursion.

It is a fundamental problem of most "modern" object-oriented code that the logic to do one simple thing (update a datawindow) is spread across several, sometimes dozens, of separate methods located in different libraries. If you show us ALL the related methods, perhaps we can help you find the problem. Good luck!

(10 Feb '14, 09:00) Breck Carter

Assuming that you have inherited the datawindow object, can you also post the error information reported in the DBError event? This error might explain in more detail the start of the behaviour you are seeing. If that is not available, perhaps a -zr sql (can be enabled from code) to see what error is being reported.

Also, is this table updated by more than one connection at a time? If so, you would be better served using autoincrement values rather than calculating a max() value.

(10 Feb '14, 09:16) Chris Keating
Replies hidden

"ids_LGMod " only datastore to update in onther table // Update properties Key and Updatable Columns ,Use Update //

Long                l_row_2, l_row_1, i
String          s_hld, s_paper, s_op_type, s_parent, s_book
String          s_inv, s_no , ls_res
date                d_date
time                t_time
dwItemStatus    l_status
int             i_count
DataStore      lds_res
double          l_doc_num

l_row_1 = dw_1.GetRow() //

If is_editmode = "A" Then s_no = f_serial("rec_ser") //only get max l_doc_num = double(s_no) IF l_doc_num <= 0 Then Return l_doc_num dw_1.Object.rec_no[l_row_1] = l_doc_num Else // l_doc_num = Long( Mid( dw_1.Object.ra_number[l_row_1], 4) ) l_doc_num = dw_1.Object.rec_no[l_row_1] End IF
// Return l_doc_num

//u_add() long l_row String ls_res, ls_curr l_row = dw_1.getrow() dw_1.object.rec_station[l_row] = w_main.is_station is_editmode = 'A' dw_1.object.ccy_code[l_row] = ls_curr dw_1.Object.ra_receipt_user_id[l_row] = w_main.is_usrid dw_1.Object.rec_date[l_row] = datetime(f_server_datetime()) dw_1.Object.ra_receipt_entry_user[l_row] =w_main.is_usrid dw_1.Object.ra_receipt_entry_date[l_row] =datetime(f_server_datetime()) IF f_get_about("rate_open")='yes' Then dw_1.Object.ra_receipt_ccy_ext_rate.protect =0 dw_1.object.ra_receipt_ccy_ext_rate.background.mode = 0 Else dw_1.Object.ra_receipt_ccy_ext_rate.protect = 1 dw_1.object.ra_receipt_ccy_ext_rate.background.mode =1 End IF

(10 Feb '14, 09:27) eraqaty

yes this table used by more users with more than one connection but please note that 2 duplicate rows have different id (max) but have the same data in all other columns DBError as following:

IF sqldbcode = -195  Then // Required value is NULL.
        MessageBox("Error", "Please specify value for Code ", StopSign!, OK!, 1)
ElseIF sqldbcode = -193  Then // Required value is NULL.
        MessageBox("Error", " Key is not Unigue !!", StopSign!, OK!, 1)       

ElseIF sqldbcode <> 0 Then // Code to handle other errors Beep(2) MessageBox("Errro " ," Error = " + String(sqldbcode ) + " " + sqlerrtext,StopSign!, OK!, 1)


RETURN 1 // Do not display system error message

(10 Feb '14, 09:45) eraqaty
showing 4 of 9 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 09 Feb '14, 06:30

question was seen: 3,544 times

last updated: 10 Feb '14, 09:45