Auto fill form fields using coldfusion

[bonneylake]

Hey Acoder,

I got it to work! But its really strange. Ok when i was testing earlier i typed in the word test into the custnum (wont let me submit without a value in there).but anyway when i went to test it the way we were testing i typed in 444 and i got Conversion failed when converting the nvarchar value 'test' to data type int. The weird thing is there 2 separate records one is 444 (with its fields) and the other is test(with its fields). An now i am wondering, if the customer number is say a4t6 is it going to have a problem with this?

An also wanted to ask. I really need to make it a drop down box for the value. Like example they start typing in the input and the drop down field populates (based on the input). this part i can figure out (have already done it). but how would i make it populate the rest of the fields by clicking on the correct number in the drop down rather then after i type in the number into the field and pressenter? from the way the code works (with what we got) couldn't i put on my dropdown box like this

onclick="document.getElementById('clientID').onblur = getClientData";

or would i need to do it this way

onclick="document.getElementById('clientID').focus()";

this part appears in the function initFormEvents( ) ok thinking about it i could even do it this way

onclick="javascript:initFormEvents()";

here is an example of what i am trying to do (without the populating rest of fields part)

http://www.mattkruse.c om/javascript/autocomplete/index.html


Thank you,
Rach

[acoder]

For the first question, can you give more details. It seems like a database problem.

For the second question, you want to call the function onchange:
[code=html]<select id="options" name="options">[/code]
[code=javascript]document.getEle mentById("optio ns").onchange = getClientData;[/code]

[bonneylake]

Acoder,

alrighty all try to explain this best i can, i am even baffled by this myself.
Basically i had a record in the database called test. for example

pk_contact id    fk_custnum....(other fields)
5                       test

well when i tried to test it the way we were testing it with custnum=444. When i tested it,it came up with the error
Conversion failed when converting the nvarchar value 'test' to data type int.
When i lookedat the database the only thing with the word test in it was that record.

When i took out the record with the name test in fk_custnum and tried to bring up 444 again it worked fine everything was filled in. However, with test in there it wouldnot do this.I event tried putting a different word in there called cat. As long as fk_custnum is all numbers it will autopopulate the rest of the fields. However, if you put a word or a letter in fk_custnum it will not work.

An i just noticed something else when i tried to type in a letter with a number in the customer number field. when i clicked
out of it to fill in another field it takes the letter away. like for example if i put a32 and click out of it, it makes it 32.

i think the database is correct because it says nvarchar(50) for fk_custNum. I think it might be with the ajax (based on it taking a letter out of my example a32). theres another file that goes with this called ajax.js. do you think it might be causeing it?

because this is all the ajax i got right now

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

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;
	</script>

do you want me to copy the script from ajax.js?

Thank you,
Rach

[acoder]

An i just noticed something else when i tried to type in a letter with a number in the customer number field. when i clicked
out of it to fill in another field it takes the letter away. like for example if i put a32 and click out of it, it makes it 32.

This is caused by this line:
[code=javascript]var clientId = document.getEle mentById('clien tID').value.rep lace( /[^0-9]/g,'');[/code]which you can change to:
[code=javascript]var clientId = document.getEle mentById('clien tID').value;[/code]

[bonneylake]

Hey Acoder,

Ok i got another problem with this (an think its because of what i added). But i went an added the drop down box. An now it wont let me type in a value into the input unless the value exists in the table (drop down).

heres what i got-heres the javascript changed

<script type="text/javascript">

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value;
			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();--->
		document.getElementById("options").onchange = getClientData;
	}
	
	
	window.onload = initFormEvents;
	</script>

an here is what my form looks like

Customer Number*:<input type="text" name="custnum" id="clientID" value="" ONKEYUP="autoCo mplete(this,thi s.form.options, 'value',true)" onChange="valid ate(this.form.c ustnum,'Custome r Number')"/>

<SELECT NAME="options" id="options"
onChange="this. form.custnum.va lue=this.option s[this.selectedIn dex].value;javascri pt:initFormEven ts();">
<cfoutput query="getcustn um">
<option value="#fk_cust Num#">#fk_custN um#</option>
</cfoutput>
</SELECT>

the drop down user a file called autocomplete.js

