School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 300,485
  • Replies: 826,138
  • Topics: 137,481
  • Snippets: 4,420
  • Tutorials: 1,148
  • Total Online: 1,774
  • Members: 77
  • Guests: 1,697

Welcome to Dream.In.Code
Become an Expert!

Join 300,485 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,774 people online right now. Registration is fast and FREE... Join Now!




Trouble inserting form info to two databases?

 

Trouble inserting form info to two databases?
cumupkid

10 Apr, 2009 - 05:14 AM
Post #1

D.I.C Head
**

Joined: 22 Jan, 2008
Posts: 61
I have a form needs to be able to edit information in two different database tables.

First part is a customer information table, and editing this information works fine.

Second part is a city, state, zip table.

What I need to do is first look to see if the city, state, zip exists in the table, this needs to then add the information if it does not exist. If it does already exist then it simply needs to change the customer information

The customer table also has a zip field in it.

The way the database works is the zip in the customer table refrences a zip table to show the city and state.

This is a database that cannot be changed as it goes to something else.

First Table is Customers and the relative field name is Zip

Second Table name is Zips, and the field names are ZipCodeNum (Zip), City, and State

Also the Zip field in the Customers Table needs to change when the edit form is submitted.

Below is 3 different blocks of code

Form Code
CODE

<link href="../assets/DTDatabaseViewer.css" rel="stylesheet" type="text/css" />



<cfparam name="URL.Phone" default="1">
<cfparam name="URL.Zip" default="1">

    <cfinvoke
         component="DTCRM.cfcs.customers"
         method="getCustomerByPhoneURL"
         returnvariable="qCustomerByPhoneURL">
    </cfinvoke>
    
    <cfinvoke
         component="DTCRM.cfcs.customers"
         method="getCityStateByZipURL"
         returnvariable="qCityStateByZipURL">
    </cfinvoke>

<cfif isdefined("form.phone")>
    <cfinvoke
         component="DTCRM.cfcs.customers" method="customeredit">
        <cfinvokeargument name="formdata" value="#form#"/>
    </cfinvoke>
    <cflocation url="index.cfm?customerDetails&Phone=#form.Phone#&Zip=#form.zip#" addtoken="no">
</cfif>


<!-- pageName -->
    <table width="100%">
        <td valign="top" class="pageName"><div align="center">Edit Customer Details</div></td>
    </table>
    
<table border="0" cellspacing="0" cellpadding="0" width="100%">
        <tr>
            <td valign="bottom" colspan="" nowrap="nowrap" class="pageHeader" height="85" width="100%">
                <cfoutput> Details for #qCustomerByPhoneURL.FirstName1# #qCustomerByPhoneURL.LastName#</cfoutput>          </td>
        </tr>
</table>

<table border="0" cellspacing="0" cellpadding="0" width="100%">
        <tr valign="bottom">
            <td height="60" colspan="7" align="center" nowrap="nowrap" id="alphaLinks"><p>
        </tr>
</table>


<cfform>

