?
Solved

cfloop for multiple inserts

Posted on 2005-04-21
8
Medium Priority
?
282 Views
Last Modified: 2013-12-24
I have a form that needs to insert multiple times into one database based on session variables.  I currently have four separate insert statements, which work fine.  My problem is getting the cflocation to work - how do I get it to finish all the inserts before redirecting?  I believe I need to do a cfloop, but don't know where to start.  Any help is greatly appreciated.  My code is shown below.  Thanks.

<cfparam name="SESSION.PhysicianID" default="1">
<cfparam name="SESSION.SupervisorID" default="1">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif isDefined("URL.IPAAgree")>
<cflock timeout=20 scope="Session" type="Exclusive">
   <cfset Session.IPAgree = #URL.IPAAgree#>
</cflock>
</cfif>

<cfif IsDefined("FORM.accMeditechnetwork") AND #Session.accMeditechnetwork# EQ "Y">
      <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accMeditechnetwork") AND #FORM.accMeditechnetwork# EQ "Y">
            1
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
</cfif>
<cfif IsDefined("FORM.accMeditechweb") AND #Session.accMeditechweb# EQ "Y">
      <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accMeditechweb") AND #FORM.accMeditechweb# EQ "Y">
            2
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
</cfif>
<cfif IsDefined("FORM.accNetwork") AND #Session.accNetwork# EQ "Y">
      <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accNetwork") AND #FORM.accNetwork# EQ "Y">
            3
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
</cfif>
<cfif IsDefined("FORM.accPACSweb") AND #Session.accPACSweb# EQ "Y">
      <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accPACSweb") AND #FORM.accPACSweb# EQ "Y">
            4
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
</cfif>
<cfquery name="rsPhysician" datasource="PhysicianAccess">
SELECT PhysicianID, FName, LName, MI, Title, PreferredName, Degree, Phone, DictationNo, TeachingNo, SSN, ExpirationDate, PhysicianInfo.Address1 as pAddress1, PhysicianInfo.Address2 as pAddress2, PhysicianInfo.City as pCity, PhysicianInfo.State as pState, PhysicianInfo.ZIP as pZIP, PhysicianInfo.GroupID as pGroupID, GroupInfo.GroupID as gGroupID, GroupName, GroupInfo.Address1 as gAddress1, GroupInfo.Address2 as gAddress2, GroupInfo.City as gCity, GroupInfo.State as gState, GroupInfo.ZIP as gZIP FROM PhysicianInfo JOIN GroupInfo ON PhysicianInfo.GroupID = GroupInfo.GroupID WHERE PhysicianID = #SESSION.PhysicianID#
</cfquery>
<cfquery name="rsContact" datasource="PhysicianAccess">
SELECT * FROM dbo.ContactInfo WHERE SupervisorID = #SESSION.SupervisorID#
</cfquery><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<link href="../default.css" rel="stylesheet" type="text/css">
</head>

<body>
<form name="form1" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
  <table width="100%"  border="0">
    <tr>
      <td><table width="100%"  border="0">
        <tr>
          <td colspan="2"><strong>Physician Information: </strong></td>
          </tr>
        <tr>
          <td width="15%">Name:</td>
          <td width="85%"><cfoutput>#rsPhysician.LName#</cfoutput>, <cfoutput>#rsPhysician.FName#</cfoutput><cfif #rsPhysician.MI# NEQ ""> <cfoutput>#rsPhysician.MI#</cfoutput></cfif><cfif #rsPhysician.PreferredName# NEQ ""> (Preferred Name: <cfoutput>#rsPhysician.PreferredName#</cfoutput>)</cfif></td>
        </tr>
        <tr>
          <td width="15%">Job Title: </td>
          <td width="85%"><cfoutput>#rsPhysician.Title#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">Degree:</td>
          <td width="85%"><cfoutput>#rsPhysician.Degree#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">&nbsp;</td>
          <td width="85%"><input name="PhysicianID" type="hidden" id="PhysicianID" value="<cfoutput>#SESSION.PhysicianID#</cfoutput>">
            <input name="accMeditechnetwork" type="hidden" id="accMeditechnetwork" value="<cfoutput>#Session.accMeditechnetwork#</cfoutput>">
                  <input name="accMeditechweb" type="hidden" id="accMeditechweb" value="<cfoutput>#Session.accMeditechweb#</cfoutput>">
                  <input name="accNetwork" type="hidden" id="accNetwork" value="<cfoutput>#Session.accNetwork#</cfoutput>">
                  <input name="accPACSweb" type="hidden" id="accPACSweb" value="<cfoutput>#Session.accPACSweb#</cfoutput>">
            </td>
        </tr>
      </table></td>
    </tr>
    <tr>
      <td><table width="100%"  border="0">
        <tr>
          <td colspan="2"><strong>Physician Group Information:</strong></td>
          </tr>
        <tr>
          <td width="15%">Group Name/Physician:</td>
          <td width="85%"><cfoutput>#rsPhysician.GroupName#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">Address:</td>
          <td width="85%"><cfoutput>#rsPhysician.gAddress1#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">Address (cont):</td>
          <td width="85%"><cfoutput>#rsPhysician.gAddress1#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">City, State Zip: </td>
          <td width="85%"><cfoutput>#rsPhysician.gCity#</cfoutput>, <cfoutput>#rsPhysician.gState#</cfoutput> <cfoutput>#rsPhysician.gZIP#</cfoutput></td>
        </tr>
      </table></td>
    </tr>
    <tr>
      <td><table width="100%"  border="0">
        <tr>
          <td colspan="2"><strong>Point of Contact Information: </strong></td>
          </tr>
        <tr>
          <td width="15%">Name:</td>
          <td width="85%"><cfoutput>#rsContact.LName#</cfoutput>, <cfoutput>#rsContact.FName#</cfoutput> <cfoutput>#rsContact.MI#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">Title:</td>
          <td width="85%"><cfoutput>#rsContact.Title#</cfoutput></td>
        </tr>
        <tr>
          <td width="15%">&nbsp;</td>
          <td width="85%"><input name="SupervisorID" type="hidden" id="SupervisorID" value="<cfoutput>#SESSION.SupervisorID#</cfoutput>"></td>
        </tr>
      </table></td>
    </tr>
    <tr>
      <td><table width="100%"  border="0">
        <tr>
          <td width="35%">Agreed to Acknowledgement of Confidentiality: </td>
          <td width="65%"><cfoutput>#Session.ConfAgree#</cfoutput></td>
        </tr>
        <tr>
          <td width="35%">Access Requested: </td>
          <td width="65%"><cfoutput><cfif #Session.accMeditechnetwork# EQ "Y">Meditech via MHS network &nbsp;</cfif><cfif #Session.accMeditechweb# EQ "Y">Meditech via web (token) &nbsp;</cfif><cfif #Session.accNetwork# EQ "Y">MHS Network, including email &nbsp;</cfif><cfif #Session.accPACSweb# EQ "Y">PACS via web (token) </cfif></cfoutput>&nbsp;</td>
        </tr>
            <cfif #Session.IPAgree# EQ "Y"><tr>
          <td width="35%">Agreed to IPAA:</td>
          <td width="65%">Yes</td>
        </tr>
            <cfelse>
            <tr></tr>
            </cfif>
            <tr>
                  <td>Access request for:</td>
                  <td><cfoutput>#Session.typeID#</cfoutput></td>
            </tr>
      </table></td>
    </tr>
    <tr>
      <td height="35" valign="bottom"><input type="submit" name="Submit" value="Submit Request"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
</form>
</body>
</html>
0
Comment
Question by:megbriggs
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 13837927
If you place the cflocation after all of the INSERT code then it will not happen until after the inserts.
0
 
LVL 1

Expert Comment

by:duroWRX
ID: 13838053
mrichmon is right -- placing cflocation after the inserts should allow inserts before a redirect.  

Did you paste us one coldfusion template or two?

Also, I see you doing IsDefined() to insert a value or null, a better idea would be to use <cfparam name="FORM.accMeditechnetwork" default="NULL"> and then just insert #FORM.accMeditechnetwork# afterwards, getting rid of the IsDefined statement in your insert query...

One more thing I see is that if you are relying on all these inserts to happen at the same time, everytime, <cftransaction> [your inserts here] </cftransaction> will make sure they either all insert or rollback all inserts in case of an error.  Much better for data integrity.

0
 

Author Comment

by:megbriggs
ID: 13838452
It is one template.  If I put the cflocation at the end of all the cfifs, the page immediately redirects to the cflocation page and I don't get to submit the form for inserts.  Thanks for your suggestions - I will look at incorporating them.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 35

Expert Comment

by:mrichmon
ID: 13839252
Well you don't want to be doing the insert unless the form is submitted either.
So it goes after the <cfquery> tags but inside the cfif tags that tell the form was submitted.
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 750 total points
ID: 13840853
Hi megbriggs!

With regards to submitting on the same page, you may simply not include attribute "action" on your "<form>" tag.  Or simply specify no value on the said attribute.