function autoComplete (field, select, property, forcematch) {
	var found = false;
	for (var i = 0; i < select.options.length; i++) {
	if (select.options[i][property].toUpperCase().indexOf(field.value.toUpperCase()) == 0) {
		found=true; break;
		}
	}
	if (found) { select.selectedIndex = i; }
	else { select.selectedIndex = -1; }
	if (field.createTextRange) {
		if (forcematch && !found) {
			field.value=field.value.substring(0,field.value.length-1); 
			return;
			}
		var cursorKeys ="8;46;37;38;39;40;33;34;35;36;45;";
		if (cursorKeys.indexOf(event.keyCode+";") == -1) {
			var r1 = field.createTextRange();
			var oldValue = r1.text;
			var newValue = found ? select.options[i][property] : oldValue;
			if (newValue != field.value) {
				field.value = newValue;
				var rNew = field.createTextRange();
				rNew.moveStart('character', oldValue.length) ;
				rNew.select();
				}
			}
		}
	}

Thank you :),
Rach

[acoder]

When i tested it,it came up with the error
Conversion failed when converting the nvarchar value 'test' to data type int.
When i lookedat the database the only thing with the word test in it was that record.

When i took out the record with the name test in fk_custnum and tried to bring up 444 again it worked fine everything was filled in. However, with test in there it wouldnot do this.I event tried putting a different word in there called cat. As long as fk_custnum is all numbers it will autopopulate the rest of the fields. However, if you put a word or a letter in fk_custnum it will not work.

You need to pass a string to the query. At the moment you're passing an integer. Wrap the variable in quotes:
[code=cfm]where fk_custNum='#ur l.custnum#'[/code]Actually, now that this has been brought up, I should mention that to help prevent SQL injection attacks, you should be using cfqueryparam for any user inputted values passed into a query.

In addition to that, if you're using a stored query, use cfstoredproc/cfstoredprocpar am instead of cfquery/cfqueryparam. Look those up - it'll be a better and safer way of coding.

[bonneylake]

Hey Acoder,

well first off sorry i was not able to respond to your reply till today. Yesterday everyone was sent home from work before we even got to are desk so i am really sorry i have not responded to this till now.

but i am still having trouble with it. Right now i am unable to type anything into the input box except if its a number that already exists in the drop down box,anything else it just makes it disappear right after you type it.but here is all the code that i got. i don't think this problem has to do with the getclient.cfm think its with the ajax..

here is the javascript that is on the same page as my form.

<SCRIPT LANGUAGE="JavaScript" SRC="autocomplete.js"></SCRIPT>
	<script type="text/javascript" src="ajax.js"></script>
<script type="text/javascript">

var ajax = new sack();
	var currentClientID=false;
	function getClientData()
	{
		var clientId = document.getElementById('clientID').value;
			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();--->
		document.getElementById("options").onchange = getClientData;
	}
	
	
	window.onload = initFormEvents;
	</script>

here is the code on autocomplete.js

function autoComplete (field, select, property, forcematch) {
	var found = false;
	for (var i = 0; i < select.options.length; i++) {
	if (select.options[i][property].toUpperCase().indexOf(field.value.toUpperCase()) == 0) {
		found=true; break;
		}
	}
	if (found) { select.selectedIndex = i; }
	else { select.selectedIndex = -1; }
	if (field.createTextRange) {
		if (forcematch && !found) {
			field.value=field.value.substring(0,field.value.length-1); 
			return;
			}
		var cursorKeys ="8;46;37;38;39;40;33;34;35;36;45;";
		if (cursorKeys.indexOf(event.keyCode+";") == -1) {
			var r1 = field.createTextRange();
			var oldValue = r1.text;
			var newValue = found ? select.options[i][property] : oldValue;
			if (newValue != field.value) {
				field.value = newValue;
				var rNew = field.createTextRange();
				rNew.moveStart('character', oldValue.length) ;
				rNew.select();
				}
			}
		}
	}

here is the code on ajax.js

