Skip to main content
J
jamie61880
Inspiring
May 4, 2011
Question

Updating & Inserting with dynamic data

  • May 4, 2011
  • 2 replies
  • 1607 views

Hi. I am trying to update and insert some initials I have in drop down menus on a page that has dynamic data. I am able to update the items that have initials in the database already, but I cannot get the insert to work at the same time if another row that has initials chosen and is not in the database yet. For instance if the 3 sets of initials below represent my rows with drop down menus, the bottom 2 are already in the database, so I just update them and I have the code for this working. For the top one, however, I just chose those initials now as I am working on this page, so I need to insert those initials into the database and just update the bottom 2 initials. I'm not sure why my If statement is not working. I have it below. I basically have it saying if the drop down has initials chosen and the APV_ItemID is the row to update, then update the table. Elseif the Initials are chosen and the APV_ItemID is blank or does not exist in the database, then insert those initials. If I remove the APV_ItemID is blank code, then it just inserts both the initials I want it to insert and also inserts the update info. into more rows in the database. It should just update the bottom 2 and insert the top one. What am I doing wrong here? I also have another column called Document Type that tells me if it's a PNR or BOM Request, so I'm not sure if I need that. I commented that out. Can someone help me with this? Thanks.

JC

AG

SN

<cfloop index="id" list="#form.listofids#" delimiters=",">

<cfif structKeyExists(form, "PNR_Approval_Initials#id#") AND  form["PNR_Approval_Initials#id#"] NEQ "">

<cfquery Datasource="#application.DataSource#">
Update Approval
Set    [Approval_Initials]='#Evaluate("form.PNR_Approval_Initials#id#")#'
Where APV_ItemID=#id#
</cfquery>

<cfelseif structKeyExists(form, "PNR_Approval_Initials#id#") AND  form["PNR_Approval_Initials#id#"] NEQ ""<!---  and "Document_Type#id#" EQ "" and "APV_ItemID#id#" EQ "" --->>

<cfquery Datasource="#application.DataSource#">
Insert Into Approval (APV_ItemID,
                      APV_ECID,
                      Document_Type,
                      Approval_Initials)
                     
Values ('#Evaluate("form.ItemID#id#")#',
        '#Evaluate("form.ECID#id#")#',
        <cfif isDefined ("form.PNR_Document_Type#id#") and
        "form.PNR_Document_Type#id#" neq "">
        '#Evaluate("form.PNR_Document_Type#id#")#',
        <cfelse>
        Null,
        </cfif>
       
        <cfif isDefined ("form.PNR_Approval_Initials#id#") and
        "form.PNR_Approval_Initials#id#" neq "">
        '#Evaluate("form.PNR_Approval_Initials#id#")#'
        <cfelse>
        Null
        </cfif>)

</cfquery>

</cfif>

</cfloop>

Andy

    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    May 4, 2011

    I would take a completely different approach.  My first step would be to run a query that looked for existing records.  From this query, I would divide my list into two, one for updates and one for new records.  Then I'd loop though those two lists and do what I had to do.

    As an aside, you can improve performance by avoiding the evaluate function.  Instead of this:

    #Evaluate("form.PNR_Approval_Initials#id#")#'
    use this:

    #form["pnr_approval_initials" & id]#

    J
    jamie61880Author
    Inspiring
    May 4, 2011

    Dan,

       I put this query into my code above the cfloop:

    <CFQUERY NAME="ShowInitials" Datasource="#application.DataSource#">
    SELECT APV_ItemID
    From Approval
    Where APV_ItemID Is Not Null
    </CFQUERY>

    What do I do with this now though? How do I use it for the Update List and for the New Record List?

    Andy

    D
    Dan_Bracuk
    Inspiring
    May 4, 2011

    Change the where clause so that it looks for only those records that are in the list you got from the form submission.  That will get you started.

    Owainnorth
    Owainnorth
    Inspiring
    May 4, 2011

    Logical debugging time.

    Sack off all the database queries, comment them out. Simply dump out the variables you're checking within the loop, so you can see on the screen in front of you. Almost certainly you're comparing an ID to a string at some point or something, but should all become clear when if you output the data.

    J
    jamie61880Author
    Inspiring
    May 4, 2011

    Is this how I dump this?

    <cfdump var=""><cfabort>

    I commented out the queries and kept the loop and structKeyExists code on the page. When I run this page, the only thing that comes up is [empty string] . Did I not do this correctly?

    Andy

    J
    jamie61880Author
    Inspiring
    May 4, 2011

    I changed the dump code to this now:

    <cfdump var="#form.listofids#"><cfabort>

    This gives me 5,4,3,2,1which are the ItemID's from my Items Table, but does not give me any info. from the Approval Table. The approval table is where the initials are located.

    Andy

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices