Skip to main content
A
Anonymous
March 19, 2009
Question

Update/Insert

  • March 19, 2009
  • 7 replies
  • 1018 views
I have a form that pulls information from a database and displays it in a form so that someone can update it. How can I say that if there is no row in a database to update to insert new information?
    This topic has been closed for replies.

    7 replies

    A
    Anonymous
    March 19, 2009
    yes
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 19, 2009
    See sample code.
    A
    Anonymous
    March 19, 2009
    How can I use the looping feature do you know that?
    A
    Anonymous
    March 19, 2009
    <cfquery name="1InputProductOptions" datasource="" username="" password="">
    UPDATE options_table
    SET Options_descriptions = '#form.option1#'
    WHERE Options_ID = '#form.options1#'
    </cfquery>

    Lastly how can I use a loop to do this 1 - 8 I tried the traditional means and it didn't work.
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 19, 2009
    quote:
    Originally posted by: PopcornCoder
    <cfquery name="1InputProductOptions" datasource="" username="" password="">
    UPDATE options_table
    SET Options_descriptions = '#form.option1#'
    WHERE Options_ID = '#form.options1#'
    </cfquery>

    Lastly how can I use a loop to do this 1 - 8 I tried the traditional means and it didn't work.


    Do you have form fields form.optionX and form.optionsX where X is 1-8?

    A
    Anonymous
    March 19, 2009
    Options1 empty if no info is already in database
    A
    Anonymous
    March 19, 2009
    UPDATE options_table
    SET Options_descriptions = '#form.option1#'
    WHERE Options_ID = '#form.options1#'
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 19, 2009
    What is the value of form.options1 if no record exists and an insert is needed?
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 19, 2009
    See attached sample code. I suggest that you use CFQUERYPARAM in your SQL statements for improved security and performance.

    CFQUERY
    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html#1102316

    CFQUERYPARAM
    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 19, 2009
    Depends on your database. MS SQL Server 2008 has a MERGE function. MySQL supports this though INSERT with ON DUPLICATE KEY UPDATE.

    For other databases you could use a stored procedure that attempts an UPDATE then checks to see if any rows where affected. If zero rows are affected perform an INSERT.

    What database server are you using?
    A
    Anonymous
    March 19, 2009
    Using MySQL
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 19, 2009
    For MySQL I'd either use INSERT with ON DUPLICATE KEY UPDATE or write a stored procedure containing something like:


    -- try update first
    UPDATE my_table
    SET ColA = 'alpha'
    WHERE ColB = 'beta'


    -- if no records updated do insert
    IF ROW_COUNT() < 1 THEN

    INSERT INTO my_table ( ColA, ColB )
    VALUES ( 'alpha', 'beta' )

    END IF
    N
    Newsgroup_User
    Inspiring
    March 19, 2009
    usually, a row in a db table will have some unique identifier -primary
    key. and usually, when a row is being edited, that pk is saved in a
    hidden field or recorded somewhere else. if your form does not have a
    field with pk value in it (or the value is empty) - you are inserting a
    new row...


    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices