Skip to main content
S
szafranman
Known Participant
January 6, 2011
Answered

2 Form Fields into one DB Entry

  • January 6, 2011
  • 2 replies
  • 2431 views

I apologize in advance if this question has been asked and answered multiple times. I am new to this and extremely frustrated because I keep getting stuck.

I am using Dreamweaver to create a website with Coldfusion as the server. I am using Quickbooks and QODBC to use the DB to integrate with CF.

I have created a form with multiple fields all text entries. I have been able to get all the information to post into my database correctly. However my question is I want to create a multiple entry that would combine two form fields into one column in the database table. For instance I have First Name and Last Name as form fields when the user submits I want these to both enter into their respective columns in the table but also combine into one entry with format Last Name, First Name into a FULL NAME Column in the table. Is this possible if so how????? Thanks in advance.

    This topic has been closed for replies.
    Correct answer meensi

    meensi thanks for your quick response. Let me clear two things up I am using ColdFusion 9 (not sure if it matters) and also I had it using "cfelse" and clob because I created a form and than manually clicked the server behaviors---->insert record and matched each form field with the DB table entry. So Dreamweaver/Coldfusion built in code set that string of code up for me. But I have since gone back and re-written it to adhere to your advice but am now getting this error:

    Error Executing Database Query.

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][QODBC] Expected lexical element not found:
    The error occurred in C:\inetpub\wwwroot\CFIDE\testsite\registration_page.cfm: line 36
    34 :     