<table bgcolor="99CCFF" align="center" width="50%" border="0" id="customerEditTable">

  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">First Name:</td>
    <td width="50%" height="20" align=" right"><cfinput type="text" name="firstname1" id="firstname1" tabindex="1" value="#qCustomerByPhoneURL.FirstName1#" cfsqltype="cf_sql_varchar" maxlength="200"></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">Last Name:</td>
    <td width="50%" height="20"><cfinput type="text" name="lastname" id="lastname" tabindex="2" value="#qCustomerByPhoneURL.LastName#" cfsqltype="cf_sql_varchar" maxlength="20"></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">Email:</td>
    <td width="50%" height="20"><cfinput type="text" name="firstname2" validateat="onSubmit" validate="Email" id="firstname2" tabindex="3" value="#qCustomerByPhoneURL.FirstName2#" cfsqltype="cf_sql_varchar" maxlength="50" typeahead="no" showautosuggestloadingicon="true"></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">Phone:</td>
    <td width="50%" height="20"><cfinput type="text" name="phone" id="phone" tabindex="4" value="#qCustomerByPhoneURL.Phone#" cfsqltype="cf_sql_varchar" maxlength="12" readonly="true" typeahead="no" showautosuggestloadingicon="true"></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">Street:</td>
    <td width="50%" height="20"><cfinput type="text" name="add1" id="add1" tabindex="5" value="#qCustomerByPhoneURL.Add1#" cfsqltype="cf_sql_varchar" maxlength="50"></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">City:</td>
    <td width="50%" height="20"><cfinput type="text" name="city" id="city" tabindex="6" value="#qCityStateByZipURL.City#" maxlength="20" cfsqltype="cf_sql_varchar"></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">State:</td>
    <td width="50%" height="20">
        <cfselect name="State" id="State" tabindex="7">
        <cfoutput>
              <option value="#qCityStateByZipURL.State#" selected="selected">#qCityStateByZipURL.State#</option>
        </cfoutput>
    <option value="AK">AK</option>
    <option value="AL">AL</option>
    <option value="AR">AR</option>
    <option value="AZ">AZ</option>
    <option value="CA">CA</option>
    <option value="CO">CO</option>
    <option value="CT">CT</option>
    <option value="DC">DC</option>
    <option value="DE">DE</option>
    <option value="FL">FL</option>
    <option value="GA">GA</option>
    <option value="HI">HI</option>
    <option value="IA">IA</option>
    <option value="ID">ID</option>
    <option value="IL">IL</option>
    <option value="IN">IN</option>
    <option value="KS">KS</option>
    <option value="KY">KY</option>
    <option value="LA">LA</option>
    <option value="MA">MA</option>
    <option value="MD">MD</option>
    <option value="ME">ME</option>
    <option value="MI">MI</option>
    <option value="MN">MN</option>
    <option value="MO">MO</option>
    <option value="MS">MS</option>
    <option value="MT">MT</option>
    <option value="NC">NC</option>
    <option value="ND">ND</option>
    <option value="NE">NE</option>
    <option value="NH">NH</option>
    <option value="NJ">NJ</option>
    <option value="NM">NM</option>
    <option value="NV">NV</option>
    <option value="NY">NY</option>
    <option value="OH">OH</option>
    <option value="OK">OK</option>
    <option value="OR">OR</option>
    <option value="PA">PA</option>
    <option value="PR">PR</option>
    <option value="RI">RI</option>
    <option value="SC">SC</option>
    <option value="SD">SC</option>
    <option value="TN">TN</option>
    <option value="TX">TX</option>
    <option value="UT">UT</option>
    <option value="VA">VA</option>
    <option value="VT">VT</option>
    <option value="WA">WA</option>
    <option value="WI">WI</option>
    <option value="WV">WV</option>
    <option value="WY">WY</option>
</cfselect></td>
  </tr>
  <tr>
    <td width="25%" height="20" class="subHeader"> </td>
    <td width="25%" height="20" class="subHeader">Zip:</td>
    <td width="50%" height="20"><cfinput type="text" name="zip" validateat="onSubmit" validate="zipcode" id="zip" tabindex="8" value="#qCustomerByPhoneURL.Zip#" cfsqltype="cf_sql_varchar" maxlength="5"></td>
  </tr>
  <tr>
    <td width="25%" height="20" align="right"> </td>
    <td width="25%" height="20" align="right"><cfinput type="submit" name="Submit" value="Submit" id="Submit"></td>
    <td width="50%" height="20"><cfinput type="button" name="cancel" id="Reset" value="Cancel" onClick="history.back()"></td>
  </tr>
</table>
</cfform>    


CFC code
CODE

    <cffunction name="getCustomerByPhoneURL" access="public" returntype="query">
           <cfquery name="qCustomerByPhoneURL" datasource="DTDatabase">
            SELECT *
            FROM Customers
            WHERE Phone = <cfqueryparam value="#URL.Phone#" cfsqltype="cf_sql_clob">
        </cfquery>
        <cfreturn qCustomerByPhoneURL>
    </cffunction>  
    
    <cffunction name="getCityStateByZipURL" access="public" returntype="query">
        <cfquery name="qCityStateByZipURL" datasource="DTDatabase">
            SELECT *
            FROM Zips
            WHERE ZipCodeNum = <cfqueryparam value="#URL.Zip#" cfsqltype="cf_sql_clob" maxlength="10">
        </cfquery>
        <cfreturn qCityStateByZipURL>
    </cffunction>

    <cffunction  name="customeredit" access="public" returntype="void">
         <cfargument name="formdata" required="yes">
        <cfquery name="customeredit" datasource="DTDatabase">
            Update customers
            set firstname1 = '#formdata.firstname1#',
                lastname = '#formdata.lastname#',
                firstname2 = '#formdata.firstname2#',
                phone = '#formdata.phone#',
                add1 = '#formdata.add1#',
                zip = '#formdata.zip#'
            where phone = '#phone#'
           </cfquery>
    </cffunction>


Some Code I was trying out
CODE

     <cffunction name="customerzipedit" access="public" returntype="void">
        <cfargument name="formdata" type="struct" required="yes" value="#form#">      
             <cfquery name="customerzipedit" datasource="DTDatabase">
                        INSERT INTO Zips (City,State,ZipCodeNum)
                SELECT  '#formdata.city#', '#formdata.state#', '#formdata.zip#'
                WHERE   NOT EXISTS (
                SELECT  City
                FROM    Zips
                WHERE   City = '#formdata.city#'
                AND     State = '#formdata.state#'
                AND     ZipCodenum = '#formdata.zip#'
                )
                          
            </cfquery>
    </cffunction>


User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/8/09 04:06AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month