function sack(file) {
	this.xmlhttp = null;

	this.resetData = function() {
		this.method = "POST";
  		this.queryStringSeparator = "?";
		this.argumentSeparator = "&";
		this.URLString = "";
		this.encodeURIString = true;
  		this.execute = false;
  		this.element = null;
		this.elementObj = null;
		this.requestFile = file;
		this.vars = new Object();
		this.responseStatus = new Array(2);
  	};

	this.resetFunctions = function() {
  		this.onLoading = function() { };
  		this.onLoaded = function() { };
  		this.onInteractive = function() { };
  		this.onCompletion = function() { };
  		this.onError = function() { };
		this.onFail = function() { };
	};

	this.reset = function() {
		this.resetFunctions();
		this.resetData();
	};

	this.createAJAX = function() {
		try {
			this.xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
		} catch (e1) {
			try {
				this.xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
			} catch (e2) {
				this.xmlhttp = null;
			}
		}

		if (! this.xmlhttp) {
			if (typeof XMLHttpRequest != "undefined") {
				this.xmlhttp = new XMLHttpRequest();
			} else {
				this.failed = true;
			}
		}
	};

	this.setVar = function(name, value){
		this.vars[name] = Array(value, false);
	};

	this.encVar = function(name, value, returnvars) {
		if (true == returnvars) {
			return Array(encodeURIComponent(name), encodeURIComponent(value));
		} else {
			this.vars[encodeURIComponent(name)] = Array(encodeURIComponent(value), true);
		}
	}

	this.processURLString = function(string, encode) {
		encoded = encodeURIComponent(this.argumentSeparator);
		regexp = new RegExp(this.argumentSeparator + "|" + encoded);
		varArray = string.split(regexp);
		for (i = 0; i < varArray.length; i++){
			urlVars = varArray[i].split("=");
			if (true == encode){
				this.encVar(urlVars[0], urlVars[1]);
			} else {
				this.setVar(urlVars[0], urlVars[1]);
			}
		}
	}

	this.createURLString = function(urlstring) {
		if (this.encodeURIString && this.URLString.length) {
			this.processURLString(this.URLString, true);
		}

		if (urlstring) {
			if (this.URLString.length) {
				this.URLString += this.argumentSeparator + urlstring;
			} else {
				this.URLString = urlstring;
			}
		}

		// prevents caching of URLString
		this.setVar("rndval", new Date().getTime());

		urlstringtemp = new Array();
		for (key in this.vars) {
			if (false == this.vars[key][1] && true == this.encodeURIString) {
				encoded = this.encVar(key, this.vars[key][0], true);
				delete this.vars[key];
				this.vars[encoded[0]] = Array(encoded[1], true);
				key = encoded[0];
			}

			urlstringtemp[urlstringtemp.length] = key + "=" + this.vars[key][0];
		}
		if (urlstring){
			this.URLString += this.argumentSeparator + urlstringtemp.join(this.argumentSeparator);
		} else {
			this.URLString += urlstringtemp.join(this.argumentSeparator);
		}
	}

	this.runResponse = function() {
		eval(this.response);
	}

	this.runAJAX = function(urlstring) {
		if (this.failed) {
			this.onFail();
		} else {
			this.createURLString(urlstring);
			if (this.element) {
				this.elementObj = document.getElementById(this.element);
			}
			if (this.xmlhttp) {
				var self = this;
				if (this.method == "GET") {
					totalurlstring = this.requestFile + this.queryStringSeparator + this.URLString;
					this.xmlhttp.open(this.method, totalurlstring, true);
				} else {
					this.xmlhttp.open(this.method, this.requestFile, true);
					try {
						this.xmlhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded")
					} catch (e) { }
				}

				this.xmlhttp.onreadystatechange = function() {
					switch (self.xmlhttp.readyState) {
						case 1:
							self.onLoading();
							break;
						case 2:
							self.onLoaded();
							break;

						case 3:
							self.onInteractive();
							break;
						case 4:
							self.response = self.xmlhttp.responseText;
							self.responseXML = self.xmlhttp.responseXML;
							self.responseStatus[0] = self.xmlhttp.status;
							self.responseStatus[1] = self.xmlhttp.statusText;

							if (self.execute) {
								self.runResponse();
							}

							if (self.elementObj) {
								elemNodeName = self.elementObj.nodeName;
								elemNodeName.toLowerCase();
								if (elemNodeName == "input"
								|| elemNodeName == "select"
								|| elemNodeName == "option"
								|| elemNodeName == "textarea") {
									self.elementObj.value = self.response;
								} else {
									self.elementObj.innerHTML = self.response;
								}
							}
							if (self.responseStatus[0] == "200") {
								self.onCompletion();
							} else {
								self.onError();
							}

							self.URLString = "";
							break;
					}
				};

				this.xmlhttp.send(this.URLString);
			}
		}
	};

	this.reset();
	this.createAJAX();
}

an here is what the input field for customer number and the drop down box next to it looks like

Customer Number*:<input type="text" name="custnum" id="clientID" value=""  ONKEYUP="autoComplete(this,this.form.options,'value',true)" onChange="validate(this.form.custnum,'Customer Number')"/>
<SELECT NAME="options" id="options"
onChange="this.form.custnum.value=this.options[this.selectedIndex].value;javascript:initFormEvents();">
<cfoutput query="getcustnum">
<option value="#fk_custNum#">#fk_custNum#</option>
</cfoutput>
</SELECT>

but thank you for all the help you have given me an again so sorry i didn't get to respond to this sooner.


Thank you,
Rach

[acoder]

In your autoComplete function call, you're setting the fourth parameter (forceMatch) to true. Change it to false.

[bonneylake]

Hey Acoder

well when i tried to do it this way

function autoComplete (field, select, property, forcematch) {
	var found = false;
	for (var i = 0; i < select.options.length; i++) {
	if (select.options[i][property].toUpperCase().indexOf(field.value.toUpperCase()) == 0) {
		found=false; break;
		}
	}

it worked the exact same way. but when i did it this way

function autoComplete (field, select, property, forcematch) {
	var found = true;
	for (var i = 0; i < select.options.length; i++) {
	if (select.options[i][property].toUpperCase().indexOf(field.value.toUpperCase()) == 0) {
		found=true; break;
		}
	}

it worked but then when i was done typing i got the error options[...] is null or not an object. Did they both need to be false false or both need to be true true or am i missing something?

Thank you,
Rach

[acoder]

No, not the actual function. That works fine. I meant here:
[code=html]Customer Number*:<input type="text" name="custnum" id="clientID" value="" ONKEYUP="autoCo mplete(this,thi s.form.options, 'value',true)" onChange="valid ate(this.form.c ustnum,'Custome r Number')"/>[/code]which needs to change to [code=html]Customer Number*:<input type="text" name="custnum" id="clientID" value="" ONKEYUP="autoCo mplete(this,thi s.form.options, 'value',false)" onChange="valid ate(this.form.c ustnum,'Custome r Number')"/>[/code]

[bonneylake]

Hey Acoder,

that works till i try to click a new field an then i get the error object expected on 170 but only thing on 170 is </td> which i know can't be right. here is it updated

Customer Number*:</td><td><input type="text" name="custnum" id="clientID" value=""  ONKEYUP="autoComplete(this,this.form.options,'value',false)" onChange="validate(this.form.custnum,'Customer Number')"/>

Thank you,
Rach

[acoder]

That must be caused by the validate() function. I think we're going back to the realms of JavaScript. If it can't be solved within a post or two, I suggest you post a new thread in the JavaScript forum. It may be that I end up helping you there anyway, but I like to keep things relevant to each forum :)

[bonneylake]

Hey Acoder,

well i can understand trying to keep everything organized :). an i know this is not a question that will be answered in one or 2 post cause still got another question after i figure out this part. But all post my question in the Javascript section. But still wanted to thank you for all the help you have given me, you truly been a life saver :)

Thank you again :),
Rach

[acoder]

No problem. I think there's still one or two loose ends to tie up on the server-side, so I don't think this thread is over yet ;)

[bonneylake]

No problem. I think there's still one or two loose ends to tie up on the server-side, so I don't think this thread is over yet ;)

Hey Acoder,

yep still a few things left to figure out. Well i understand changing the name from totalAttachment s to uploads but i don't understand the comma-delimited string. Here is what i got

<cfif structKeyExists(FORM, "uploads")>
     <cfset currentDirectory = GetDirectoryFromPath(GetTemplatePath()) & "uploaded">
     <cfparam name="FORM.uploads" default="0">
     <cfloop from="1" to="#form.uploads#" index="counter">
      <cfset currentDescription = form["description" & counter]>
      <!---verify the form field exists --->
     <cfif structKeyExists(FORM, "attachment"& counter)>
          <!--- try and upload it ...--->
          <cffile action="upload" fileField="form.attachment#counter#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\" nameconflict="MAKEUNIQUE">
          <cfset filename = cffile.ClientFileName & "_" & form.id & "_" & counter & "." & cffile.ClientFileExt>
          <CFFILE ACTION="RENAME" SOURCE="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#CFFILE.ServerFile#" destination="C:\Inetpub\Development\WWWRoot\RachelB\footprints\form\attachments\#filename#">
              <cfquery name="attachment" datasource="CustomerSupport">
    exec usp_CS_Insertattachments
'#Form.ID#','#evaluate(serialnum)#','#currentDescription#','#filename#','#Form.fk_addedBy#'
</cfquery>
     </cfif>
     </cfloop>
</cfif>

Thank you,
Rach