35 :     <cfif IsDefined('form.zipcode') and len(form.zipcode) NEQ 0>
36 :     <cfset strBillAddressPostalCode=form.zipcode/>
37 :     </cfif>
38 : )
    VENDORERRORCODE  11015
    SQLSTATE  42000
    SQL   INSERT INTO Customer (Name, LastName, FullName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode) VALUES ( )
    DATASOURCE  QBs

    this is my new code just the cf fuction (I didn't change the second DB entry line of code yet bc I didn't think it would matter so it will show still using the cfquerparm and cfelse for that section.):

    <cfif IsDefined("FORM.username")>

      <cfquery name="MM_search" datasource="Access">

        SELECT Logininfo.Username FROM Logininfo WHERE Logininfo.Username=<cfqueryparam value="#FORM.username#" cfsqltype="cf_sql_clob" maxlength="50">

      </cfquery>

      <cfif MM_search.RecordCount GTE 1>

        <cflocation url="taken.cfm?requsername=#FORM.username#" addtoken="no">

      </cfif>

    </cfif>

    <cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>

    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "customer">

      <cfquery datasource="QBs">  

        INSERT INTO Customer (Name, LastName, FullName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode)

    VALUES (<cfif IsDefined('form.firstname') and len(form.firstname) NEQ 0>

    <cfset strName=form.firstname/>

        </cfif>

        <cfif IsDefined('form.lastname') and len(form.lastname) NEQ 0>

        <cfset strLastName=form.lastname/>

        </cfif>

        <cfset FullName=strName &''& LastName/>

        <cfif IsDefined('form.streetaddress') and len(form.streetaddress) NEQ 0>

        <cfset strBillAddressAddr2=form.streetaddress/>

        </cfif>

        <cfif IsDefined('form.city') and len(form.city) NEQ 0>

        <cfset strBillAddressCity=form.city/>

        </cfif>

        <cfif IsDefined('form.state') and len(form.state) NEQ 0>

        <cfset strBillAddressState=form.state/>

        </cfif>

        <cfif IsDefined('form.zipcode') and len(form.zipcode) NEQ 0>

        <cfset strBillAddressPostalCode=form.zipcode/>

        </cfif>

    )

      </cfquery>

    <cfquery datasource="Access">

    INSERT INTO Logininfo (FirstName, LastName, Username, Password)

    VALUES (<cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ "">

    <cfqueryparam value="#FORM.firstname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ "">

    <cfqueryparam value="#FORM.lastname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.username") AND #FORM.username# NEQ "">

    <cfqueryparam value="#FORM.username#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.password") AND #FORM.password# NEQ "">

    <cfqueryparam value="#FORM.password#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    )

    </cfquery>

      <cflocation url="login.cfm">

    </cfif>

    thanks for your help and I apologize for my rookie mistakes in advance!!!


    Hi,

    In the first query, where is the comma to separate the values.??? Thats the problem I think.

    This is how your code should be.

    1. set the values in the variables.

    2. use the varaibles inside your CFQUERYPARAM

    <cfif IsDefined('form.firstname') and len(form.firstname) NEQ 0>

    <cfset strName=form.firstname/>

    </cfif>

    <cfif IsDefined('form.lastname') and len(form.lastname) NEQ 0>

        <cfset strLastName=form.lastname/>

        </cfif>

    <cfset FullName=strName &''& LastName/>

      <cfif IsDefined('form.streetaddress') and len(form.streetaddress) NEQ 0>

        <cfset strBillAddressAddr2=form.streetaddress/>

        </cfif>

        <cfif IsDefined('form.city') and len(form.city) NEQ 0>

        <cfset strBillAddressCity=form.city/>

        </cfif>

        <cfif IsDefined('form.state') and len(form.state) NEQ 0>

        <cfset strBillAddressState=form.state/>

        </cfif>

        <cfif IsDefined('form.zipcode') and len(form.zipcode) NEQ 0>

        <cfset strBillAddressPostalCode=form.zipcode/>

        </cfif>

    Please fill the maxlength and cfsqltype accordingly, also in your first query, billaddressAddr1 is missing (check that also); In the query i have added.

    <cfquery datasource="QBs">

        INSERT INTO Customer  (Name, LastName, FullName, BillAddressAddr1, BillAddressAddr2,  BillAddressCity, BillAddressState, BillAddressPostalCode)

    VALUES (

          <cfqueryparam value="#strName#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#strLastName#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#FullName#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#BillAddressAddr1#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#strBillAddressAddr2#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#strBillAddressCity#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#strBillAddressState#" cfsqltype="cf_sql_clob" maxlength="25"/>,

          <cfqueryparam value="#strBillAddressPostalCode#" cfsqltype="cf_sql_clob" maxlength="25"/>)

      </cfquery>

    I hope this will work..

    2 replies

    B
    BrianKeszler
    Participant
    January 8, 2011

    I know your question has been answered, but let me try to clear up a few things.

    Putting 2 form fields into 1 db field is as easy as <cfqueryparam value="#form.variable1# #form.variable2#" cfsqltype="cf_sql_clob" maxlength="50"/>

    You simply put both form variables inside the double quotes for the value parameter.

    I notice you are converting all your form variables to local variables:

    <cfif IsDefined('form.firstname') and len(form.firstname) NEQ 0>
      <cfset #FirstName#=form.firstname/>
    </cfif>

    Form.Firstname will always be defined because it's part of the form on the page that submits to this one. That's a good thing too, because if it didn't exist then your query would fail because you don't have a cfelse to handle when it doesn't! Have you tested to see what happens when the firstname field isn't filled in? The len(form.firstname) will be 0, your cfif will fail, the local variable FirstName won't be set, and your query will fail.

    My point is, you don't need all the cfif statements... you can simply use the cfqueryparam tags with the form variable names: <cfqueryparam value="#form.FirstName#" cfsqltype="cf_sql_clob" maxlength="50"/>

    If the user doesn't fill in a field like firstname, the cfqueryparam tag will insert nothing into that field. (not a null value, the field will literally be nothing, as in "")

    I hope that doesn't confuse things for you. Worst case, just ignore my advice here and keep your page as-is, since you said it's working fine!

    S
    szafranmanAuthor
    Known Participant
    January 6, 2011

    This is my current code::

    <cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>

    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "customer">

      <cfquery datasource="QBs">  

        INSERT INTO Customer (Name, FirstName, LastName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode)

    VALUES (<cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ "">

    <cfqueryparam value="#FORM.lastname#" cfsqltype="cf_sql_clob" maxlength="41">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ "">

    <cfqueryparam value="#FORM.firstname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ "">

    <cfqueryparam value="#FORM.lastname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ "">

    <cfqueryparam value="#FORM.firstname#" cfsqltype="cf_sql_clob" maxlength="41">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.streetaddress") AND #FORM.streetaddress# NEQ "">

    <cfqueryparam value="#FORM.streetaddress#" cfsqltype="cf_sql_clob" maxlength="41">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.city") AND #FORM.city# NEQ "">

    <cfqueryparam value="#FORM.city#" cfsqltype="cf_sql_clob" maxlength="31">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.state") AND #FORM.state# NEQ "">

    <cfqueryparam value="#FORM.state#" cfsqltype="cf_sql_clob" maxlength="21">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.zipcode") AND #FORM.zipcode# NEQ "">

    <cfqueryparam value="#FORM.zipcode#" cfsqltype="cf_sql_clob" maxlength="13">

    <cfelse>

    ''

    </cfif>

    )

      </cfquery>

    <cfquery datasource="Access">

    INSERT INTO Logininfo (FirstName, LastName, Username, Password)

    VALUES (<cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ "">

    <cfqueryparam value="#FORM.firstname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ "">

    <cfqueryparam value="#FORM.lastname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.username") AND #FORM.username# NEQ "">

    <cfqueryparam value="#FORM.username#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.password") AND #FORM.password# NEQ "">

    <cfqueryparam value="#FORM.password#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    )

    </cfquery>

      <cflocation url="thankyou.cfm">

    </cfif>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">

    <head>

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

    <title>Untitled Document</title>

    <script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>

    <script src="SpryAssets/SpryValidationPassword.js" type="text/javascript"></script>

    <link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />

    <link href="SpryAssets/SpryValidationPassword.css" rel="stylesheet" type="text/css" />

    </head>

    <body>

    <h1>Welcome to our sign up Page!</h1>

    <p>Please fill out the form below to register with out site and gain access to our members account page.</p>

    <form name="customer" action="<cfoutput>#CurrentPage#</cfoutput>" method="POST" id="customer"><table width="auto" border="1">

      <tr>

        <td><label for="firstname">

          <div align="right">First Name:</div>

          </label></td>

        <td><span id="sprytextfield1">

          <input type="text" name="firstname" id="firstname" accesskey="n" tabindex="05" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="lastname">

          <div align="right">Last Name:</div>

          </label></td>

        <td><span id="sprytextfield2">

          <input type="text" name="lastname" id="lastname" accesskey="n" tabindex="10" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="streetaddress">

          <div align="right">Street Address</div>

          </label></td>

        <td><span id="sprytextfield3">

          <input type="text" name="streetaddress" id="streetaddress" accesskey="n" tabindex="15" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="city">

          <div align="right">City:</div>

          </label></td>

        <td><span id="sprytextfield4">

          <input type="text" name="city" id="city" accesskey="n" tabindex="20" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="state">

          <div align="right">State:</div>

          </label></td>

        <td><span id="sprytextfield5">

          <input type="text" name="state" id="state" accesskey="n" tabindex="25" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="zipcode">

          <div align="right">Zipcode:</div>

          </label></td>

        <td><span id="sprytextfield6">

          <input type="text" name="zipcode" id="zipcode" accesskey="n" tabindex="30" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="username">

          <div align="right">Username:</div>

        </label></td>

        <td><span id="sprytextfield7">

          <input type="text" name="username" id="username" accesskey="n" tabindex="40" />

          <span class="textfieldRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td><label for="password">

          <div align="right">Password:</div>

        </label></td>

        <td><span id="sprypassword1">

          <input type="password" name="password" id="password" accesskey="n" tabindex="45" />

          <span class="passwordRequiredMsg">A value is required.</span></span></td>

      </tr>

      <tr>

        <td colspan="2"><div align="center">

          <input type="submit" name="submit" id="submit" value="Register" accesskey="n" tabindex="50" />

        </div></td>

        </tr>

    </table>

      <input type="hidden" name="MM_InsertRecord" value="customer" />

    </form>

    <script type="text/javascript">

    var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1");

    var sprytextfield2 = new Spry.Widget.ValidationTextField("sprytextfield2");

    var sprytextfield3 = new Spry.Widget.ValidationTextField("sprytextfield3");

    var sprytextfield4 = new Spry.Widget.ValidationTextField("sprytextfield4");

    var sprytextfield5 = new Spry.Widget.ValidationTextField("sprytextfield5");

    var sprytextfield6 = new Spry.Widget.ValidationTextField("sprytextfield6");

    var sprytextfield7 = new Spry.Widget.ValidationTextField("sprytextfield7");

    var sprypassword1 = new Spry.Widget.ValidationPassword("sprypassword1");

    </script>

    </body>

    </html>

    M
    meensi
    Inspiring
    January 6, 2011

    Hi,

    First of all, why are you having too may CFQUERYPARAM in <cfif> and <cfelse>

    are you not doing anything in CFELSE???

    If you are not doing anything in the CFELSE, need not specify that.

    It can be like this.

    <cfif IsDefined('form.firstname') and len(form.firstname) neq 0>

         <cfset strFirstName = form.firstname/>

    </cfif>

    The answer for your question..:

    <cfif IsDefined('form.firstname') and len(form.firstname) neq 0>

         <cfset strFirstName = form.firstname/>

    </cfif>

    <cfif IsDefined('form.lastname') and len(form.lastname) neq 0>

         <cfset strLastName = form.lastname/>

    </cfif>

    <cfset Fullname = strFirstName & ' ' & strLastName/>

    Fullname will have your Firstname plus a space and then the lastname

    One more suggestion from my side, if you are doing anything in CFELSE while checking for the form variables inside your query. It can be written like this.

    <cfif IsDefined('form.firstname') and len(form.firstname) neq 0>

         <cfset strFirstName = form.firstname/>

    <cfelse>

         <cfset strFirstName = ''/>

    </cfif>

      <cfquery datasource="QBs">

        INSERT INTO

              Customer  (FirstName)

         VALUES

         (<cfqueryparam value="#strFirstName #" cfsqltype="cf_sql_clob" maxlength="25">)

    </cfquery>

    Why are you having CLOB datatype for Firstname , lastname..... etc..

    S
    szafranmanAuthor
    Known Participant
    January 6, 2011

    meensi thanks for your quick response. Let me clear two things up I am using ColdFusion 9 (not sure if it matters) and also I had it using "cfelse" and clob because I created a form and than manually clicked the server behaviors---->insert record and matched each form field with the DB table entry. So Dreamweaver/Coldfusion built in code set that string of code up for me. But I have since gone back and re-written it to adhere to your advice but am now getting this error:

    Error Executing Database Query.

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][QODBC] Expected lexical element not found:
    The error occurred in C:\inetpub\wwwroot\CFIDE\testsite\registration_page.cfm: line 36
    34 :     
