Type Zip Code - auto fill city & state 1

schase · May 26, 2004

Hi folks,

My javascript abilities are next to none. I've searched for this but have not been able to figure it out.

What I am looking to do is.

Type in a zip code in a text box. hit tab or click out of that text box - and if it matches in my database - than the city and State are Automatically filled, if not then it stays blank.

I use ASP and mySQL. I can easily pull the records out of the database - but am entirely unsure as to how to match and autofill.

The one other thing I would need to do is somehow flag if it is not previously matched. Like put $$ infront of the city if I type it in.

From that point I will write up the code to insert into the zip code table for future matches.

Any ideas on how to accomplish this?

thank you.

"Never underestimate the power of determination"

Stuart
Net+

simonchristieis · May 26, 2004

Do you have freedom of range on browser for this project, if not, then the only way to do this would be to download all the infomation into javascript arrays, or send another http request to the server (ie asp page).

If you can choose the browser, then try looking at saving the zipcode data into an xml file and loading the file into the page and populating the text field from there.

Of course this wont work if javascript is turned off.

hth

Simon

schase · May 26, 2004

i can dictate the browser.

but man, I havnt touched xml yet.

how would the arrays work?

"Never underestimate the power of determination"

Stuart
Net+

tviman · May 26, 2004

I don't think you want to try to load the data into an array, or an XML file, and load it withthe page. The US zip code/city directory is over 1000 pages and probably has more than 250 entries per page. Just the 5 digit code alone is over a megabyte of data. Now add in multiple cities for each zip code and you could easily exceed 6 or 7 megabytes of data. You'd never get this to load in any reasonable time limit and I doubt that most machines would have the ability to handle it.

The only reasonable way to do this is to make a call to the server and search the database.

There's always a better way. The fun is trying to find it!

schase · May 26, 2004

The array or whatever will be ok.

I am not loading the US Zip code - only the Zip Codes we enter here. Maybe 150 total, less than 200 at anyrate.

"Never underestimate the power of determination"

Stuart
Net+

kaht · May 26, 2004

Simon is correct, you can't dynamically pull information like that from the database, so you have to pull it all at runtime and save it into an array to use it dynamically. From there code similar to this will populate the other fields:

Code:

<script language=JavaScript>

var zips = new Array();
var cities = new Array();
var states = new Array();

zips[0] = 12345;
cities[0] = 'Chicago';
states[0] = 'Illinois';
zips[1] = 67890;
cities[1] = 'St. Louis';
states[1] = 'Missouri';
zips[2] = 11111;
cities[2] = 'New York';
states[2] = 'New York';
zips[3] = 99999;
cities[3] = 'Los Angeles';
states[3] = 'California';


function populateFields(zip) {
   if (zip > 9999) {   //check to ensure zip is 5 digits
      for (i = 0; i < zips.length; i++) {
         if (zip == zips[i]) {
            blahForm.city.value = cities[i];
            blahForm.state.value = states[i];
         }
      }
   }
   else {
      blahForm.city.value = '';
      blahForm.state.value = '';
   }
}

</script>
<body>
<form name=blahForm>
<input type=text name=zipcode onkeyup='populateFields(parseInt(this.value, 10));' maxlength=5><b>Zip Code</b><br>
<input type=text name=city onkeyup=''><b>City</b><br>
<input type=text name=state onkeyup=''><b>State</b><br>
</form>
</body>

And to fill the arrays of course you'd do some server side coding similar to this (depending on what SSL you're using):

Code:

<%
var count = 0;
oRS.Source = "select zipcode, city, state from database";
oRS.Open();
while (!oRS.EOF) {
   Response.Write("zips[" + count + "] = " + oRS.Fields("zipcode").value + "\n");
   Response.Write("cities[" + count + "] = " + oRS.Fields("city").value + "\n");
   Response.Write("states[" + count + "] = " + oRS.Fields("state").value + "\n");
   count++;
}
oRS.Close();
%>

-kaht

schase · May 26, 2004

Thanks Kaht,

Most of it seems to be working. I get the zip into an array just fine. Here is my code

Code:

<!-- begin zip code array -->
<script language=JavaScript>

var zips = new Array();
var cities = new Array();
var states = new Array();

<% 'first get the zip codes, put into an array for later use
mySQL="Select * from tblzip order by fldCity ASC, fldState ASC, fldZip ASC;"		  
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)

If  rstemp.eof then
   strNoRecs="There are no entries to display"
   Call CloseAll
   response.end
end if
' Now lets grab all the records
alldata=rstemp.getrows
Call CloseAll

numrows=ubound(alldata,2)
fld_theid=0
fld_fldZip=1
fld_fldCity=2
fld_fldState=3
strCount=0
FOR rowcounter = 0 TO numrows

   theid=alldata(fld_theid,rowcounter)
   fldZip=alldata(fld_fldZip,rowcounter)
   fldCity=alldata(fld_fldCity,rowcounter)
   fldState=alldata(fld_fldState,rowcounter)
   
   theid=cleanfield(theid)
   fldZip=cleanfield(fldZip)
   fldCity=cleanfield(fldCity)
   fldState=cleanfield(fldState)
strCount=strCount + 1   
response.write("zips[" & strCount & "] = " & fldZip) & vbcrlf
response.write("cities[" & strCount & "] = " & fldCity) & vbcrlf
response.write("states[" & strCount & "] = " & fldState) & vbcrlf

NEXT
'end zip code array
%>

function populateFields(txtZip) {
   if (txtZip > 9999) {   //check to ensure zip is 5 digits
      for (i = 0; i < zips.length; i++) {
         if (txtZip == zips[i]) {
            frmLead.txtCity.value = cities[i];
            frmLead.txtState.value = states[i];
         }
      }
   }
   else {
      frmLead.txtCity.value = '';
      frmLead.txtState.value = '';
   }
}

</script>
<!-- end zip code array -->

And for the text boxes

Code:

                  <td> 
                    <input type=text name=txtZip onkeyup='populateFields(parseInt(this.value, 10));' maxlength=5>
                  </td>
                  <td> 
                    <input type=text name=txtCity onkeyup=''>
                  </td>
                  <td> 
                    <input type=text name=txtState onkeyup=''>
                  </td>

Now the Array will produce like this

Code:

<!-- begin zip code array -->
<script language=JavaScript>

var zips = new Array();
var cities = new Array();
var states = new Array();

zips[1] = 79914
cities[1] = El Paso
states[1] = TX
zips[2] = 79924
cities[2] = El Paso
states[2] = TX
zips[3] = 79936
cities[3] = El Paso
states[3] = TX


function populateFields(txtZip) {
   if (txtZip > 9999) {   //check to ensure zip is 5 digits
      for (i = 0; i < zips.length; i++) {
         if (txtZip == zips[i]) {
            frmLead.txtCity.value = cities[i];
            frmLead.txtState.value = states[i];
         }
      }
   }
   else {
      frmLead.txtCity.value = '';
      frmLead.txtState.value = '';
   }
}

</script>
<!-- end zip code array -->

But it then says Object expected on this line

Code:

<input type=text name=txtZip onkeyup='populateFields(parseInt(this.value, 10));' maxlength=5>

Unless i'm overlooking something - everything matches ok - the forms name is indeed "frmLead"

Any ideas where i'm missing?

"Never underestimate the power of determination"

Stuart
Net+

kaht · May 26, 2004

There's a few things I see that you might wanna change.

1. Your arrays are being numbered 1..n. The foor loop cycles thru 0..n so you'll need to change these lines:

Code:

response.write("zips[" & strCount & "] = " & fldZip) & vbcrlf
response.write("cities[" & strCount & "] = " & fldCity) & vbcrlf
response.write("states[" & strCount & "] = " & fldState) & vbcrlf
[red]strCount=strCount + 1[/red]

so that strCount is incremented after the values are written to the browser.

2. When the cities and states are written to the browser, they need to be encapsulated in quotes.
cities[1] = [red]"[/red]El Paso[red]"[/red]
states[1] = [red]"[/red]TX[red]"[/red]
In JScript I would put \" in my Response.Write, not sure how it's done in VBScript, you might just be able to pass a single quote instead:

Code:

response.write("cities[" & strCount & "] = [red]'[/red]" & fldCity & [red]"'"[/red]) & vbcrlf
response.write("states[" & strCount & "] = [red]'[/red]" & fldState & [red]"'"[/red]) & vbcrlf

Try out those changes and let us know how it worked.

-kaht

schase · May 26, 2004

absolutely perfect,

My bad for missing the 0 count, and the "'".

thank you very much!

"Never underestimate the power of determination"

Stuart
Net+

simonchristieis · May 27, 2004

just missed most of the post - sorry.

tviman, loading the data into an array would be very resourse consuming, but I have used xml with very large files and found the loading times extremely quick.

vongrunt · May 27, 2004

My $.02... XML, arrays, whatever - for larger data sets both methods are inefficient. It is better to use some kind of remote code invocation - through remote scripting, download behavior (IE) or dynamic SCRIPT nodes.

schase · May 27, 2004

well the array works great, but of course i'm open to faster ideas.

I don't know XML...yet

And not sure what type of remote scripting you are talking about.

