troubleshooting Question

updating 2 tables using coldfusion sql ... newbie so be gentle with me ;)

Avatar of scooferman
scooferman asked on
Web ServersSQL
37 Comments1 Solution372 ViewsLast Modified:
Hello,
I need to update 2 tables in an access db using coldfusion and sql. I have tried several different methods but I am getting nowehere fast! Really what I would like to do is have a client select from a drop down list then have that data passed onto a second page where they can select another item and then pass that onto another page where they can fill out more details and that would update the 2 tables.... sounds simple to you guys but it has me stumped!
add_new.cfm
 
<cfquery name="findregion" datasource="cpl">
SELECT DISTINCT region
FROM region 
  </cfquery>
 
  <cfform name="findregion" action="Add_property.cfm">
    <h2 align="center">Select region 
	  <select name="selectregion">
	    <cfoutput query="findregion">
	      <option value="#findregion.region#">#findregion.region#</option>
          </cfoutput>
        </select>
	  <input name="Submitregion" type="submit" value="Submit">
              <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
                        	      <input type="hidden" value="#FORM.findregion.region#" name="FORM.findregion.region">
 
 
    </h2>
  </cfform>
 
<!--- next page --->
 
Add_property.cfm
 
<cfparam name="FORM.regionId" default="1">
 
<cfquery name="findregion" datasource="cpl">
SELECT DISTINCT region
FROM region 
  </cfquery>
 
  <cfform name="findregion" action="Add_property.cfm">
    <h2 align="center">&nbsp;</h2>
    <h2 align="center">&nbsp;</h2>
    <h2 align="center">Select region 
	  <select name="selectregion">
	    <cfoutput query="findregion">
	      <option value="#findregion.region#">#findregion.region#</option>
 
        </cfoutput>
      </select>
    </h2>
  </cfform>
 
<cfquery name="selectProperty" datasource="cpl">
SELECT *
FROM propertyType1
WHERE regionId = <cfqueryparam value="#FORM.regionId#" cfsqltype="cf_sql_clob" maxlength="50"> 
</cfquery>
 
<cfparam name="selectProperty" default="retail" type="Any">
<cfform name="selectProperty" action="add_propertyType.cfm" method="post">
	<div align="center">
	  <h2>Property Type 
	    <select name="selectProperty">
	      <option value="retail">Retail</option>
	      <option value="Office">Office</option>
	      <option value="Industrial">Industrial</option>
	      <option value="Land Plots">Land Plots</option>
          </select>
	    <input name="selectProperty" type="submit" value="Submit">
	    <cfoutput>
	      <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
          </cfoutput>
      </h2>
	</div>
</cfform> 
 
<!--- last page --->
 
add_propertyType.cfm
 
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="cpl">   
    INSERT INTO propertyType1 ("Description", address1, address2, townCity, postcode)
VALUES (<cfif IsDefined("FORM.Description") AND #FORM.Description# NEQ "">
<cfqueryparam value="#FORM.Description#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address1") AND #FORM.address1# NEQ "">
<cfqueryparam value="#FORM.address1#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address2") AND #FORM.address2# NEQ "">
<cfqueryparam value="#FORM.address2#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.townCity") AND #FORM.townCity# NEQ "">
<cfqueryparam value="#FORM.townCity#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.postcode") AND #FORM.postcode# NEQ "">
<cfqueryparam value="#FORM.postcode#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
  </cfquery>
  <cflocation url="#CurrentPage#?#CGI.QUERY_STRING#">
</cfif>
<cfquery name="Recordset1" datasource="cpl">
SELECT *
FROM propertyType1 
</cfquery>
 
<form method="post" name="form1" action="<cfoutput>#CurrentPage#?#CGI.QUERY_STRING#</cfoutput>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">Description:</td>
      <td><input type="text" name="Description" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address1:</td>
      <td><input type="text" name="address1" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address2:</td>
      <td><input type="text" name="address2" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TownCity:</td>
      <td><input type="text" name="townCity" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Postcode:</td>
      <td><input type="text" name="postcode" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
          <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
 
</form>
CPL.mdb
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 37 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 37 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros