Skip to main content
S
szafranman
Known Participant
January 11, 2011
Question

Update two DataSources one Query?

  • January 11, 2011
  • 1 reply
  • 1174 views

I am building a dynamic site using Dreamweaver CS5 and Coldfusion 9.

Ok after messing with this particular page for almost five hours now and still have not figured this out I thought I would ask someone (anyone that reads this) who has a better clue than me at this.

I made a registration page which gets data put into two separate databases "QBs" table-Customer gets contact info "Access" table-Logininfo gets some contact info and username + password +access level. Now I wanted to create a contact update page to allow the user to change any of the information he/she typed in on the registration page. I wanted both sources to be updated in one submit button because the two sources reference each other on different pages so they both need to be updated with the same info. For instance both sources-tables have a column FullName and it gets used as a session variable throughout the site so if the customer changes either first or last name it needs to be updated in both sources at one instant for the site to maintain operation.

The error I keep getting is this:

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\Pages\User  Pages\user_contact_info.cfm: line 97
95 : Email=<cfqueryparam value="#Email#" cfsqltype="cf_sql_carchar" maxlength="75"/>
96 : )
97 : WHERE ListID=<cfoutput>'#Session.ListID#'</cfoutput>
98 :   </cfquery>
99 :   <cfquery datasource="Access">
VENDORERRORCODE  11015
SQLSTATE  42000
SQL   UPDATE Customer SET  VALUES ( Salutation= (param 1) , FirstName= (param 2) , LastName= (param 3) ,  Name= (param 4) , BillAdressAddr1= (param 5) , BillAddressAddr2= (param 6) ,  BillAddressCity= (param 7) , BillAddressState= (param 😎 ,  BillAddressPostalCode= (param 9) , Contact= (param 10) , Phone= (param 11) ,  AltPhone= (param 12) , Email= (param 13) ) WHERE ListID='80000025-1246408045'
DATASOURCE  QBs

Now this is my code for the Query and I will also list my table because maybe there is something I am missing from that. I have a feeling what I am trying to do is not possible??? thus the lack of success in fiddling with the code for hours. Any help is very much appreciated!!!!!!

HEAD:

<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">

  <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>

  <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>

</cflock>

<cfif MM_Username EQ "" OR MM_UserAuthorization EQ "" OR ListFind("User,Admin",MM_UserAuthorization) EQ 0>

  <cfset MM_referer=CGI.SCRIPT_NAME>

  <cfif CGI.QUERY_STRING NEQ "">

    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>

  </cfif>

  <cfset MM_failureURL="unauthorized.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>

  <cflocation url="#MM_failureURL#" addtoken="no">

</cfif>

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

SELECT ID, FullName, FirstName, LastName, Username, Password

FROM Logininfo

WHERE ID=<cfoutput>#Session.ID#</cfoutput>

</cfquery>

<cfquery name="update_contactinfo" datasource="QBs">

SELECT ListID, Name, FullName, CompanyName, Salutation, FirstName, LastName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, AltPhone, Email

FROM Customer

WHERE ListID=<cfoutput>'#Session.ListID#'</cfoutput>

</cfquery>

<cfquery name="listvariables" Datasource="Access">

SELECT Salutation FROM ListVariables

</cfquery>

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>

<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "UpdateContactInformation">

<cfquery datasource="QBs">  

   UPDATE Customer SET

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

  <cfset #Salutation#=form.salutation/>

  </cfif>

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

<cfset #FirstName#=form.firstname/>

  </cfif>

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

    <cfset #LastName#=form.lastname/>

    </cfif>

  <cfset Name=#FirstName#&' '&#LastName#/>

  <cfset BillAddressAddr1=#Salutation#&' '&#FirstName#&' '&#LastName#/>

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

    <cfset #BillAddressAddr2#=form.streetaddress/>

  </cfif>

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

    <cfset #BillAddressCity#=form.city/>

    </cfif>

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

    <cfset #BillAddressState#=form.state/>

    </cfif>

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

    <cfset #BillAddressPostalCode#=form.zipcode/>

    </cfif>

<cfset Contact=#FirstName#&' '&#LastName#/>

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

    <cfset #Phone#=form.dayphone/>

  </cfif>

  <cfif IsDefined('form.eveningphone') and len(form.eveningphone) NEQ "">

    <cfset #AltPhone#=form.eveningphone/>

  </cfif>

  <cfif IsDefined('form.email') and len(form.email) NEQ "">

    <cfset #Email#=form.email/>

  </cfif>

VALUES (

Salutation=<cfqueryparam value="#Salutation#" cfsqltype="cf_sql_varchar" maxlength="15"/>,

FirstName=<cfqueryparam value="#FirstName#" cfsqltype="cf_sql_varchar" maxlength="41"/>,

LastName=<cfqueryparam value="#LastName#" cfsqltype="cf_sql_varchar" maxlength="41"/>,

Name=<cfqueryparam value="#Name#" cfsqltype="cf_sql_varchar" maxlength="50"/>,

BillAdressAddr1=<cfqueryparam value="#BillAddressAddr1#" cfsqltype="cf_sql_varchar" maxlength="80"/>,

BillAddressAddr2=<cfqueryparam value="#BillAddressAddr2#" cfsqltype="cf_sql_varchar" maxlength="50"/>,

BillAddressCity=<cfqueryparam value="#BillAddressCity#" cfsqltype="cf_sql_varchar" maxlength="50"/>,

BillAddressState=<cfqueryparam value="#BillAddressState#" cfsqltype="cf_sql_varchar" maxlength="25"/>,

BillAddressPostalCode=<cfqueryparam value="#BillAddressPostalCode#" cfsqltype="cf_sql_varchar" maxlength="13"/>,

Contact=<cfqueryparam value="#Contact#" cfsqltype="cf_sql_varchar" maxlength="41"/>,

Phone=<cfqueryparam value="#Phone#" cfsqltype="cf_sql_varchar" maxlength="15"/>,

AltPhone=<cfqueryparam value="#AltPhone#" cfsqltype="cf_sql_varchar" maxlength="15"/>,

Email=<cfqueryparam value="#Email#" cfsqltype="cf_sql_carchar" maxlength="75"/>

)

WHERE ListID=<cfoutput>'#Session.ListID#'</cfoutput>

  </cfquery>

  <cfquery datasource="Access">

UPDATE Logininfo SET

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

<cfset #FirstName#=form.firstname/>

</cfif>

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

<cfset #LastName#=form.lastname/>

</cfif>

<cfset FullName=#form.firstname#&' '&#form.lastname#/>

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

<cfset #Username#=form.username/>

</cfif>

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

<cfset #Password#=form.password/>

</cfif>

VALUES (

FullName=<cfqueryparam value="#FullName#" cfsqltype="cf_sql_varchar" maxlength="80"/>,

FirstName=<cfqueryparam value="#FirstName#" cfsqltype="cf_sql_varchar" maxlength="41"/>,

LastName=<cfqueryparam value="#LastName#" cfsqltype="cf_sql_varchar" maxlength="41"/>,

UserName<cfqueryparam value="#Username#" cfsqltype="cf_sql_varchar" maxlength="41"/>,

Password=<cfqueryparam value="#Password#" cfsqltype="cf_sql_varchar" maxlength="41"/>

)

WHERE ID=<cfoutput>'#Session.ID#'</cfoutput>

</cfquery>

<cflocation url="updated_contactinfo.cfm">

</cfif>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html><!-- InstanceBegin template="/Templates/customer_template.dwt.cfm" codeOutsideHTMLIsLocked="false" -->

<head>

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

<!-- InstanceBeginEditable name="title" --><title>Update Contact Information</title><!-- InstanceEndEditable -->

<link href="../../styles/Main.css" rel="stylesheet" type="text/css">

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

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

<!-- InstanceBeginEditable name="head" -->

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

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

<link href="../../SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css"><!-- InstanceEndEditable --></head>

BODY:

<body>

<div id="Container">

  <div id="header"><img src="../../Images/Website-Image.gif" width="393" height="217" alt="TS Landscaping Logo and Name of company"></div>

  <div id="menu_bar1">

    <ul id="MenuBar1" class="MenuBarHorizontal">

      <li><a class="MenuBarItemSubmenu" href="../../index.cfm">Home</a>

        <ul>

          <li><a href="#">Item 1.1</a></li>

          <li><a href="#">Item 1.2</a></li>

          <li><a href="#">Item 1.3</a></li>

        </ul>

      </li>

      <li><a href="#">About Us</a></li>

      <li><a href="#">Services</a></li>

      <li><a class="MenuBarItemSubmenu" href="#">User Account</a>

        <ul>

          <li><a href="../../login.cfm">Login</a></li>

          <li><a href="../../registration_page_contact.cfm">Register</a></li>

          <li><a href="../../members_page.cfm">Customer Page</a></li>

        </ul>

      </li>

      <li><a href="#">Contact Us</a></li>

    </ul>

  </div>

  <div id="Main">

    <div id="side_bar"><!-- InstanceBeginEditable name="sidebar" -->Content for  id "side_bar" Goes Here<!-- InstanceEndEditable --></div>

    <div id="main_body">

<p align="left"><a href="../../invoice_page.cfm">Customer Invoices</a> | <a href="../../service_request_page.cfm">Request Services</a> | <a href="user_contact_info.cfm">Change Contact Info</a></p>

      <hr>

<!-- InstanceBeginEditable name="mainbody" -->

<h1><cfoutput>#Session.FullName#</cfoutput> Contact Information</h1>

<table width="auto" border="1">

  <tr>

    <td><div align="center">Update Your Contact Information</div></td>

    </tr>

  <tr>

    <td> 

      <form method="post" name="UpdateContactInformation" action="<cfoutput>#CurrentPage#</cfoutput>">

        <table align="center" >

          <tr valign="baseline">

            <td nowrap align="right"><div align="right"><label for="salutation">Salutation</label></div></td>

            <td><select name="salutation" id="salutation" accesskey="n" tabindex="15">

              <cfoutput query="listvariables">

                <option value="#listvariables.Salutation#" <cfif (isDefined("Session.Salutation") AND listvariables.Salutation EQ Session.Salutation)>selected="selected"</cfif>>#listvariables.Salutation#</option>

              </cfoutput>

            </select>

              <tr valign="baseline">

                <td nowrap align="right"><div align="right">FirstName:</div></td>

                <td><input type="text" name="FirstName" id="firstname" value="<cfoutput>#update_contactinfo.FirstName#</cfoutput>"  size="32"></td>

                </tr>

          <tr valign="baseline">

            <td nowrap align="right"><div align="right">LastName:</div></td>

            <td><input type="text" name="LastName" id="lastname" size="32" value="<cfoutput>#update_contactinfo.LastName#</cfoutput>"></td>

            </tr>

          <tr valign="baseline" >

            <td nowrap align="right"><div align="right">Street Address:</div></td>

            <td><input type="text" name="streetaddress" id="streetaddress"  size="32" value="<cfoutput>#update_contactinfo.BillAddressAddr2#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

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

            <td><input type="text" name="city" id="city"  size="32"value="<cfoutput>#update_contactinfo.BillAddressCity#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

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

            <td><input type="text" name="state" id="state" size="32" value="<cfoutput>#update_contactinfo.BillAddressState#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

            <td nowrap align="right"><div align="right">Zip Code:</div></td>

            <td><input type="text" name="zipcode" id="zipcode" size="32"value="<cfoutput>#update_contactinfo.BillAddressPostalCode#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

            <td nowrap align="right"><div align="right">Day Phone:</div></td>

            <td><input type="text" name="dayphone" id="dayphone" size="32" value="<cfoutput>#update_contactinfo.Phone#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

            <td nowrap align="right"><div align="right">Evening Phone:</div></td>

            <td><input type="text" name="eveningphone" id="eveningphone"  size="32" value="<cfoutput>#update_contactinfo.AltPhone#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

            <td nowrap align="right"><div align="right">Email:</div></td>

            <td><input type="text" name="email" id="email" size="32" value="<cfoutput>#update_contactinfo.Email#</cfoutput>"></td>

            </tr>

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

            <td><input type="text" name="username" id="username" size="32" value="<cfoutput>#update_info.Username#</cfoutput>"></td>

            </tr>

          <tr valign="baseline">

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

            <td><input type="password" name="password" id="password" accesskey="n" tabindex="45" size="32" value="<cfoutput>#update_info.Password#</cfoutput>"></td>

            </tr>

          <td nowrap align="right"><div align="right"><label for="confirmpassword">Confirm Password:</label></div></td>

            <td><span id="spryconfirm1">

              <input type="password" name="confirmpassword" id="confirmpassword" accesskey="n" tabindex="50" size="32">

              <span class="confirmRequiredMsg">A value is required.</span><span class="confirmInvalidMsg">The values don't match.</span></span></td>

            </tr>

          <tr valign="baseline">

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

              <input type="submit" value="Update Contact Info">

              </div></td>

            </tr>

          </table>

        <input type="hidden" name="MM_UpdateRecord" value="UpdateContactInformation">

        </form>

      <p> </p></td>

    </tr>

</table>

<p> </p>

<script type="text/javascript">

var spryconfirm1 = new Spry.Widget.ValidationConfirm("spryconfirm1","password");

</script>

    <!-- InstanceEndEditable -->

    </div>

  </div>

  <div id="footer">

    <p>Home | About Us | Site Map </p>

    <p>&copy; TS Landscaping LLC 2010</p>

  </div>

</div>

<script type="text/javascript">

var MenuBar1 = new Spry.Widget.MenuBar("MenuBar1", {imgDown:"../SpryAssets/SpryMenuBarDownHover.gif", imgRight:"../SpryAssets/SpryMenuBarRightHover.gif"});

    </script>

</body>

<!-- InstanceEnd --></html>

    This topic has been closed for replies.

    1 reply

    M
    meensi
    Inspiring
    January 11, 2011
     Email=<cfqueryparam value="#Email#" cfsqltype="cf_sql_carchar" maxlength="75"/>
    96 : )
    97 : WHERE ListID=<cfoutput>'#Session.ListID#'</cfoutput>
    98 :   </cfquery>
    99 :   <cfquery datasource="Access">

    Hi,

    I hope there is no datatyple like this CF_SQL_CARCHAR, it was supposed to be CF_SQL_VARCHAR;

    Please change it and try.!!

    S
    szafranmanAuthor
    Known Participant
    January 11, 2011

    Meensi thanks for the reply. Shortly after posting I noticed that mistake however the correction did not stop the error I'm afraid. I tried putting the values under cfqueryparam in single quotes so: Value='#Salutation#' no help and combined with the Salutation=<cfqueryparam so "Salutation"=<cfqueryparam value="#Salutation#" both single and double quote attempts failed.

    I changed all the form names and ids to read exactly as column heads so for instance "streetaddress" reads "BillAddressAddr2" I made the change in the cfif isdefined area as well to correlate to the correct form name, no luck.

    Since you haven't hinted that this query might not be possible I am still optimistic I can get this to work. I think I am on seven hours now of trying different vairations but I feel like something I should know that I don't is causing the problem. I reduced the entire form down to only a few variables at one point and to no avail. You have already helped me a great deal if you have any other suggestions or advice that would be greatly appreciated! Thanks.

    S
    szafranmanAuthor
    Known Participant
    January 11, 2011

    Meensi just wanted to update you so you didn't put to much time into my problem I have discovered this line of code to work. Obviously it does not include all that I want to update but tomorrow I will add in each line and test as I go to see where I get the error. So far now I think I am at a point where I can figure this out. I am sure there is some value in the database that can't be updated although I looked at the charts for the databases and they all said UPDATABLE I am thinking maybe a typo but I'll find out tomorrow. Thanks again for you help!

    <cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">
      <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>
      <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>
    </cflock>
    <cfif MM_Username EQ "" OR MM_UserAuthorization EQ "" OR ListFind("User,Admin",MM_UserAuthorization) EQ 0>
      <cfset MM_referer=CGI.SCRIPT_NAME>
      <cfif CGI.QUERY_STRING NEQ "">
        <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
      </cfif>
      <cfset MM_failureURL="unauthorized.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>
      <cflocation url="#MM_failureURL#" addtoken="no">
    </cfif>

    <cfquery name="Update_info" datasource="Access">
    SELECT ID, FullName, FirstName, LastName, Username, Password
    FROM Logininfo
    WHERE ID=<cfoutput>#Session.ID#</cfoutput>
    </cfquery>

    <cfquery name="update_contactinfo" datasource="QBs">
    SELECT ListID, Name, FullName, CompanyName, Salutation, FirstName, LastName, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, AltPhone, Email
    FROM Customer
    WHERE ListID=<cfoutput>'#Session.ListID#'</cfoutput>
    </cfquery>

    <cfquery name="listvariables" Datasource="Access">
    SELECT Salutation FROM ListVariables
    </cfquery>

    <cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
    <cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "UpdateContactInformation">

    <cfset #FistName#=Form.FirstName/>

    <cfquery datasource="QBs">
    UPDATE Customer Set

    FirstName=<cfqueryparam value='#FirstName#' cfsqltype="cf_sql_varchar" maxlength="41"/>

    WHERE ListID=<cfoutput>'#Session.ListID#'</cfoutput>

    </cfquery>

    <cfset #FirstName#=Form.FirstName/>

    <cfquery datasource="Access">
    UPDATE Logininfo Set

    FirstName=<cfqueryparam value='#FirstName#' cfsqltype="cf_sql_varchar" maxlength="41"/>

    WHERE ID=<cfoutput>#Session.ID#</cfoutput>
    </cfquery>


    <cflocation url="updated_contactinfo.cfm">
    </cfif>

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