Solved

Need insert.cfm page for my form

Posted on 2006-10-31
2
565 Views
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:

THANKS IN ADVANCE!!!


TableName: Trips

TripID
UserID
CountryID
CityID
ArrivalDate
DepartureDate
SponsorDept
SponsorDept
SponsorPhone
LocationsVisiting
BusinessReason
FirstTime
Protocols
SignOff





<!---//--------------------------------------------------------------------
requestform.cfm
---------------------------------------------------------------------//--->

<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 "">
    '#FORM.selectCountry#'
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.selectCity") AND #FORM.selectCity# NEQ "">
    '#FORM.selectCity#'
      <cfelse>
    NULL
  </cfif>
    )
  </cfquery>
  <cflocation url="insert.cfm">
</cfif>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

</head>

<body>



<cfscript>
     country = queryNew("countryid,name");
     queryAddRow(country);
     querySetCell(country, 'countryid','1');
     querySetCell(country, 'name','Colombia');
     queryAddRow(country);
     querySetCell(country, 'countryid','2');
     querySetCell(country, 'name','Indonesia');
     queryAddRow(country);
     querySetCell(country, 'countryid','3');
     querySetCell(country, 'name','Nigeria');
     queryAddRow(country);
     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)
     {
          queryAddRow(city);
          querySetCell(city,'countryid',countryid);
          querySetCell(city,'name',name);
          querySetCell(city,'cityid',cityid);
     }
</cfscript>

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

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


<!--- Login Form --->
<cfform format="flash"
            skin="haloblue"
            style="background-color:##FFFFFF;" height="600" width="550"
            action="requestform_action.cfm"
            METHOD="post">
       
            
<!--- Destination Header--->
<cfformgroup type="panel"
                  label="Destination"
                  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"
                  onChange="#actionPopulate#">
                <option>Please select a Country</option>
        </cfselect>
        <cfselect queryposition="below" disabled="true" label="City"
                  name="selectCity"  width="200">
                  <cfoutput query="city">
                          <option value="#cityid#|#Countryid#" id="cityvalue" name="Country">#name#
                          </option>
                          </cfoutput>
        </cfselect>
</cfformgroup>
        
            
            <!--- 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">
</cfformgroup>
            
            
<!--- Sponsor Info--->
<cfformgroup type="panel"
                   label="Sponsor"
                   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">
            </cfformgroup>
                        
<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>
            
            
            <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>
            
            <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" />
            </cfformgroup>

            
            <!--- 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">
            </cfformgroup>
            </cfformgroup>
</cfform>

</div>
</body>
</html>
0
Comment
Question by:selms
[X]
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
2 Comments
 
LVL 2

Expert Comment

by:Marco_van_den_Oever
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
0
 
LVL 25

Accepted Solution

by:
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
CF_SQL_DATE
CF_SQL_VARCHAR
CF_SQL_INTEGER
CF_SQL_NUMERIC
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>
)
Values
(
<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>
)
</CFQUERY>

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
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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 …
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

622 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