Auto fill form fields using coldfusion

**acoder** · Aug 21 '08, 06:48 PM

It's actually AND. Also, look at the logic. You want the custnum query to return something so the check should be that it's not 0.

**bonneylake** · Aug 21 '08, 07:28 PM

Hey Acoder,

Ok here is what i got now, is this right?

Code:

<cfif custnum NEQ "" AND custnum.recordcount IS NOT 0> 
      <cfoutput query= "custnum">
      formObj.custnum.value = "#custnum#";
      formObj.compname.value = "#compname#";
      formObj.fname.value = "#fname#";
      formObj.lname.value = "#lname#";
      formObj.address.value = "#address#";
      formObj.city.value = "#city#";
      formObj.state.value = "#state#";
      formObj.zip.value = "#zip#";
      formObj.email.value = "#email#";
      formObj.priphone.value = "#priphone#";
      formObj.secphone.value = "#secphone#";
      formObj.custnotes.value = "#custnotes#";
 </cfoutput>
      <cfelse>
  formObj.custnum.value = "";
      formObj.compname.value = "";
      formObj.fname.value = "";
      formObj.lname.value = "";
      formObj.address.value = "";
      formObj.city.value = "";
      formObj.state.value = "";
      formObj.zip.value = "";
      formObj.email.value = "";
      formObj.priphone.value = "";
      formObj.secphone.value = "";
      formObj.custnotes.value = "";
      </cfif>

Thank you :),
Rach

**acoder** · Aug 21 '08, 08:16 PM

That should be url.custnum, not custnum unless you've set custnum to url.custnum somewhere. Test the page again as you did earlier but this time wth invalid input too and see if it works.

**bonneylake** · Aug 21 '08, 08:38 PM

Acoder,

ok when i typed in 111 (which forgot i didn't have 111 in my database) i got this

Code:

formObj.custnum.value = ""; formObj.compname.value = ""; formObj.fname.value = ""; formObj.lname.value = ""; formObj.address.value = ""; formObj.city.value = ""; formObj.state.value = ""; formObj.zip.value = ""; formObj.email.value = ""; formObj.priphone.value = ""; formObj.secphone.value = ""; formObj.custnotes.value = "";

however, when i typed in a number that is in my database i got this

Code:

Error Diagnostic Information
Expression result cannot be converted to a string

Expressions used inside tags like CFOUTPUT, CFQUERY, CFMAIL, etc. must evaluate to a value that can be converted to a string for output or dynamic text accumulation purposes. Complex objects, such as queries, arrays, and COM/DCOM objects, cannot be represented as strings.

The error occurred while processing an element with a general identifier of (#custnum#), occupying document position (9:32) to (9:40) in the template file

an here is the full getClient.cfm

Code:

<cfparam name="url.custnum" default="">

<cfquery name="custnum" datasource="CustomerSupport">
      select * from dbo.tbl_CS_contacts
      where fk_custNum=#url.custnum#
      </cfquery>
          <cfif url.custnum NEQ "" AND custnum.recordcount IS NOT 0> 
      <cfoutput query= "custnum">
      formObj.custnum.value = "#custnum#";
      formObj.compname.value = "#compname#";
      formObj.fname.value = "#fname#";
      formObj.lname.value = "#lname#";
      formObj.address.value = "#address#";
      formObj.city.value = "#city#";
      formObj.state.value = "#state#";
      formObj.zip.value = "#zip#";
      formObj.email.value = "#email#";
      formObj.priphone.value = "#priphone#";
      formObj.secphone.value = "#secphone#";
      formObj.custnotes.value = "#custnotes#";
       </cfoutput>
      <cfelse>
      formObj.custnum.value = "";
      formObj.compname.value = "";
      formObj.fname.value = "";
      formObj.lname.value = "";
      formObj.address.value = "";
      formObj.city.value = "";
      formObj.state.value = "";
      formObj.zip.value = "";
      formObj.email.value = "";
      formObj.priphone.value = "";
      formObj.secphone.value = "";
      formObj.custnotes.value = "";
      </cfif>

Thank you :),
Rach

**acoder** · Aug 21 '08, 08:49 PM

That'll be caused by the #custnum# column value. Rename the query to another name, e.g. customerdetails , then change lines 7 and 8 to use that name.

**bonneylake** · Aug 21 '08, 09:11 PM

Acoder,

Ok i get the error

Code:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.

SQL = "select * from dbo.tbl_CS_contacts where fk_custNum="

Data Source = "CUSTOMERSUPPORT"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:55) in the template file C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\getClient.cfm

.

an heres the code. do i need to name the url.custnum do i need to change the custnum part to customerd?

Code:

<cfparam name="url.custnum" default="">

<cfquery name="customerd" datasource="CustomerSupport">
      select * from dbo.tbl_CS_contacts
      where fk_custNum=#url.custnum#
      </cfquery>
          <cfif url.custnum NEQ "" AND customerd.recordcount IS NOT 0> 
      <cfoutput query= "customerd">
      formObj.custnum.value = "#custnum#";
      formObj.compname.value = "#compname#";
      formObj.fname.value = "#fname#";
      formObj.lname.value = "#lname#";
      formObj.address.value = "#address#";
      formObj.city.value = "#city#";
      formObj.state.value = "#state#";
      formObj.zip.value = "#zip#";
      formObj.email.value = "#email#";
      formObj.priphone.value = "#priphone#";
      formObj.secphone.value = "#secphone#";
      formObj.custnotes.value = "#custnotes#";
       </cfoutput>
      <cfelse>
      formObj.custnum.value = "";
      formObj.compname.value = "";
      formObj.fname.value = "";
      formObj.lname.value = "";
      formObj.address.value = "";
      formObj.city.value = "";
      formObj.state.value = "";
      formObj.zip.value = "";
      formObj.email.value = "";
      formObj.priphone.value = "";
      formObj.secphone.value = "";
      formObj.custnotes.value = "";
      </cfif>

Thank you,
Rach

**acoder** · Aug 22 '08, 09:09 AM

Check that the query is correct by testing in SQL Server. In post #18, the query was slightly different.

**bonneylake** · Aug 22 '08, 01:22 PM

Acoder,

Ok think i got it now. I had some of the fields named wrong. But here is what i get when i try a number (that has all the fields filled out)

Code:

formObj.custnum.value = "555"; formObj.cust_company.value = "test"; formObj.fname.value = "test"; formObj.lname.value = "test"; formObj.add1.value = "test"; formObj.city.value = "test"; formObj.state.value = "test"; formObj.zip.value = "test"; formObj.email.value = "test"; formObj.pri_phone.value = "test"; formObj.sec_phone.value = "test"; formObj.notes.value = "test";

an here is what all of getClient.cfm looks like

Code:

<cfparam name="url.custnum" default="">

<cfquery name="customerd" datasource="CustomerSupport">
      select * from dbo.tbl_CS_contacts
      where fk_custNum=#url.custnum#
      </cfquery>
          <cfif url.custnum NEQ "" AND customerd.recordcount IS NOT 0> 
      <cfoutput query="customerd">
      formObj.custnum.value = "#custnum#";
      formObj.cust_company.value = "#cust_company#";
      formObj.fname.value = "#fname#";
      formObj.lname.value = "#lname#";
      formObj.add1.value = "#add1#";
      formObj.city.value = "#city#";
      formObj.state.value = "#state#";
      formObj.zip.value = "#zip#";
      formObj.email.value = "#email#";
      formObj.pri_phone.value = "#pri_phone#";
      formObj.sec_phone.value = "#sec_phone#";
      formObj.notes.value = "#notes#";
       </cfoutput>
      <cfelse>
      formObj.custnum.value = "";
      formObj.cust_company.value = "";
      formObj.fname.value = "";
      formObj.lname.value = "";
      formObj.add1.value = "";
      formObj.city.value = "";
      formObj.state.value = "";
      formObj.zip.value = "";
      formObj.email.value = "";
      formObj.pri_phone.value = "";
      formObj.sec_phone.value = "";
      formObj.notes.value = "";
      </cfif>

Whats next?

Thank you :),
Rach

**acoder** · Aug 22 '08, 01:50 PM

Well, with this you should be able to plug it in in place of getClient.php in the scripts that you linked to in the first post (as long as the form fields are correct).

**bonneylake** · Aug 22 '08, 02:25 PM

Hey Acoder,

Well i tried doing it but can't seem to get it right so i got a few questions.

ok in the javascript part there is this line

Code:

ajax.requestFile = 'getClient.cfm?getClientId='+clientId;

an was wondering after the ? what would i add? would i put #url.custnum#=" +clidentid;?