35 :     <cfif IsDefined('form.zipcode') and len(form.zipcode) NEQ 0>
36 :     <cfset strBillAddressPostalCode=form.zipcode/>
37 :     </cfif>
38 : )
    VENDORERRORCODE  11015
    SQLSTATE  42000
    SQL   INSERT INTO Customer (Name, LastName, FullName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode) VALUES ( )
    DATASOURCE  QBs

    this is my new code just the cf fuction (I didn't change the second DB entry line of code yet bc I didn't think it would matter so it will show still using the cfquerparm and cfelse for that section.):

    <cfif IsDefined("FORM.username")>

      <cfquery name="MM_search" datasource="Access">

        SELECT Logininfo.Username FROM Logininfo WHERE Logininfo.Username=<cfqueryparam value="#FORM.username#" cfsqltype="cf_sql_clob" maxlength="50">

      </cfquery>

      <cfif MM_search.RecordCount GTE 1>

        <cflocation url="taken.cfm?requsername=#FORM.username#" addtoken="no">

      </cfif>

    </cfif>

    <cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>

    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "customer">

      <cfquery datasource="QBs">  

        INSERT INTO Customer (Name, LastName, FullName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode)

    VALUES (<cfif IsDefined('form.firstname') and len(form.firstname) NEQ 0>

    <cfset strName=form.firstname/>

        </cfif>

        <cfif IsDefined('form.lastname') and len(form.lastname) NEQ 0>

        <cfset strLastName=form.lastname/>

        </cfif>

        <cfset FullName=strName &''& LastName/>

        <cfif IsDefined('form.streetaddress') and len(form.streetaddress) NEQ 0>

        <cfset strBillAddressAddr2=form.streetaddress/>

        </cfif>

        <cfif IsDefined('form.city') and len(form.city) NEQ 0>

        <cfset strBillAddressCity=form.city/>

        </cfif>

        <cfif IsDefined('form.state') and len(form.state) NEQ 0>

        <cfset strBillAddressState=form.state/>

        </cfif>

        <cfif IsDefined('form.zipcode') and len(form.zipcode) NEQ 0>

        <cfset strBillAddressPostalCode=form.zipcode/>

        </cfif>

    )

      </cfquery>

    <cfquery datasource="Access">

    INSERT INTO Logininfo (FirstName, LastName, Username, Password)

    VALUES (<cfif IsDefined("FORM.firstname") AND #FORM.firstname# NEQ "">

    <cfqueryparam value="#FORM.firstname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.lastname") AND #FORM.lastname# NEQ "">

    <cfqueryparam value="#FORM.lastname#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.username") AND #FORM.username# NEQ "">

    <cfqueryparam value="#FORM.username#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    , <cfif IsDefined("FORM.password") AND #FORM.password# NEQ "">

    <cfqueryparam value="#FORM.password#" cfsqltype="cf_sql_clob" maxlength="25">

    <cfelse>

    ''

    </cfif>

    )

    </cfquery>

      <cflocation url="login.cfm">

    </cfif>

    thanks for your help and I apologize for my rookie mistakes in advance!!!

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices