Write to oracle without repeating name

Asked

Viewed 51 times

0

I have this block inside a record label that records a representative, which comes from a screen in ASP Classic.

v_qtd_rep := ts_obtem_dados_no_xml(v_no_completo,'QTD_REP');

            for i in 1..v_qtd_rep loop

            --delete from prestador_representante where cod_prestador_ts = v_COD_PRESTADOR_TS;

            v_COD_PRESTADOR_TS   := ts_obtem_dados_no_xml(v_no_completo,'COD_PRESTADOR_TS');
            v_NOME_REPRESENTANTE := ts_obtem_dados_no_xml(v_no_completo,'NOME_REP_' || i);
            v_DDD_REPRESENTANTE  := ts_obtem_dados_no_xml(v_no_completo,'DDD_REP_' || i);
            v_TEL_REPRESENTANTE  := ts_obtem_dados_no_xml(v_no_completo,'TEL_REP_' || i);
            v_ID_REPRESENTANTE   := ts_obtem_dados_no_xml(v_no_completo,'ID_REPRESENTANTE_' || i);

            select prestador_representante_seq.nextval into vId from dual;

            --select nom_representante from ts.prestador_representante where cod_prestador_ts = v_COD_PRESTADOR_TS;

            --if v_ID_REPRESENTANTE is null then

              if v_NOME_REPRESENTANTE is not null and v_DDD_REPRESENTANTE is not null and v_TEL_REPRESENTANTE is not null and v_ID_REPRESENTANTE is null then
                      insert into ts.prestador_representante(id_representante, cod_prestador_ts, nom_representante, ddd_representante, tel_representante, dt_atu, cod_usuario_atu)
                                                       values(vId, v_COD_PRESTADOR_TS, v_NOME_REPRESENTANTE , v_DDD_REPRESENTANTE, v_TEL_REPRESENTANTE, sysdate, v_cod_usuario);
              --end if;
            /*else
              update prestador_representante set nom_representante = v_NOME_REPRESENTANTE, ddd_representante = v_DDD_REPRESENTANTE, tel_representante = v_TEL_REPRESENTANTE
                     where cod_prestador_ts = v_COD_PRESTADOR_TS;    */   
            end if;

            end loop;
            commit;

The fields that have the name,ddd and tel are dynamic. A button adds fields on the screen. A function ASP take the data from the screen, turn it into a XML and sends it to the ORACLE. What happens is that when I open the screen and say it has 3 records, which are shown on the screen. If I have it processed, it turns to 5 and is processed again to 9 and so on. She won’t repeat the first record and don’t ask me, because I have no idea why. As I do, in the code above not to save the same name. The commented lines, are my attempts to make it work, apart from the already deleted ones. On hold.

oracle asp

1 answer

1

pnet,

You’re doing an Insert, so the registration will always increase anyway. See that when you get the XML data, in the last line, you bring a v_ID_REPRESENTANTE, but, just below, you go in the array provider_representante_seq and bring a new id, which is stored in Vid.

I don’t understand exactly what you want to do, but I suppose replacing this Insert with an Update should solve.

  • Alysson, this was already another attempt not to record. I posted, not deleted, to show you what I’ve already done. Actually Vid is the right one, because I take Quence and record in PK. What I want is when I give an Insert, already make sure it’s not going values already repeated. Apparently, I will try to treat on the screen, but it would not be correct. The correct would be in the bank, in itself PROC.

  • pnet, I understand, you only want to insert if you don’t have the value in the table. In this case you should do a SELECT as in the line you commented, something like this:

  • SELECT nom_representative INTO vNOME FROM ts.prestador_representative WHERE cod_prestador_ts = v_COD_PRESTADOR_TS;

  • Alysson, this way gives error, because I can have several names in the table and the into will get lost, because it accepts only one. Now came the idea of a Count and put on the var. From the above shape I did and did not function.

Browser other questions tagged oracle asp

You are not signed in. Login or sign up in order to post.