Need insert.cfm page for my form

Posted on 2006-10-31
Last Modified: 2013-12-24

I have a working requestform.cfm (below) - you can save it and see what it is supposed to do.

I need to create an insert.cfm page that will insert a new record into an Access table (trips) with the follwing fields when the requestform.cfm is submitted:


TableName: Trips



<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "myform">
<cfquery datasource="TravelApprover">
    INSERT INTO TravelApprover (CountryID, CityID)
    VALUES (
  <cfif IsDefined("FORM.selectCountry") AND #FORM.selectCountry# NEQ "">
  <cfif IsDefined("FORM.selectCity") AND #FORM.selectCity# NEQ "">
  <cflocation url="insert.cfm">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"



     country = queryNew("countryid,name");
     querySetCell(country, 'countryid','1');
     querySetCell(country, 'name','Colombia');
     querySetCell(country, 'countryid','2');
     querySetCell(country, 'name','Indonesia');
     querySetCell(country, 'countryid','3');
     querySetCell(country, 'name','Nigeria');
     querySetCell(country, 'countryid','4');
     querySetCell(country, 'name','Venezuela');
     city = queryNew("countryid,name,cityid");
     addRow(city, '1','Bogota','1a' );
     addRow(city, '2', 'Jakarta' ,'2a');
     addRow(city, '3', 'Lagos' ,'3a');
     addRow(country, '4','Caracas', '4a');
     addRow(country, '4','Puerto La Cruz', '4b');

     function addRow( rs, countryid, name, cityid)

<cfsavecontent variable="actionPopulate">
if(_global.arrCities == undefined) _global.arrCities = selectCity.dataProvider.slice(0);
     var arrCities:Array = _global.arrCities;
for(var i = 0; i < arrCities.length; i++)
     var item = arrCities[i].data.split('|');
          if(item[1] == selectCountry.value )
     selectCity.enabled = (selectCity.length >0) ? true:false;

<div id="maincontent">
   <p><strong>Welcome back, #SESSION.auth.firstName#!</strong><br>
   Fill out the following form to request approval for a new trip.</p>

<!--- Login Form --->
<cfform format="flash"
            style="background-color:##FFFFFF;" height="600" width="550"
<!--- Destination Header--->
<cfformgroup type="panel"
                  style="headerColors:##D6EFFE, ##D6EFFE; fontSize:12; text-align: left;">

<!--- Display for Country-picklist - on Travel Approval Request Form 3 of 3 --->
<!--- 1 and 2 are on header.cfm--->
        <cfformgroup type="hbox">
        <cfselect queryposition="below" label="Country"
                  name="selectCountry" query="Country" value="Countryid"
                  display="name" width="200"
                <option>Please select a Country</option>
        <cfselect queryposition="below" disabled="true" label="City"
                  name="selectCity"  width="200">
                  <cfoutput query="city">
                          <option value="#cityid#|#Countryid#" id="cityvalue" name="Country">#name#
            <!--- Set initial selected and blocked-out dates.--->
        <cfparam name="Form.departuredate" default="#dateformat(now(), 'mm/dd/yyyy')#">
        <cfparam name="Form.returndate" default="#dateformat(now()+1, 'mm/dd/yyyy')#">
<!--- Dynamic Calendars for dates --->

<cfformgroup type="hbox">
<cfinput type="dateField" name="departuredate" label="Arriving In-Country:" width="100" value="#Form.departuredate#" required="Yes" align="left">

<cfinput type="dateField" name="returndate" label="Departing" width="100" value="#Form.returndate#" required="Yes">
<!--- Sponsor Info--->
<cfformgroup type="panel"
                   style="headerColors:##D6EFFE, ##D6EFFE; fontSize:12; text-align: left;">
            <cfformgroup type="Horizontal" label="Sponsor Name, Dept:">
            <cfinput type="text" name="SponsorName" width="100" required="Yes">
            <cfinput type="text" name="SponsorDept" width="150" required="Yes">
<cfinput type="text" name="SponsorPhone" width="200" label="Sponsor Telephone:" validate="noblanks,telephone" required="Yes">
<cfinput type="Text" name="LocationsVisiting" label="Locations to be visited:" width="250" required>
<cfinput type="Text" name="BusinessReason" label="Business Reason for this trip" width="300" required>
            <cfformgroup type="horizontal" label="Are you a first time traveler to this destination?">
            <cfinput name="FirstTimeTraveler" type="radio" label="Yes" value="1Yes" />
            <cfinput name="FirstTimeTraveler" type="radio" label="No" value="1No" />
            <cfformgroup type="horizontal" label="Have you arranged protocols with the local office?">
            <cfinput name="Protocols" type="radio" label="Yes" value="2Yes" />
            <cfinput name="Protocols" type="radio" label="No" value="2No" />

            <!--- Submit.--->
            <cfformgroup type="panel" label="Submit Request" style="headerColors:##D6EFFE, ##D6EFFE; fontSize:12; text-align: left;">
            <cfinput name="checkbox"  type="checkbox" label=" I have read the briefing and request permission to travel." enabled="true" required="yes"/>

            <cfinput type="Submit" name="submitBtn" value="Submit">

Question by:selms

Expert Comment

ID: 17848728
Hi there:) You don't need a extra insertpage for the code above, set the cfformaction url to the pagetitle of the code above, then the insertrecord will be activated, the only thing you need to do is ad more fields to the insertrecord so that all the field of the cfform and not only countryid/cityid will be updated and filled out.

Then in the cflocationurl you can give an url to a page with something like: Your trip to the netherlands, leiden is set!

You can ofcourse mail me with the idea of the whole application so that i can put it all together, maybe that's easier instead of just talk about one part at a time, bit coldfusion euhmmm confusing:) for us both
LVL 25

Accepted Solution

dgrafx earned 500 total points
ID: 17848743
Well here is the insert query.
1) I put a <cfif StructKeyExists(form,"variablename")></cfif> around them just in case they aren't defined
2) are any of the columns in the database numeric or date datatype?
If so - you need to change the CF_SQL_VARCHAR to appropriate type
you can look up more

<CFQUERY datasource="TravelApprover">
Insert Into Trips
(<cfif StructKeyExists(form,"TripID")>TripID,</cfif>
<cfif StructKeyExists(form,"UserID")>UserID,</cfif>
<cfif StructKeyExists(form,"CountryID")>CountryID,</cfif>
<cfif StructKeyExists(form,"CityID")>CityID,</cfif>
<cfif StructKeyExists(form,"ArrivalDate")>ArrivalDate,</cfif>
<cfif StructKeyExists(form,"DepartureDate")>DepartureDate,</cfif>
<cfif StructKeyExists(form,"SponsorDept")>SponsorDept,</cfif>
<cfif StructKeyExists(form,"SponsorPhone")>SponsorPhone,</cfif>
<cfif StructKeyExists(form,"LocationsVisiting")>LocationsVisiting,</cfif>
<cfif StructKeyExists(form,"BusinessReason")>BusinessReason,</cfif>
<cfif StructKeyExists(form,"FirstTime")>FirstTime,</cfif>
<cfif StructKeyExists(form,"Protocols")>Protocols,</cfif>
<cfif StructKeyExists(form,"SignOff")>SignOff</cfif>
<cfif StructKeyExists(form,"TripID")><cfqueryparam value="#form.TripID#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"UserID")><cfqueryparam value="#form.UserID#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"CountryID")><cfqueryparam value="#form.CountryID#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"CityID")><cfqueryparam value="#form.CityID#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"ArrivalDate")><cfqueryparam value="#form.ArrivalDate#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"DepartureDate")><cfqueryparam value="#form.DepartureDate#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"SponsorDept")><cfqueryparam value="#form.SponsorDept#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"SponsorPhone")><cfqueryparam value="#form.SponsorPhone#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"LocationsVisiting")><cfqueryparam value="#form.LocationsVisiting#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"BusinessReason")><cfqueryparam value="#form.BusinessReason#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"FirstTime")><cfqueryparam value="#form.FirstTime#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"Protocols")><cfqueryparam value="#form.Protocols#" cfsqltype="CF_SQL_VARCHAR">,</cfif>
<cfif StructKeyExists(form,"SignOff")><cfqueryparam value="#form.SignOff#" cfsqltype="CF_SQL_VARCHAR"></cfif>

Important! You need to make sure your last field in the list is defined
because if it isn't you'll throw an error from a trailing comma

good luck

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CGI takes almost 2 minutes to load using thttpd 20 43
Unable To Access a Web Domain From Our Network 16 84
Firewall Speed Issue 6 66
spamming  on Hosted svrs? 6 72
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now