asked on

Hidden Field value needs to be query result data- unsure of proper syntax.

Hello,
I am having problems with my hidden input fields, I need for the AssociateCell and Email address to be passed to the next form and posted to a table but am unsure of how to format the hidden fields. Here is what I have:

<cfquery name="getAgents" datasource="teamportals"
Select LastName + ' ' + FirstName + ' ' + 'office ' + ' ' + Company AS Agent,AssociateCell,Email
      From tblUser
Where Inactive = '0' AND Role ='agent'
Order By LastName

</cfquery>
Select Agent:  
     <cfselect name="agentname" query="getAgents" value="Agent" display="Agent" required="yes" multiple="no" size="1" >
</cfselect>
<br /><br />
<input type="hidden" name="AssociateCell" value="#getAgents.AssociateCell#">
<input type="hidden" name="Email" value="#getAgents.Email#">

<input type="submit" name="add_btn" value = "submit">   
<input type="Reset" name="cancel_btn" value = " Clear ">
</td>
</tr>

</tr>
</cfform>

This is the erro that I receive:
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]The name 'AssociateCell#' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

SQL = "Insert Into savings_card (FName1,LName1,FName2,LName2,Address,City,State,Zip,Email,Phone,TransType,Agent,AssociateCell,Email, DateEntered) Values ('Mary','Jones','Other', 'User','25800 Northwestern','Somewhere','USA', '40000','xxxxx@rxxxxx.com','222-222-222,'purchase', 'Allison Daniel office 35',#getAgents.AssociateCell#,#getAgents.Email#, {d '2011-10-24'})"

Data Source = "HSO"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (56:3) to (56:43) in the template file D:\WebSites\HomeServices\registration_action_new.cfm.

Paul MacDonald

You need to escape the query when using the variable values:

SQL = "Insert Into savings_card (FName1,LName1,FName2,LName2,Address,City,State,Zip,Email,Phone,TransType,Agent,AssociateCell,Email, DateEntered) Values ('Mary','Jones','Other', 'User','25800 Northwestern','Somewhere','USA', '40000','xxxxx@rxxxxx.com','222-222-222,'purchase', 'Allison Daniel office 35'," & #getAgents.AssociateCell# & ", " & #getAgents.Email# & ", {d '2011-10-24'})"

Modify the syntax for your scripting language.

jdines

ASKER

Hello,
It is still using the actual text #getAgents.Email# and #getAgents.AssociateCell# rather than evaluating the query results and posting the actual email address and associal cell numbers to the table. I think the problem is somewhere within this block of code:
<cfquery name="getAgents" datasource="teamportals">

Select LastName + ' ' + FirstName + ' ' + 'office ' + ' ' + Company AS Agent,AssociateCell,Email
From tblUser
Where Inactive = '0' AND Role ='agent'
Order By LastName

</cfquery>
Select Agent:  
<cfselect name="agentname" query="getAgents" value="Agent" display="Agent" required="yes" multiple="no" size="1" >
</cfselect>
<br /><br />
<input type="hidden" name="AssociateCell" value="#getAgents.AssociateCell#">
<input type="hidden" name="Email" value="#getAgents.Email#">

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdines

ASKER

Thank you very much