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
Solved

Need insert.cfm page for my form

Posted on 2006-10-31
2
538 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Interesting problem when moving between environments 6 52
Unsearchable in Google,Yahoo and Bing. 6 63
SSL sertificate 5 70
Question to locate the problem 18 127
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…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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