Professional Web Applications Themes

DELETE query problem - Coldfusion - Advanced Techniques

I have a strange problem when executing a insert and delete operation within one transaction, sometime the 'DeleteValues' is not executing, what could be the problem any ideas <CFTRANSACTION> <CFQUERY NAME="DeleteValues" PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#" USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#" DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#" DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#"> DELETE FROM mytable WHERE mykey=#Variables.Key# </CFQUERY> <CFQUERY NAME="CommitQuery" PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#" USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#" DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#" DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#"> COMMIT; </CFQUERY> <CFIF LEN("#Form.list_to_insert#") NEQ 0> <CFLOOP LIST="#Form.list_to_insert#" INDEX="currListItem"> <CFQUERY NAME="InsertValues" PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#" USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#" DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#" DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#"> INSERT INTO mytable(val1, val2) VALUES (#currListItem#, #Variables.Key#) </CFQUERY> </CFLOOP> </CFIF> </CFTRANSACTION>...

  1. #1

    Default DELETE query problem

    I have a strange problem when executing a insert and delete operation within
    one transaction, sometime the 'DeleteValues' is not executing, what could be
    the problem
    any ideas



    <CFTRANSACTION>
    <CFQUERY NAME="DeleteValues"
    PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#"
    USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#"
    DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#"
    DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#">
    DELETE FROM mytable WHERE mykey=#Variables.Key#
    </CFQUERY>

    <CFQUERY NAME="CommitQuery"
    PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#"
    USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#"
    DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#"
    DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#">
    COMMIT;
    </CFQUERY>

    <CFIF LEN("#Form.list_to_insert#") NEQ 0>
    <CFLOOP LIST="#Form.list_to_insert#" INDEX="currListItem">
    <CFQUERY NAME="InsertValues"
    PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#"
    USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#"
    DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#"
    DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#">
    INSERT INTO mytable(val1, val2) VALUES (#currListItem#, #Variables.Key#)
    </CFQUERY>
    </CFLOOP>
    </CFIF>
    </CFTRANSACTION>

    cfdyn Guest

  2. #2

    Default Re: DELETE query problem

    Instead of

    <CFQUERY NAME="CommitQuery"
    PASSWORD="#Request.DataSources.Signatorie_Details. DBPassword#"
    USERNAME="#Request.DataSources.Signatorie_Details. DBUserName#"
    DATASOURCE="#Request.DataSources.Signatorie_Detail s.DSN#"
    DBTYPE="#Request.DataSources.Signatorie_Details.Co nnectionType#">
    COMMIT;
    </CFQUERY>

    have you tried

    <cftransaction action="commit">

    since you are using the queries within a CFTRANSACTION tag?

    Zeke_the_Geek Guest

  3. #3

    Default Re: DELETE query problem

    Uh, what is it you're trying to do?

    DeleteValues will be rolledback if the transaction does not complete
    succesfully.

    Given that you're attempting to commit immediately afterwards, why is it even
    in the transaction?

    I suspect you are actually wanting to do something similar to this...


    <cfif Len(Form.list_to_insert) GT 0>
    <cfquery name="DeleteValues" datasource="#ds_name#">
    DELETE FROM mytable
    WHERE mykey=#Variables.Key#
    </cfquery>

    <cftransaction>
    <cfloop list="#Form.list_to_insert#" index="currListItem">
    <cfquery name="InsertValues" datasource="#ds_name#">
    INSERT INTO mytable (val1, val2)
    VALUES (#currListItem#, #Variables.Key#)
    </cfquery>
    </cfloop>
    </cftransaction>
    </cfif>

    boughtonp Guest

  4. #4

    Default Re: DELETE query problem

    Note: "Form.list_to_insert" is a SELECT control
    cfdyn Guest

  5. #5

    Default Re: DELETE query problem

    hi boughtonp thanks for the reply,
    No first i want to wipe out all datas in 'mytable', then i want to insert the values from the select control one by one

    cfdyn Guest

  6. #6

    Default Re: DELETE query problem

    It sounds like you probably don't need/want a SELECT control then.

    Can you post your HTML code?
    boughtonp Guest

  7. #7

    Default Re: DELETE query problem

    Here is the HTML code,
    I would like to insert selected values in the 'list_to_insert' into 'mytable',
    before that ,i need delete all the datas in the same table.


    <SELECT NAME="list_to_insert" STYLE="width:100%;" CLASS="W150" >
    <CFLOOP QUERY="MyQuery">
    <OPTION VALUE="#MyQuery.val1#">#MyQuery.val1#</OPTION>
    </CFLOOP>
    </SELECT>

    cfdyn Guest

  8. #8

    Default Re: DELETE query problem

    small correction, here is the CFLOOP code
    <CFLOOP LIST="#Form.list_to_insert#" INDEX="currListItem">
    <CFQUERY NAME="InsertValues" dataSource= "#ds_name#">
    INSERT INTO mytable(val1, mykey)
    VALUES (#currListItem#, #Variables.Key#)
    </CFQUERY>
    </CFLOOP>


    cfdyn Guest

  9. #9

    Default Re: DELETE query problem

    I'm still not clear what it is you are trying to do - what does MyQuery.val1
    contain?

    If your select is like this:
    <select name="list_to_insert" class="W150">
    <option value="orange,apple,banana,apricot">Fruit (orange, apple, banana,
    apricot)</option>
    <option value="red,blue,green,purple">Colours (red, blue, green,
    purple)</option>
    <option value="tiger,jaguar,koala,lion">Animals (tiger, jaguar, koala,
    lion)</option>
    </select>
    And you want to select one of those three lists and insert each of the four
    values into mytable, then the above code should do that.

    If your select is like this:
    <select name="list_to_insert" class="W150">
    <option value="orange">orange</option>
    <option value="apple">apple</option>
    <option value="banana">banana</option>
    <option value="apricot">apricot</option>
    </select>
    And you want to insert those four items into mytable, then you probably don't
    want a select but to get it to work you'll need to have a multi-select like
    this:
    <select name="list_to_insert" class="W150" multiple="multiple">..</select>
    And will need to use control or shift to select all the items.

    boughtonp Guest

  10. #10

    Default Re: DELETE query problem

    sorry for making things complicated.. I will explain,
    ************************************************** ********
    <select name="list_to_insert" class="W150">
    <option value="orange">orange</option>
    <option value="apple">apple</option>
    <option value="banana">banana</option>
    <option value="apricot">apricot</option>
    </select>
    ************************************************** ********
    the above code is the right , its a multi-select control .but user has the
    option to select and unselect the items listed. After user submits this page,
    I want all the values that are SELECTED to be inserted into 'mytable'.
    Before that, I need to delete previous contents in 'mytable' .
    Eg:
    <cfquery name="DeleteValues" datasource="#ds_name#">
    DELETE FROM mytable WHERE mykey='Fruit_ID'
    </cfquery>
    ie, I want to delete all the 'fruits' that were previously inserted.

    thanks again
    cfdyn




    cfdyn Guest

  11. #11

    Default Re: DELETE query problem

    Hi

    then you need write the cfloop to delete all the fruit_ids whichr are interted, then write one more loop to insert all those selected items.
    vkunirs Guest

  12. #12

    Default Re: DELETE query problem

    Not Necessary
    cfdyn Guest

  13. #13

    Default Re: DELETE query problem

    Originally posted by: cfdyn
    Before that, I need to delete previous contents in 'mytable' .
    Eg:
    <cfquery name="DeleteValues" datasource="#ds_name#">
    DELETE FROM mytable WHERE mykey='Fruit_ID'
    </cfquery>
    ie, I want to delete all the 'fruits' that were previously inserted.

    Ok, so if the table only contains 'fruits', then you just need "DELETE FROM
    mytable" - that will clear the entire contents of the table.

    boughtonp Guest

Similar Threads

  1. date delete query
    By roohbir in forum MySQL
    Replies: 2
    Last Post: March 20th, 12:47 PM
  2. Help with delete query please
    By Donna Elles in forum MySQL
    Replies: 3
    Last Post: June 2nd, 08:38 PM
  3. advanced delete query sql
    By kabbi~thkek in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 19th, 06:58 AM
  4. Delete row in query
    By jarilo in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: February 21st, 12:27 PM
  5. variable empty after after posting a delete query
    By Boefje in forum PHP Development
    Replies: 8
    Last Post: October 30th, 11:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139