Stop inclusion of a record

Applications Forms
#1

Hi to all, I would like to know if there is a way to stop the inclusion of a new record based on a OnBeforeInsert event.

I have a form that inserts new records on my database, what I want is that if that records exist the record be updated and not included, because it shows an error message “Already Exists”…

#2

Re: Stop inclusion of a record

Are you using MSYQL? If so have a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

I think it would be a better to use this approach than trying to cancel an insert, then issue an update.

Regards,
Scott.

#3

Re: Stop inclusion of a record

ok Scott and reading Mysql Manual, but when should I insert that SQL Statement on SC?

I am new with this system…

#4

Re: Stop inclusion of a record

I mean “where” not “when”… sorry for that…

#5

Re: Stop inclusion of a record

$sql = “INSERT INTO tb_stock_accesorios (id_accesorio,cantidad_stock) VALUES (’{id_accesorio}’,’{cantidad_stock}’)
ON DUPLICATE KEY UPDATE cantidad_stock=cantidad_stock+{cantidad_stock}”;

If I use this statement in OnBeforeInsert event the results are exactly the same, it shows an error message “Register Already Exists”… I need to stop the Insert execution on Sriptcase… Maybe creating an PHP Button instead of using toolbar “Insert” button is the solution… I will try and post results…

#6

Re: Stop inclusion of a record

PHP Button doesn?t work for my purpose… still thinking in a solution

#7

Re: Stop inclusion of a record

I see your problem. Several options I could think of …
Check if exists in onbefore, then call a PHP function to update and then sc_exit/sc_redir out?

I am not sure if the sc_error* options can override the default error messages.

#8

Re: Stop inclusion of a record

sc_error_continue() perhaps? … and have a look at sc_error_insert as well.

Regards,
Scott.

#9

Re: Stop inclusion of a record

…what I want is that if that records exist the record be updated and not included…

If a record already exists, what’s the use of updating it?

Do you mean that the primary/unique key value already exists in the table?

#10

Re: Stop inclusion of a record

I have used update on insert in several instances… Add inventory item, but if exists, just update the price and qty for example.

Regards,
Scott.

#11

Re: Stop inclusion of a record

I have used update on insert in several instances… Add inventory item, but if exists, just update the price and qty for example.

Regards,
Scott.

As Scott said this is an Inventory item, I think I will use a temp table, and delete the record after inclusion.

#12

Re: Stop inclusion of a record

The other option is to create a CONTROL form and just handle the insert yourself with the button click and not use the prebuilt INSERT FORM.

This way you can call the SQL function I mentioned then redir to the desired app.

Regards,
Scott.

#13

Re: Stop inclusion of a record

I tought that, but I need multiple register, so a Control App can?t do this…

#14

Re: Stop inclusion of a record

I solved my problem using a new temporary table on my BD. Thanks a lot for supporting me Scott… can you PM me your msn?

#15

Re: Stop inclusion of a record

Why don’t you overrule the default insert procedure by checking the “Enable INSERT Procedure”? This way the form doesn’t execute the insert but calls your own stored procedure. In this procedure you can check for existing record and either insert new or update existing record.

#16

Re: Stop inclusion of a record

I know nothing about “Procedures”… That is why I opened this topic, to look for any help… I will check that option for further apps… Thanks Freezer

#17

Re: Stop inclusion of a record

It would be nice to override Insert using this with custom code instead of only a stored proc. I guess this could be an option. Call a store procedure.

Anyway. Check the ‘Enable INSERT Procedure’
Enter the Procedure name and # of field. Use the template below to create your SQL procedure.

Regards,
Scott.



DROP PROCEDURE IF EXISTS mydb.setProduct;
CREATE PROCEDURE mydb.`setProduct`(
    
IN   p_name      VARCHAR(20),
IN   p_description   VARCHAR(20),
IN   p_company     VARCHAR(20),
IN   p_country     VARCHAR(20))
BEGIN  
    INSERT INTO Product
    (
        name,
        description,
        company,
        country
    )
    VALUES
    (
        p_name,
        p_description,
        p_company,
        p_country
    )
    ON DUPLICATE KEY 
    UPDATE description=p_description
       , company = p_company
       , country = p_country
    ;
END;