So, it's either one of the ff :
  1.  <form name="form1" method="post">
  2.  <form name="form1" method="post" action="">

And speaking of your problem, about redirecting to another page after insert statement.  Let me revise your code on the part of insertion.

:: REVISED CODE ::
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <!--- First Insert Statement --->
  <cfquery datasource="PhysicianAccess">
    INSERT INTO dbo.RequestInfo (AccessID, PhysicianID)
    VALUES
    (
      <cfif IsDefined("FORM.accMeditechnetwork") AND #FORM.accMeditechnetwork# EQ "Y">
        1
      <cfelse>
        NULL
      </cfif>
      ,
      <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
        #FORM.PhysicianID#
      <cfelse>
        NULL
      </cfif>
    )
  </cfquery>
  <!--- Second Insert Statement --->
  <cfquery datasource="PhysicianAccess">
    INSERT INTO dbo.RequestInfo (AccessID, PhysicianID)
    VALUES
    (
      <cfif IsDefined("FORM.accMeditechweb") AND #FORM.accMeditechweb# EQ "Y">
        2
      <cfelse>
        NULL
      </cfif>
      ,
      <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
        #FORM.PhysicianID#
      <cfelse>
        NULL
      </cfif>
    )
  </cfquery>
  <!--- Third Insert Statement --->
  <cfquery datasource="PhysicianAccess">
    INSERT INTO dbo.RequestInfo (AccessID, PhysicianID)
    VALUES
    (
      <cfif IsDefined("FORM.accNetwork") AND #FORM.accNetwork# EQ "Y">
        3
      <cfelse>
        NULL
      </cfif>
      ,
      <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
        #FORM.PhysicianID#
      <cfelse>
        NULL
      </cfif>
    )
  </cfquery>
  <!--- Forth Insert Statement --->
  <cfquery datasource="PhysicianAccess">
    INSERT INTO dbo.RequestInfo (AccessID, PhysicianID)
    VALUES
    (
      <cfif IsDefined("FORM.accPACSweb") AND #FORM.accPACSweb# EQ "Y">
        4
      <cfelse>
        NULL
      </cfif>
      ,
      <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
        #FORM.PhysicianID#
      <cfelse>
        NULL
      </cfif>
    )
  </cfquery>

  <!--- After All Insert Statements, Redirect to another page --->
  <cflocation url="nextpage.cfm" addtoken="no">
</cfif>

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 

Author Comment

by:megbriggs
ID: 13843746
Here is what I ended up with.  I had to modify Entrance2002's response, as without the first cfif statement, I got an error about inserting nulls into a filed that doesn't allow nulls.  Thanks to everyone for your help and suggestions.

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
      <!--- First Insert Statement --->
      <cfif IsDefined("FORM.accMeditechnetwork") AND #Session.accMeditechnetwork# EQ "Y">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accMeditechnetwork") AND #FORM.accMeditechnetwork# EQ "Y">
            1
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
      <!--- Second Insert Statement --->
      <cfif IsDefined("FORM.accMeditechweb") AND #Session.accMeditechweb# EQ "Y">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accMeditechweb") AND #FORM.accMeditechweb# EQ "Y">
            2
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
      <!--- Third Insert Statement --->
      <cfif IsDefined("FORM.accNetwork") AND #Session.accNetwork# EQ "Y">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accNetwork") AND #FORM.accNetwork# EQ "Y">
            3
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
      <!--- Fourth Insert Statement --->
      <cfif IsDefined("FORM.accPACSweb") AND #Session.accPACSweb# EQ "Y">
        <cfquery datasource="PhysicianAccess">
        INSERT INTO dbo.RequestInfo (AccessID, PhysicianID) VALUES (
        <cfif IsDefined("FORM.accPACSweb") AND #FORM.accPACSweb# EQ "Y">
            4
              <cfelse>
              NULL
        </cfif>
        ,
        <cfif IsDefined("FORM.PhysicianID") AND #FORM.PhysicianID# NEQ "">
            #FORM.PhysicianID#
              <cfelse>
              NULL
        </cfif>
        )
        </cfquery>
      </cfif>
      <cflocation url="physsubmitted.cfm" addtoken="no">
</cfif>

0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13847775
Hi megbriggs!

Yah, I forgot the other condition.  I'm a little bit careless about it.  Anyway as long as you have resolved your problem.

I'm glad that I have helped you.  Hope to help you again.


Regards!
eNTRANCE2002 :-)
0
 

Author Comment

by:megbriggs
ID: 13848230
I'll take all the help I can get - thanks again.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
What You Need to Know when Searching for a Webhost Provider
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question