Repeated Rows Inserted

[Former Member]

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

[Breck_Carter]

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

[VolkerBarth]

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

[Former Member]

i_mess = f_confirm("Question?", "Do you wish to save your changes?", b_new, b_print )
CHOOSE CASE i_mess
    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
            Else
                //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
        ELSE
            s_mass = ""
            GoTo InError
        END IF
        //  
    CASE 2
        dw_1.SetFocus()
        Return
END CHOOSE

[Former Member]

I am sure that no triggers on this table

[VolkerBarth]

@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...)

[Breck_Carter]

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!

[chris_keating]

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.

[Former Member]

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

wf_save()
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 

[Former Member]

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.
        Beep(2)
        MessageBox("Error", "Please specify value for Code ", StopSign!, OK!, 1)
        MessageBox("Error",sqlerrtext)
        MessageBox("Error",sqlsyntax)
ElseIF sqldbcode = -193  Then // Required value is NULL.
        Beep(2)
        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)
END If
RETURN 1 // Do not display system error message