"Never underestimate the power of determination"

Stuart
Net+

simonchristieis · May 27, 2004

You said that you could choose the browser, if you can avoid macs as well then look at activex to load the page - then filter the xml file using xpath and mark up the page using dom.(it's easier than it sounds)

Do you have the postcodes in a database, or can you open a recordset with them in asp/ado?

schase · May 27, 2004

I can choose the browser - and no mac's allowed.

activex to load the page - then filter the xml file using xpath and mark up the page using dom.(it's easier than it sounds)

I got the deer in the headlights look going on here.

What I am doing with the postcodes is making my own databae of the ones that we use - as time passes it will grow, but only as we gain customers from other regions out of town.

"Never underestimate the power of determination"

Stuart
Net+

simonchristieis · May 27, 2004

loading an xml doc goes like this:

(blatently stolen from w3schools.com)

Code:

<script type="text/javascript">

var nH = new ActiveXObject("Microsoft.XMLDOM")
nH.async="false"
nH.load("postcode.xml")

now you need to select the right xml node - ie the right postcode, if you created the file using ado's recordset.save and you postcode attribute was called 'postcode' then:

Code:

strSearch = "contains(@postcode, 'EC4 4EC')"

var nH=tXML.selectNodes("/xml/rs:data/z:row["+ strSearch +"]");

then do sommat with it:

Code:

for(var i=0;i<nH.length;i++){
alert(nH.item(i).attributes.getNamedItem("postcode").value
}

</script>

hth
simon

schase · May 27, 2004

I am more than a bit lost here,

I'm guessing the .xml page would load the values dynamically from the database, then the script above pulls it in if it gets selected.

hmm. might be more than I should tackle at this moment. I have to get this app done and done soon. I had hoped to rewrite this lead manager in PHP - but knowing only ASP so far. It is too much to tackle given time constraints.

Of course I can rewrite it again later on. Maybe PHP, maybe asp.net maybe something something xml.

Not sure yet.

"Never underestimate the power of determination"

Stuart
Net+

simonchristieis · May 27, 2004

all you need to do to create the xml file in asp is:

Code:

sql = "select * from postCodeTable"

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Code","me","whatever" 

Set rs = Server.CreateObject("ADODB.RecordSet")
set rs = conn.execute(sql)

rs.save "C:\here\there\everywhere\postcode.xml" ,1

easy!

schase · May 27, 2004

holy cow.

uhh ok, so i have this

Code:

<%
sql = "select * from tblZip"

Set conn = Server.CreateObject("ADODB.Connection")
conn.open myDSN

Set rs = Server.CreateObject("ADODB.RecordSet")
set rs = conn.execute(sql)

rs.save "C:\here\there\everywhere\postcode.xml" ,1
%>

Which when i ran it - made this for xml file

Code:

- <s:Schema id="RowsetSchema">
- <s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
- <s:AttributeType name="zipID" rs:number="1" rs:nullable="true" rs:writeunknown="true">
  <s:datatype dt:type="i1" dt:maxLength="1" rs:precision="4" rs:fixedlength="true" /> 
  </s:AttributeType>
- <s:AttributeType name="fldZip" rs:number="2" rs:writeunknown="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" /> 
  </s:AttributeType>
- <s:AttributeType name="fldCity" rs:number="3" rs:writeunknown="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="40" rs:maybenull="false" /> 
  </s:AttributeType>
- <s:AttributeType name="fldState" rs:number="4" rs:writeunknown="true">
  <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30" rs:maybenull="false" /> 
  </s:AttributeType>
  <s:extends type="rs:rowbase" /> 
  </s:ElementType>
  </s:Schema>
- <rs:data>
  <z:row zipID="1" fldZip="79924" fldCity="El Paso" fldState="TX" /> 
  <z:row zipID="2" fldZip="79936" fldCity="El Paso" fldState="TX" /> 
  <z:row zipID="3" fldZip="79914" fldCity="El Paso" fldState="TX" /> 
  </rs:data>
  </xml>

wow, this uhh looking good?

"Never underestimate the power of determination"

Stuart
Net+

schase · May 27, 2004

going along with it, how will your search string of

Code:

strSearch = "contains(@postcode, 'EC4 4EC')"

actually search for it as I type in the zip code into the text box field - then auto populate the city and state if a match is found?

"Never underestimate the power of determination"

Stuart
Net+

simonchristieis · May 27, 2004

I'm going home - not ignoring you, but use this:

strSearch = "contains(@fldZip, '79924')"

var nH=tXML.selectNodes("/xml/rs:data/z:row["+ strSearch +"]");

this will return the first record.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Type Zip Code - auto fill city & state 1

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor