Solved

Need insert.cfm page for my form

Posted on 2006-10-31
2
475 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
2 Comments
 
LVL 2

Expert Comment

by:Marco_van_den_Oever
Comment Utility
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 24

Accepted Solution

by:
dgrafx earned 500 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

13 Experts available now in Live!

Get 1:1 Help Now