23 Replies Latest reply on May 21, 2007 4:22 AM by ace4u

    Multiple Values

    ace4u Level 1
      My db's primary table, ContractorInfo, has columns for each of your typical business's stats- i.e. name, address, zip, etc. Well, I have a very basic cf template that the client inputs thier zip to to find out if there is a member (contractor) in thier area and it works ok (thanks to the help of jdelin).
      The problem that I'm having is a:) that as I enroll the members, each member will have multiple zip codes and b:) any zip could have up to 3 members so, I need to setup my db so that each member has all of the zips in thier area associated with them.
      I also need not only my <cfoutput to render up to 3 results but to imbed these results in to one form so that when the form is submitted, it reaches all 3 members. The last thing is that I neede the form to be dynamic so that I don't have to keep reproducing it for the new members/ zips.Thanks, Andy
        • 1. Re: Multiple Values
          Level 7
          table ContractorInfo:
          contractorID - autonumber
          name - text
          address - text
          ...

          table ZIPCodes
          zipID - autonumber
          zipcode - text or number depending on your zip format

          table ContractorZIP
          contractorzipID - autonumber
          contractorID - number, FK to ContractorInfo table (contractoID field)
          zipID - number, FK to ZIPCodes table (zipID field)


          alternatively, depending on your requirements, you may not need the
          ZIPCodes table and instead store actual zip codes directly in the
          ContractorZIP table...

          --
          Azadi Saryev
          Sabai-dee.com
          Vientiane, Laos
          http://www.sabai-dee.com
          • 2. Re: Multiple Values
            Dan Bracuk Level 5
            I would do it slightly differently than Azadi. My ContractorZip table would have two fields only, the contractorID and the ZipId. The primary key would be both fields, and they would each be foreign keys to the contractor and zip tables.

            The reason is to prevent duplicate combinations of contractors and zip codes.
            • 3. Re: Multiple Values
              ace4u Level 1
              thanks guys!
              • 4. Re: Multiple Values
                ace4u Level 1
                This is not working out. SQL 2005 is not very user friendly. Please be more specific.
                I have 3 tables- contractorInfo, contractorZip and zipCodes. As you said, I made 2 tables in zipCodes, both primary keys. I'm having 2 problems with the foreign keys though. Even though I made the column's data type identical, SQL is saying thier not. Therefore, not allowing the relationship. 2) I'm not sure which columns to tie the FK to. Also, I have no autonumber option so I'm using int. Here is what the db looks like so that maybe you can give me more detail:

                CONT_INFO
                contID
                zipCode
                CompanyName
                .......


                ZIPCODE
                zipID
                zipCode


                CONT_ZIP
                zipID
                contID










                Will I have to enter data in to each field independently?
                ***Note: As I said previously, every contractor will have multiple zip codes and
                every zip can have up to 3 contractors. Finally, I have a zip code table in my db which was downloaded off the net (40,000 zips) should I tie to it to make my process easier and possibly use for dynamic content i.e.
                "Sorry, we do not have a member in #dbo.zipcodes.city# yet.
                • 5. Re: Multiple Values
                  Level 7

                  first, you do not need zipCode column in CONT_INFO table any more since
                  you will be storing all zip codes associated with a contractor in the
                  CONT_ZIP table.

                  Second, if all your zip codes in the ZIPCODE table are unique you can
                  drop the zipID column from that table, too, and have the zipCode column
                  as unique id.

                  so, now, your tables will have the following structure:

                  CONT_INFO:
                  contID [PK]
                  CompanyName
                  ...

                  ZIPCODE:
                  zipCode [PK]
                  ... (i.e. city/state/etc )

                  CONT_ZIP:
                  contzipID [PK]
                  zipCode [FK to ZIPCODE.zipCode]
                  contID [FK to CONT_INFO.cont_ID]


                  on your page that enteres contractors you can have a multiple-select
                  list with zip codes from ZIPCODE table. you can then select as many zip
                  codes associated with a contractor as you want. your form will store all
                  selected zip codes in a comma-delimited string (i.e. if your select box
                  has name="contractor_zips", then form.contractor_zips will hold all your
                  selected zip codes). so, when you process your form entries on the
                  form's action page:
                  - first insert all contractor info into the CONT_INFO table
                  - get the inserted contractor's ID
                  - loop through form.contractor_zips list and insert cont_ID you got in
                  step 2 and current list index into CONT_ZIP table

                  if yuo have a page that edits the contractor's info, then on that page
                  you have a multi-select list box as well with currently selected zip
                  codes pre-selected (shout if you need help on how to do this). then when
                  you process the edited info, update the CONT_INFO table with any
                  changes, then first delete all rows from CONT_ZIP table that have the
                  same contID as the contractor being edited, then insert the zip codes
                  selected in the list box and contID - this way is a lot easier than
                  trying to figure out which individual zip codes for the contractor to
                  remove/update...


                  --

                  Azadi Saryev
                  Sabai-dee.com
                  http://www.sabai-dee.com
                  • 6. Re: Multiple Values
                    ace4u Level 1
                    OK!? I'm sorry to seem stupid but I don't know half of what you just said such as multi-select. I do not have a form that writes to the db yet nor do I have any idea how to make that happen but, last week I didn't know what a select statement was and now, thanks to you people's support, I have a functioning site.
                    I do know that the next step is to build a password protected admin page that contains a form that will write to the db and I will have a print out of your post in front of me when I do but, keep this in mind Azadi: If you where asked all of the zip codes in your tri-county area would you know them? This form has to be very simple so that the people that I pay to get contractors signed up can just fill in a few fields and all the correct info is updated on the db. Also, when I tried to make a column unique ID it was asking me for a 16 digit number. (all work so far has been done directly on the db.)Thank you for your post, Andy
                    • 7. Re: Multiple Values
                      ace4u Level 1
                      Okay Azadi! You said to shout so....
                      Could you please provide me with the most basic syntax of a form that will populate my db?
                      • 8. Re: Multiple Values
                        Level 7
                        ace4u wrote:
                        > Okay Azadi! You said to shout so....
                        > Could you please provide me with the most basic syntax of a form that will populate my db?

                        sorry for a delay in getting back to you.... been busy with some stuff
                        here....

                        ok... where shall we start?..

                        first, a form will not populate your db by itself. a form will only
                        allow you (user) to enter information you want populated in your db or
                        processed in some other way.

                        a form usually has CONTROLS (i.e. textboxes, checkboxes, textareas,
                        select lists, button, etc) that users uses to enter or select data in
                        the form. CONTROLS should have NAMES, so data entered into them can be
                        accessed on the form's action page (see below). i hope you are familiar
                        with basic workings of a form and the <form> and <cfform> tags - if not,
                        you better read up on that.

                        a form SUBMITS when a user clicks on a submit button (or through another
                        trigger, i.e. javascript) and sends all the info entered/selected in it
                        to the ACTION PAGE, which is specified inside the <form> tag (if it is
                        not specified, the form will submit to itself, i.e. to the same page it
                        is on). depending on your personal preferences, you can have a separarte
                        action page for each form, or have your forms submit to the same page. i
                        personally like the latter. in any case, the ACTION PAGE is where you
                        validate (using server-side validation; you can also use client-side
                        validation, which is performed BEFORE the form is submitted, on the
                        actual form's page) and process the submitted information.

                        a form can submit its data to the action page using either POST or GET
                        methods, which are specified in the <form> tag. default, i believe, is
                        POST and that is what you should be using. with GET method all form's
                        data will be submitted as part of url - not very useful in most cases...

                        all the data entered/selected in the form is available on the action
                        page in the FORM scope (when POST submission method is used). to access
                        individual control's value (data entered into it in the form) you use
                        #form.controlname# syntax. some controls have a default value (even if
                        nothing has been entered into them - i.e. a textbox always has a value,
                        an empty string if nothing has been typed into it in the form), others
                        don't, and others may have not a single value, but a comma-delimited
                        list of values (i.e. a multi-select list, which is your regular <select>
                        tag but with "multiple" attribute in it; or if you have several controls
                        in your form with the same name then the form scope will hold a
                        comma-delimited list of values of all those controls in one
                        form.controlname variable). some controls (i.e. checkboxes) will not be
                        submitted with the form (and thus will not be in the FORM scope) if they
                        were not selected by user.

                        to populate your db with form's data you use <cfquery> tags with INSERT
                        or UPDATE sql statements depending on if you are inserting new data or
                        updating existing data. if you are not familiar with sql INSERT and
                        UPDATE statements you better read up on those, too. a book by Ben Forta
                        called something like "Teach yourself SQL in 10 minutes" has been
                        recommended a lot in these forums + there are a ton of websites with
                        tutorials and instructions. important thing here is that what you enter
                        into your db table and how depends on what datatype your table column
                        is... main point: any values in your insert/update queries that go into
                        TEXT fields in your db table should be surrounded with ' (single
                        quotes), while those that go into INTEGER (number) fields should not:
                        i.e. use '#form.textvalue#' and #form.integervalue# - see examples below
                        for details.

                        there are a multitude of other things you should know about forms and
                        sql statements, but i will leave you to learn them as you go. so,
                        without further ado, here is a simple example. i have purposefully NOT
                        made it based on your tables, so you can practice and make the form and
                        action page for your specific needs yourself :)

                        ===================================================
                        form page (form.cfm):
                        <!--- some basic form with a number of different controls --->

                        <form action="actionpage.cfm" method="post">
                        First Name: <input type="text" name="firstname" size="50"><br>
                        Last Name: <input type="text" name="lastname" size="50"><br>
                        Your Gender: <input type="radio" name="gender" value="M" checked>Male
                        <input type="radio" name="gender" value="F">Female<br>
                        Your Age: <input type="text" name="age" size="2"><br>
                        Your Interests:
                        <select name="interests" size="5" multiple>
                        <option value="movies">Movies</option>
                        <option value="theatre">Theatre</option>
                        <option value="ballet">Ballet</option>
                        <option value="opera">Opera</option>
                        <option value="coldfusion">CF programming</option>
                        <br>
                        <input type="submit" name="submit" value="save">
                        </form>

                        ====================================================
                        action page (actionpage.cfm):
                        <!---
                        here is where we process submitted form data.
                        this example is for INSERTING new data into db, not updating data.
                        this example inserts data into 2 tables: basic data (name, gender, age)
                        is inserted into one table, while selected interests are inserted into
                        second table which is linked to the first table through an FK.

                        tables structure:

                        tblUserData
                        (UserID [PK, autonumber], Firstname [text], Lastname [text], Gender
                        [text], Age [number])

                        tblUserInterests
                        (userinterestID [PK, autonumber], userID [FK to tblUserData.userID,
                        number], interestName [text])
                        --->

                        <cfif isdefined("form.save")><!--- check that a form has been submitted
                        and we have FORM scope available to us --->
                        <!--- insert basic data into tblUserData --->
                        <cfquery name="insertData" datasource="yourdsn">
                        INSERT INTO tblUserData (Firstname, Lastname, Gender, Age)
                        VALUES ('#form.firstname#', '#form.lastname#', '#form.gender#',
                        #form.age#)
                        </cfquery>

                        <!--- check if any interests have been selected in the form
                        and if they have, enter them into tblUserInterests --->
                        <cfif isdefined("form.interests") AND len(trim(form.interests)) gt 0>
                        <!--- get inserted user's userID --->
                        <cfquery name="getuserid" datasource="yourdsn">
                        SELECT MAX(userID) as userid FROM tblUserData
                        </cfquery>

                        <!--- loop through submitted interests and insert them --->
                        <cfloop list="#form.interests#" index="interestName">
                        <cfquery name="insertInterest" datasource="yourdsn">
                        INSERT INTO tblUserInterests (userID, interestName)
                        VALUES (#getuserid.userid#, '#interestName#')
                        </cfquery>
                        </cfloop>
                        </cfif>
                        <cflocation url="whereveryouwanttogoafterinsertingdataintoyourdb"
                        addtoken="no">
                        </cfif>

                        ==============================================================
                        the above example is far from perfect, but it should set you on your
                        way. as always, the forums are here if you hit a roadblock...

                        hope this helps...

                        --

                        Azadi Saryev
                        Sabai-dee.com
                        http://www.sabai-dee.com
                        • 9. Re: Multiple Values
                          ace4u Level 1
                          Hi Azadi. Sorry it took so long to get back but its been a busy week. Anyway thank you very much for taking the time to show me this stuff. I have most of the weekend to learn it but while I do, please consider this scenario:
                          I have my form all setup to INSERT the user data. This form will only be available to contractors but, I send them this form with text fields for company name, address & zip code. They fill out the form and submit it- great! Except for one problem- the zip code. They will have submitted thier zip code but they actually have several zips in thier service area. I cant possibly expect them to be able to know all of thier service area's zips because I dot know half of mine. I service the Tampa Bay Area and I probably have 50-60 zip or more. The only way the consumer will find this contractor is if they hit on the one zip which was submitted in the form.
                          Anyway, I'll probably have some much more basic questions than that but I just wanted you to be aware of the end result that I'm looking for. Thanks again! Andy
                          • 10. Re: Multiple Values
                            ace4u Level 1
                            Question & Comment:
                            Q: Do I design the table structure in SQL Management Studio?

                            C: dbo.ZipCodeDatabase has the following structure and I believe it is like that to enable a scenario like the one I described above. Here are it's columns:
                            Zip_code (float), Latitude (float), Longitude (float), Zip_Class (nvarchar255), City (nvarchar255), State_Number (float), County_Number (float), upsize_ts(timestamp)
                            Should I use this (already-populated) table or just make a new one. I can already see if I do use it, it will make my cfquery pretty complicated.
                            • 11. Re: Multiple Values
                              ace4u Level 1
                              Oh No Azadi!
                              I set the tables up in Magagement Studio to the specs. that you outlined which entailed deleting the column "PostalCode" from electricalContractorInfo. The problem is that the following SQL statement is the crux of my entire site:

                              SELECT *
                              FROM dbo.ElectricalContractorInfo
                              WHERE dbo.ElectricalContractorInfo.PostalCode = #form.myZip#
                              Please go to www.electriciansnet.com. You will see that form.myZip is the main operator. My zip code db is full of zips so I have to make it equal something else.
                              • 12. Re: Multiple Values
                                ace4u Level 1
                                Never mind...

                                SELECT *
                                FROM dbo.ElectricalContractorInfo, dbo.contractor_Zip, dbo.ZipCodeDatabase
                                WHERE dbo.contractor_Zip.zipCode = #form.myZip#
                                • 13. Re: Multiple Values
                                  ace4u Level 1
                                  What a mess I have made!!!
                                  Here is the 1st error but nothing is getting written to the db. I think I have the tables setup just like you said. Anyway- ERROR:
                                  The error occurred in C:\Inetpub\wwwroot\electriciansnet.com\enroll.cfm: line 65

                                  63 : <cfquery name="session.insertData" datasource="#enet#">
                                  64 : INSERT INTO tbldbo.ElectricalContractorInfo (memberID, companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, License, CellNumber, NetworkID, Slogan)
                                  65 : VALUES ('#form.myCompany#', '#frm.myAddress#', '#form.myCity#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#',
                                  66 : #form.license#, '#form.cell_phone#', 'form.carrier', 'form.slogan')
                                  ____________________________________________________________
                                  The process works until I get to the action page and I think it is because I have a query at the front that retrieves the zip codes from the zip code table.

                                  city.cfm:

                                  <cfFORM ACTION="contractor.cfm" METHOD="post" name="enterZip" preloader="no">


                                  <TR><TD VALIGN=TOP>
                                  <cfinput type="TEXT" name="myCompany" message="Please enter your Company Name." required="Yes" size="30" class="shadeform">Company Name<BR>
                                  <cfinput type="TEXT" name="myAddress" message="Please enter your company's address" required="Yes" size="30" class="shadeform"> Address<BR>
                                  <BR>

                                  <cfINPUT TYPE="TEXT" NAME="myCity" required="Yes" SIZE="30" CLASS="shadeform"> City<BR>

                                  <cfinput type="submit" NAME="submitCompany">

                                  </cfFORM>


                                  contractor.cfm
                                  <!---This gets the zip codes--->
                                  <CFQUERY NAME="getZips" DATASOURCE="enetdb1sql">
                                  SELECT CITY, COUNTY_NUMBER, ZIP_CODE
                                  FROM dbo.ZipCodeDatabase
                                  WHERE dbo.ZipCodeDatabase.CITY LIKE '#form.myCity#'


                                  </CFQUERY>

                                  <cfform name="frmEnroll" Action="enroll.cfm" Method="Post">
                                  <select name="ZipCodes" size="5" multiple height="30" width="30">
                                  <option value="zips" <cfif (isDefined("getZips.ZIP_CODE") AND "zips" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>{ZIP_CODE}</option>
                                  <cfoutput query="getZips">
                                  <option value="#getZips.ZIP_CODE#" <cfif (isDefined("getZips.ZIP_CODE") AND getZips.ZIP_CODE EQ getZips.ZIP_CODE)>selected="selected"</cfif>>#getZips.ZIP_CODE#</option>
                                  </cfoutput>
                                  </select>

                                  <cfINPUT TYPE="TEXT" NAME="Company" value="#Form.myCompany#" SIZE="30" Message="Please enter your Company Name." required="Yes" CLASS="shadeform"> Company Name<BR>

                                  </TD></TR><TR><TD>

                                  <cfINPUT NAME="address" TYPE="TEXT" CLASS="shadeform" value="#Form.myAddress#" SIZE="30" Message="Please enter your Address." required="Yes">
                                  Address <BR>

                                  <cfINPUT TYPE="text" NAME="city" value="#form.myCity#" visible="true" CLASS="shadeform" readonly="true"> City <BR>


                                  State: <BR>
                                  • 14. Re: Multiple Values
                                    ace4u Level 1
                                    continued:

                                    <select name="states" class="shadeform">
                                    <option value="" selected <cfif (isDefined("getZips.ZIP_CODE") AND "" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Choose a State
                                    <option value="UNK" <cfif (isDefined("getZips.ZIP_CODE") AND "UNK" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Outside US / Canada
                                    <option value="AL" <cfif (isDefined("getZips.ZIP_CODE") AND "AL" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Alabama
                                    <option value="AK" <cfif (isDefined("getZips.ZIP_CODE") AND "AK" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Alaska
                                    <option value="AB" <cfif (isDefined("getZips.ZIP_CODE") AND "AB" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Alberta
                                    <option value="AS" <cfif (isDefined("getZips.ZIP_CODE") AND "AS" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>American Samoa
                                    <option value="AZ" <cfif (isDefined("getZips.ZIP_CODE") AND "AZ" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Arizona
                                    <option value="AR" <cfif (isDefined("getZips.ZIP_CODE") AND "AR" EQ getZips.ZIP_CODE)>selected="selected"</cfif>>Arkansas

                                    ...all states
                                    </select>

                                    <cfINPUT TYPE="TEXT" NAME="first_name" required="Yes" SIZE="30" CLASS="shadeform"> First Name <BR>



                                    <cfINPUT TYPE="TEXT" NAME="last_name" required="Yes" SIZE="30" CLASS="shadeform"> Last Name <BR>



                                    <cfINPUT TYPE="TEXT" NAME="title" required="Yes" SIZE="30" CLASS="shadeform"> Title <BR>




                                    <cfINPUT TYPE="TEXT" NAME="PhoneO" required="yes" SIZE="30" CLASS="shadeform">
                                    Office Phone<BR>




                                    <cfINPUT TYPE="TEXT" NAME="email" required="no" SIZE="30" CLASS="shadeform"> eMail <BR>



                                    <cfINPUT TYPE="TEXT" NAME="url" Message= required="no" SIZE="30" CLASS="shadeform"> Web Address <BR>




                                    <cfINPUT TYPE="TEXT" NAME="license" required="no" CLASS="shadeform">
                                    License Number <BR>



                                    <cfINPUT TYPE="TEXT" NAME="cell_phone" required="Yes" SIZE="30" CLASS="shadeform"> Cell Number


                                    Cellular Phone Carrier:<br></TD></TR><TR><TD>


                                    <cfselect name="carrier" class="dropdown">
                                    <option selected value="noneSelected">Please Select A Carrier ----></option>
                                    <option value="1">Alltell</option>
                                    <option value="2">AT&T </option>
                                    <option value="3">Cingular</option>
                                    <option value="4">Not Listed</option>
                                    <option value="5">Nextel</option>
                                    <option value="6">Sprint</option>
                                    <option value="7">T Mobile</option>
                                    <option value="8">Verizon</option>
                                    <option value="9">Virgin Mobile</option>
                                    </cfselect>


                                    <BR><BR>

                                    </TD></TR><TR><TD>

                                    Company Slogan:<BR>

                                    </td></tr></table>


                                    <TEXTAREA NAME="slogan" ROWS="6" COLS="30" wrap=soft CLASS="textarea"></TEXTAREA>

                                    <br><br>

                                    <cfinput type="submit" name="enroll" value="Register" class="submitbutton"><br>
                                    </cfFORM>


                                    Enroll.cfm:

                                    <cfif isdefined("form.Register")><!--- check that a form has been submitted
                                    and we have FORM scope available to us --->
                                    <!--- insert basic data into tblUserData --->
                                    <cfquery name="insertData" datasource="enetdb1sql">
                                    INSERT INTO tbldbo.ElectricalContractorInfo (memberID, companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, License, CellNumber, NetworkID, Slogan)
                                    VALUES ('#Form.myCompany#', '#form.myAddress#', '#form.myCity#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#',
                                    #form.license#, '#form.cell_phone#', 'form.carrier', 'form.slogan')
                                    </cfquery>

                                    <!--- check if any zip codes have been selected in the form
                                    and if they have, enter them into tblUserInterests --->
                                    <cfif isdefined("form.ZipCodes") AND len(trim(form.ZipCodes)) gt 0>
                                    <!--- get inserted user's userID --->
                                    <cfquery name="getMemberID" datasource="enetdb1sql">
                                    SELECT (MemberID) as MemberID FROM tbldbo.ElectricalContractorInfo
                                    </cfquery>

                                    <!--- loop through submitted zip codes and insert them --->
                                    <cfloop list="#form.ZipCodes#" index="dbo.contractor_Zip.zipCode">
                                    <cfquery name="insertZipCodes" datasource="enetdb1sql">
                                    INSERT INTO tbldbo.contractor_Zip (memberZipID, MemberID, zipCode)
                                    VALUES (#getmemberZipID.MemberID#, '#zipCode#', #MemberID#)
                                    </cfquery>
                                    </cfloop>
                                    </cfif>
                                    <cflocation url="../thanks.cfm" addtoken="no">
                                    addtoken="no">
                                    </cfif>


                                    </body>
                                    </html>






                                    I tried scoping the query to "session." since that is what worked the last time but it is not seeing any of these elements.

                                    I did not start getting unrecognised elements until I took out your <cfif tags to try to see why nothing was being written to the db.
                                    Sorry this post is getting so bulky. I would realy appreciate the help. Thanks, Andy
                                    • 15. Re: Multiple Values
                                      Level 7
                                      first things first:

                                      i think the error is due to the fact that form.myCity is NOT defined in
                                      your form on contractor.cfm page - you only pass that variable to
                                      contractor.cfm page from city./cfm page, but it is not in the form scope
                                      of contractor.cfm form...

                                      you should add a hidden text field to the form on the contractor.cfm
                                      page and set its value to #form.myCity#
                                      --

                                      Azadi Saryev
                                      Sabai-dee.com
                                      http://www.sabai-dee.com
                                      • 16. Re: Multiple Values
                                        ace4u Level 1
                                        Yeah I just found it. I was refering to the wrong form when I said myCompany. I should have said company which is read only and populated by myCompany on the prior form. I'm narrowing down my errors now and I can see on the bottom half of the error that the server recognises the sql statement all the way down to "carrier".
                                        I have a dropdown select for the person's cell phone carrier which is another table in the db. 1= Alltel, 2=AT&T etc. That is how the consumers contast form is addressed to the member so that the message goes to the member's cell phone.
                                        BUT, it does make it a lot more complex. Table- networkID will not let me make it's PK a FK to electricalContractorInfo'.networkID.
                                        What I am doing right now is coppying/passting your cfif and cfloop statements that I am attenpting to use for the zip codes and seeing what happends.
                                        • 17. Re: Multiple Values
                                          ace4u Level 1
                                          Had to put a NOT in your isDefined statement to make anything work but here is what I am trying to do:

                                          <cfif NOT IsDefined("frmEnroll.save")><!--- check that a form has been submitted
                                          and we have FORM scope available to us --->
                                          <!--- insert basic data into tblUserData --->
                                          <cfquery name="insertData" datasource="#enet#">
                                          INSERT INTO dbo.ElectricalContractorInfo ( companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, License, CellNumber, NetworkID, Slogan)
                                          VALUES ('#Form.company#', '#form.address#', '#form.city#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#',
                                          #form.license#, '#form.cell_phone#', 'form.slogan')
                                          </cfquery>


                                          <!--- check if any cell carrier has been selected in the form
                                          and if they have, enter them into tblUserInterests --->
                                          <cfif isdefined("form.carrier") AND len(trim(form.carrier)) gt 0>
                                          <!--- loop through submitted zip codes and insert them --->
                                          <cfloop list="#form.carrier#" index="dbo.contractor_Zip.zipCode">
                                          <cfquery name="insertNetworkID" datasource="#enet#">
                                          INSERT INTO dbo.ElectricalContractorInfo (NetworkID,)
                                          VALUES (#getmemberZipID.MemberID#, '#zipCode#', #MemberID#)
                                          </cfquery>
                                          </cfloop>
                                          </cfif>
                                          <!--- check if any zip codes have been selected in the form
                                          and if they have, enter them into tblUserInterests --->
                                          <cfif isdefined("form.ZipCodes") AND len(trim(form.ZipCodes)) gt 0>
                                          <!--- get inserted user's userID --->
                                          <cfquery name="getMemberID" datasource="#enet#">
                                          SELECT MAX (MemberID) as MemberID FROM tbldbo.ElectricalContractorInfo
                                          </cfquery>

                                          <!--- loop through submitted zip codes and insert them --->
                                          <cfloop list="#form.ZipCodes#" index="dbo.contractor_Zip.zipCode">
                                          <cfquery name="insertZipCodes" datasource="enetdb1sql">
                                          INSERT INTO dbo.contractor_Zip (memberZipID, MemberID, zipCode)
                                          VALUES (#getmemberZipID.MemberID#, '#zipCode#', #MemberID#)
                                          </cfquery>
                                          </cfloop>
                                          </cfif>
                                          <cflocation url="../thanks.cfm" addtoken="no">

                                          </cfif>
                                          • 18. Re: Multiple Values
                                            ace4u Level 1
                                            This is what I'm trying:

                                            <cfif isdefined("form.carrier") AND len(trim(form.carrier)) gt 0>
                                            <!--- loop through submitted carrier and insert it --->
                                            <cfloop list="#form.carrier#" index="dbo.NetworkID.NetworkID">
                                            <cfquery name="insertNetworkID" datasource="#enet#">
                                            INSERT INTO dbo.ElectricalContractorInfo (NetworkID)
                                            VALUES (#insertNetworkID.networkID#, '#zipCode#', #MemberID#)
                                            </cfquery>
                                            • 19. Re: Multiple Values
                                              ace4u Level 1
                                              Please allow me to close for the night with this error:

                                              [Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.

                                              The error occurred in C:\Inetpub\wwwroot\electriciansnet.com\enroll.cfm: line 70

                                              68 : INSERT INTO dbo.ElectricalContractorInfo (companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, CellNumber, NetworkID, Slogan)
                                              69 : VALUES ('#Form.company#', '#form.address#', '#form.city#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#',
                                              70 : '#form.cell_phone#', '#form.carrier#', '#slogan#')
                                              71 : </cfquery>
                                              72 :

                                              SQL INSERT INTO dbo.ElectricalContractorInfo (companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, CellNumber, NetworkID, Slogan) VALUES ('ACEnterprises Inc.', '8728 N Hyaleah Rd.', 'Tampa', 'FL', 'Andrew', 'carter', 'President', '813-244-0620', 'andy@electriciansnet.com', 'www.electriciansnet.com', '8137895581', '6', 'Power, Voice, Data')
                                              DATASOURCE enetdb1sql
                                              VENDORERRORCODE 8152
                                              • 20. Re: Multiple Values
                                                ace4u Level 1
                                                Got that problem figured out by changing data types to all "nvarcgar(MAX) but I have not yet been able to get past errors and write to the tables because sql is not seeing this statement:

                                                <cfquery name="getMemberID" datasource="#enet#">
                                                INSERT INTO dbo.ElectricalContractorInfo (memberID)
                                                SELECT MAX (memberID) as memberID FROM dbo.ElectricalContractorInfo
                                                </cfquery>

                                                and then my next error will probably be this one:

                                                <!--- check if any cell carrier has been selected in the form
                                                and if they have, enter them into tblUserInterests --->
                                                <cfif isdefined("form.carrier") AND len(trim(form.carrier)) gt 0>
                                                <!--- loop through submitted carrier and insert it --->
                                                <cfloop list="#form.carrier#" index="dbo.NetworkID.NetworkID">
                                                <cfquery name="insertNetworkID" datasource="#enet#">
                                                INSERT INTO dbo.ElectricalContractorInfo (NetworkID)
                                                VALUES (#insertNetworkID.networkID#, '#zipCode#', #MemberID#)<MUST GO
                                                </cfquery>
                                                </cfloop>
                                                </cfif>

                                                and if I get those two, the last rode block will be this:

                                                <!--- check if any zip codes have been selected in the form
                                                and if they have, enter them into tblUserInterests --->
                                                <cfif isdefined("form.ZipCodes") AND len(trim(form.ZipCodes)) gt 0>
                                                <!--- get inserted user's userID --->

                                                <!--- loop through submitted zip codes and insert them --->
                                                <cfloop list="#form.ZipCodes#" index="dbo.contractor_Zip.zipCode">
                                                <cfquery name="insertZipCodes" datasource="enetdb1sql">
                                                INSERT INTO dbo.contractor_Zip (memberZipID, MemberID, zipCode)
                                                VALUES (#getmemberZipID.MemberID#, '#zipCode#', #MemberID#)
                                                </cfquery>
                                                </cfloop>
                                                </cfif>
                                                <cflocation url="../thanks.cfm" addtoken="no">
                                                </cfif>
                                                Thanks, Andy
                                                • 21. Re: Multiple Values
                                                  Level 7
                                                  i am afraid i have lost you there in all the little snippets of code...
                                                  can you post your full code for the form and action page? or just email
                                                  it directly to me.


                                                  --

                                                  Azadi Saryev
                                                  Sabai-dee.com
                                                  http://www.sabai-dee.com
                                                  • 22. Re: Multiple Values
                                                    ace4u Level 1
                                                    ABSOLUTELY- PLEASE!

                                                    <!--- check that a form has been submitted
                                                    and we have FORM scope available to us --->
                                                    <cfif NOT IsDefined("form.save")> (Had to change to NOT to get anything to work)
                                                    <!--- insert basic data into tblUserData --->

                                                    (Seems to work with all columns set as passive as possible)
                                                    <cfquery name="insertData" datasource="#enet#">
                                                    INSERT INTO dbo.ElectricalContractorInfo (companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, CellNumber, NetworkID, Slogan)
                                                    VALUES ('#Form.company#', '#form.address#', '#form.city#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#',
                                                    '#form.cell_phone#', '#form.carrier#', '#slogan#')
                                                    </cfquery>

                                                    <!--- check if any zip codes have been selected in the form
                                                    and if they have, enter them into tblUserInterests --->
                                                    <cfif isdefined("form.ZipCodes") AND len(trim(form.ZipCodes)) gt 0>

                                                    (Failing to see what writes to memberzipid)
                                                    <!--- get inserted Member's userID --->
                                                    <cfquery name="getMemberID" datasource="#enet#">
                                                    SELECT MAX (memberID) AS memberID FROM dbo.ElectricalContractorInfo(Trys to write to last, populated line)
                                                    INSERT INTO dbo.ElectricalContractorInfo (memberID) (Added this line myself)
                                                    </cfquery>
                                                    Does not work at all and does not account for dbo.contractor_Zip.memberZipID
                                                    <!--- loop through submitted zip codes and insert them --->
                                                    <cfloop list="#form.ZipCodes#" index="zipCode">
                                                    <cfquery name="insertZipCodes" datasource="#enet#">
                                                    INSERT INTO dbo.ElectricalContractorInfo (MemberID)
                                                    VALUES (#getmemberID.memberID#)
                                                    </cfquery>
                                                    </cfloop>

                                                    I changed the order of the querys and got it past a couple of errors but I put it back close to the way you had it so you may be able to tell me more.)

                                                    Table elecontinf:
                                                    MemberID (PK INT) ConpanyName and all others set to (varchar(MAX))

                                                    Table contractorzip:
                                                    memberZipID (PK INT, will not allow FK to anything)
                                                    memberID (PK INT, FK to contractorInfo.memberID)
                                                    zip code varchar(MAX)

                                                    Table ZipCodeDatabase
                                                    Being used for query on 1st form page. (City.cfm)You enter your city and it populates form.zipCodes in the next form page (contractor.cfm) which is what I’m trying to get CF to loop through to enter these zips in dbo.contractorZip. This part works ok but only allows for one city.
                                                    Please go to www.electriciansnet.com/city.cfm and you will see what I mean. Thanks Azadi
                                                    p.s. very nice site you have!

                                                    • 23. Re: Multiple Values
                                                      ace4u Level 1
                                                      Maybe my posts are too long so you stopped answering but can you please be more specific with this one, Dan? I have been working on this problem for days and days. 2 contractors could have the same zip. Would it go:
                                                      contractorID 1 zipID1
                                                      contractorID 1 zipID2
                                                      contractorID 1 zipID3
                                                      contractorID 1 zipID4

                                                      contractorID 2 zipID5
                                                      contractorID 2 zipID6
                                                      contractorID 2 zipID7
                                                      contractorID 2 zipID8 ?
                                                      I personally have 82 zips in my service area.
                                                      I have figured out how to insert in several ways but I cannot figure out which is right.