Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Need insert.cfm page for my form

Posted on 2006-10-31
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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 2000 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

Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

705 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