topic Re: Database Update Problem in Application Development and Automation Discussions

Database Update Problem

Former Member — Mon, 29 Jan 2007 03:28:32 GMT

Hi All,

I am having a table control on a screen, I am updating the table control rows and then saving , I want all the rows in the table should get modified in the database table, for this I am using the code below but it is giving a runtime error 'SAPSQL_ARRAy_INSERT_DUPREC.

Please Suggest.

form update_database.

data: lt_del_rows type table of ZEXC_REC,

lt_ins_keys type g_verifier->zexc_rec_keys,

l_ins_key type g_verifier->ZEXC_REC_key,

ls_ZEXC_REC type ZEXC_REC,

ls_outtab like line of gt_outtab,

lt_instab type table of ZEXC_REC.

§8.When all values are valid, use your internal tables
to update your table on the database.

First delete lines in data base according to the
keys you remembered within 'g_verifier'.
Imagine a user deleted a row and then entered one with
the same key fields like the deleted ones.
Then both tables (deleted_rows, inserted_rows) have got
an entry of this row.
So if you first inserted the new rows in the data base
and then deleted rows according to 'deleted_rows'
The reentered rows would be lost.

1.Delete Lines:

call method g_verifier->get_deleted_rows

importing deleted_rows = lt_del_rows.

delete ZEXC_REC from table lt_del_rows.

2.Insert Lines:

call method g_verifier->get_inserted_rows

importing inserted_rows = lt_ins_keys.

loop at lt_ins_keys into l_ins_key.

read table gt_outtab into ls_outtab

with key LIFNR = l_ins_key-LIFNR

DOCNO = l_ins_key-DOCNO

DOCTYP = l_ins_key-DOCTYP

HIERNO = l_ins_key-HIERNO

matnr = l_ins_key-matnr.

if sy-subrc eq 0.

move-corresponding ls_outtab to ls_ZEXC_REC.

append ls_ZEXC_REC to lt_instab.

endif.

endloop.

insert ZEXC_REC from table lt_instab.

commit work.

Insert first default row for ARE1 document

§9.Refresh your internal tables.

call method g_verifier->refresh_delta_tables.

endform.

Thanks.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 03:45:03 GMT

i think you are updating key fields with duplicate values. so you can insert like this it may work.

insert ZEXC_REC from table lt_instab accepting duplicate keys.

it may work and one thing your internal table should be like the database table with all the fields in correct order.

regards

shiba dutta

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 03:55:48 GMT

Hi,

Use MODIFY statement instead of INSERT because INSERT tries to insert new records,

whereas,

Modify checks if there is a record with the primary keys in your internal table, if there exists an entry, it will update the entry. If does not find it then it would create a new entry, so any time you will never experience the Duplicate entry error.

Regards

Subramanian

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 03:55:53 GMT

Thanks a lot Shiba for ur reply,

I tried using ur idea but now it is not inserting only in the database. I want the previous rows to get deleted and new rows with modified values to get inserted in the database.

Please suggest.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 03:59:15 GMT

Hi Subramanian ,

I tried modify but it is not getting modified in the database after save.

Is there any problem with the code.

Please suggest.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:01:21 GMT

Hi,

Use the modify statement as I have explained earlier.

Regards

Subramanian

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:06:57 GMT

the thing is if you try to duplicate your key values then i think it is not possible because it may leads to database inconsistancies. the key fields must be unique in database.

regards

shiba dutta

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:07:45 GMT

Hi Rahul,

In your code you are straight away inserting the internal table records into the database table. You need to do two thnigs before inserting the lines into the DB table...

1. Check for SY-SUBRC to confirm if the previous records are deleted successfully.

2. Before inserting records, sort internal table and use statement DELETE ADJACENT DUPLICATE. If there are any numeric fields in the table and you want the total to appear in one entry, use COLLECT statement and get all unique key data in one internal table and then insert the records in the DB table.

Hope this will solve your problem.

Susanth

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:22:15 GMT

Hi Again,

I tried using sy-subrc, its 0, but<b> lt_del_rows</b> in the following line of code is blank it is not having any data, so it is just deleting blank row i guess, i want to delete all the rows of the table control.

delete ZEXC_REC from table lt_del_rows.

Please suggest.

Message was edited by:

Rahul Ghan

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:34:35 GMT

Hi,

Do a acheck before delete.

If NOT lt_del_rows IS INITIAL.
 delete ZEXC_REC from table lt_del_rows.
ENDIF.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:35:36 GMT

Can anybody please tell , what following statement does,

call method g_verifier->get_deleted_rows

importing deleted_rows = lt_del_rows.

and what is g_verifier.

Thanks

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 04:44:39 GMT

Hi Judith,

I tried ur code , it is not going inside the if statement , lt_del_rows is initial,

before that it is calling the function

call method g_verifier->get_deleted_rows

importing deleted_rows = lt_del_rows.

and the definition of the function is,

method get_deleted_rows.

deleteted_rows = me->deleted_rows.

endmethod.

Can u please tell what above function is doing?

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 05:00:45 GMT

Rahul,

'me' is an instance of a class created in your program. Can you provide all your program code....

FYI, the internal table name for deletion should be one you defined while creating the table control....

Susanth.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 05:03:32 GMT

You have to write like this

DATA : o_alvgrid          TYPE REF TO cl_gui_alv_grid,

  METHOD handle_user_command.
* In event handler method for event USER_COMMAND: Query your
*   function codes defined in step 2 and react accordingly.

    CASE e_ucomm.

      WHEN 'FCODE'.
        CALL METHOD o_alvgrid->get_selected_rows
          IMPORTING
            et_index_rows = i_selected_rows

Have you tried similar to this.

Check for the parameters in get_deleted_rows.

The function should retrieve teh deleted rows when you press a delete button.

So write the code in the user_command for delete functioanlity.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 05:10:23 GMT

Hi Susanth,

I have already posted the code above,

I am using a grid in the screen declared using the statement

g_custom_container type ref to cl_gui_custom_container,

g_grid type ref to cl_gui_alv_grid,

I am having more than one row in this grid and I want to save changes to all these rows in the database at the click of save button.

I am using object based programming here for inserting and deleting rows,

Please suggest.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 05:21:11 GMT

You have to write like this


************************************************************************
INCLUDE <icon>.

* Predefine a local class for event handling to allow the
* declaration of a reference variable before the class is defined.
CLASS lcl_event_receiver DEFINITION DEFERRED.
CLASS cl_gui_container DEFINITION LOAD.

DATA : o_alvgrid          TYPE REF TO cl_gui_alv_grid,
       i_selected_rows TYPE lvc_t_row.


CLASS lcl_event_receiver DEFINITION.

*   event receiver definitions for ALV actions
  PUBLIC SECTION.
    CLASS-METHODS:
* Status bar
       handle_user_command
        FOR EVENT user_command OF cl_gui_alv_grid
            IMPORTING e_ucomm,
ENDCLASS.
CLASS lcl_event_receiver IMPLEMENTATION.

  METHOD handle_user_command.
* In event handler method for event USER_COMMAND: Query your
*   function codes defined in step 2 and react accordingly.

    CASE e_ucomm.

      WHEN 'FCODE'.
        CALL METHOD o_alvgrid->get_selected_rows
          IMPORTING
            et_index_rows = i_selected_rows

        IF i_selected_rows[] IS INITIAL.
          MESSAGE i153 WITH text-009.
          LEAVE LIST-PROCESSING.
        ENDIF.

  WHEN OTHERS.

    ENDCASE.
  ENDMETHOD.

Have you tried similar to this.

Check for the parameters in get_deleted_rows.

The function should retrieve teh deleted rows when you press a delete button.

So write the code in the user_command for delete functionality.

Make sure all these are there in ur program.

Also SET HANDLER FOR user command should be there.

Verify whether get_deleted_rows is available in the method.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 05:39:53 GMT

Hi Judith,

I want to update the changes done in the grid into the database by any way,

I was trying the way to first delete all the rows and then insert all the rows from the grid into the database with updated values.

Is it possible to update the contents of the batabase table from the contents of the grid, if yes please tell the way to do it.

Thanks a lot for ur help.

Re: Database Update Problem

Former Member — Mon, 29 Jan 2007 07:20:03 GMT

Hi,

You can update dbase table from grid.

You can do the coding here

 IF NOT  i_selected_rows[] IS INITIAL.

Then depending upon ur requirement u can do the coding here, for updation and deletion.

ENDIF.

Anyway u can do the processing after selecting rows from the grid.

Now the i_selected_rows table will be having some values.

If you like to update table then do that here.

hope this helps.