an during the rest of this code do i need to change everything from clientID to custnum or am i suppose to be changing them to another name?

here is what the code looks like

Code:

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value.replace(/[^0-9]/g,'');
		if(clientId.length==4 && clientId!=currentClientID){
			currentClientID = clientId
			ajax.requestFile = 'getClient.cfm?getClientId='+clientId;	// Specifying which file to get
			ajax.onCompletion = showClientData;	// Specify function that will be executed after file has been found
			ajax.runAJAX();		// Execute AJAX function			
		}
	}
	
	function showClientData()
	{
		var formObj = document.forms['clientForm'];	
		eval(ajax.response);
	}
	
	function initFormEvents()
	{
		document.getElementById('clientID').onblur = getClientData;
		document.getElementById('clientID').focus();
	}
	
	window.onload = initFormEvents;

Thank you for all the help you have given me i really do appreciate it :),
Rach

**acoder** · Aug 22 '08, 03:12 PM

You just have to change "getClientI d" to "custnum".

**bonneylake** · Aug 22 '08, 03:29 PM

Hey Acoder,

I tried what you said an i got nothing. Could it be because of the form i have?
here is what the javascript looks like now

Code:

	<script type="text/javascript">

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value.replace(/[^0-9]/g,'');
		if(clientId.length==4 && clientId!=currentClientID){
			currentClientID = clientId
			ajax.requestFile = 'getClient.cfm?custnum='+clientId;	// Specifying which file to get
			ajax.onCompletion = showClientData;	// Specify function that will be executed after file has been found
			ajax.runAJAX();		// Execute AJAX function			
		}
		
	}
	
	function showClientData()
	{
		var formObj = document.forms['page1'];	
		eval(ajax.response);
	}
	
	
	function initFormEvents()
	{
		document.getElementById('clientID').onblur = getClientData;
		document.getElementById('clientID').focus();
	}
	
	
	window.onload = initFormEvents;
	</script>

an heres what my form looks like

Code:

<form name="page1" action="saveticket1.cfm" method="POST">
<input type="text" name="custnum" id="clientID" value="" />
<input type="text" name="compname" id="cust_company" value="" size="20"/>
<input type="text" name="fname" id="fname" value="" size="20"/>
<input type="text" name="lname" id="lname" value="" size="20"/>
<input type="text" name="address" value="" id="add1" size="20"/>
<input type="text" name="city" id="city" value="" size="20"/>
<input type="text" name="state" id="state" value="" size="20"/>
<input type="text" name="zip" id="zip" value="" size="20"/>
<input type="text" name="email" id="email" value="" size="20"/>
<input type="text" name="priphone" id="pri_phone"value="" size="20"/>
<input type="text" name="secphone" id="sec_phone" value="" size="20"/>
<textarea  maxlength='500' onkeyup='return ismaxlength(this)' onkeydown='return ismaxlength(this)' rows="4" cols="60" name="custnotes" id="notes" value=""></textarea>
 <input type="submit" value="Submit" class="officalsubmit"/>
 </form>

Any suggestions? Thank you :)
Rach

**acoder** · Aug 22 '08, 05:20 PM

What's the length of the customer number that you're testing with? On line 8:
[code=javascript]if(clientId.len gth==4 && clientId!=curre ntClientID){[/code]if the clientId is less than 1000, it won't do anything. If you want any number then remove that condition.

**bonneylake** · Aug 22 '08, 05:43 PM

Acoder,

The number i been trying is 444 but none of the fields are being filled in or nothing (an no errors). Here is what the code looks like now with the length taken out

Code:

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value.replace(/[^0-9]/g,'');
			currentClientID = clientId
			ajax.requestFile = 'getClient.cfm?custnum='+clientId;	// Specifying which file to get
			ajax.onCompletion = showClientData;	// Specify function that will be executed after file has been found
			ajax.runAJAX();		// Execute AJAX function			
		
	}
	
	function showClientData()
	{
		var formObj = document.forms['page1'];	
		eval(ajax.response);
	}
	
	
	function initFormEvents()
	{
		document.getElementById('clientID').onblur = getClientData;
		document.getElementById('clientID').focus();
	}
	
	
	window.onload = initFormEvents;

Thank you,
Rach

**acoder** · Aug 22 '08, 05:57 PM

Is 444 a valid customer number? Does it return any data if you try it out as earlier? What about 555 which returned some test data?

Auto fill form fields using coldfusion

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment