Henry
|
| Posted: 05/27/2004, 9:30 AM |
|
There must be an easy way to do this, but I can't think of it. I have a basic Add Record grid that gets address information (among other things). I want to set it up so that when user enters a zip code, the city and state fields are automatically populated with the approprate values from a validation table (which I have).
I've got to start it off with an onChange event for the zip field, but then what? Custom sql in the city, st fields?
Thanks for any advice.
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 05/27/2004, 10:36 AM |
|
Probably not as easy as you may think 
To start with, "On Change" is a client event (JavaScript) and you cannot access your database or use SQL in the browser.
There are usually couple solutions:
1. Use OnChange JavaScript to submit the form, then in the Before Show event of the form you could read the submitted Zip value and lookup the City and State values. But I'm not sure what would happen with the other submitted values - you might need to retrieve those values as well to repopulate the form. I haven't tried this but suspect that some testing would be needed to get this right.
2. Convert your data to JavaScript array and place it directly in HTML with the appropriate JavaScript code that would retrieve the City and State values from the array based on Zip.
Here you can find related discussions:
http://groups.google.com/groups?q=javascript%20data%20lookup%20array
I also recommend that you try to find a Website that already does this so that you can see how it's done there, which should help you understand the whole process better and see if they use a method that you like.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
rrodgers
|
| Posted: 05/27/2004, 12:13 PM |
|
Hi Henry,
Check out.
http://www.sylvancomputing.com/ccs/deplist/
It uses a technique from
http://www.atgconsulting.com/gofetch.asp
that would likely work for you. On change event you open a link with a _Blank target the page you open take a parameter you pass, the zip code. The the page you open queries the database using the passed zip code and modifies the caller window with the proper values from the database then closes when done.
rob
Quote Henry:
There must be an easy way to do this, but I can't think of it. I have a basic Add Record grid that gets address information (among other things). I want to set it up so that when user enters a zip code, the city and state fields are automatically populated with the approprate values from a validation table (which I have).
I've got to start it off with an onChange event for the zip field, but then what? Custom sql in the city, st fields?
Thanks for any advice.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/27/2004, 12:48 PM |
|
BTW, Rob, you may want to take a look at this version that doesn't pop-up a visible window: http://performance.speedycarparts.com/
I think that they use a hidden frame instead.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Henry
|
| Posted: 05/27/2004, 2:55 PM |
|
Those are quite tricky. Here's what I've been doing in the meantime... I think it will work, but not quite there yet. I basically want to get two values from user -- zip and HS_State, then use them to populate City, State, (from zip) and the HS listbox (from the hs_state).
Top of the page has a form with a text box for ZIPand listbox with valid states. They enter the zip first then select the state. The onchange event for the state does this...
window.location.href = "Recr.asp?zip_id=" + Srch.zip_srch.value + "&state_id=" + this.value;
The City field in the record below has this in Before Show...
if not isempty(CCGetParam("zip_id",Empty)) Then
tblRecruitsIncoming.fld_City.value = CCDLookup("GTVZIPC_CITY","GTVZIPC","GTVZIPC_CODE=" & CCToSQL(Srch.zip_srch.value,"text"),"ADM_SqlServer")
end if
I'm having trouble with the CCDLookup. Am I not specifying the connection right? I get this error: Variable is undefined: 'ADM_SqlServer'
The params are getting passed right...
/Recr.asp?zip_id=28036&state_id=DC
Ideas?
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/27/2004, 4:54 PM |
|
This looks similar to rob's solution/example above.
Regarding the code, as the last parameter of CCDLookup you need to specify the database connection prefixed with DB, for example DBConnection1 , without quotes.
See http://docs.codecharge.com/studio/html/ProgrammingTechn...gleDBValue.html
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
rrodgers
|
| Posted: 05/27/2004, 5:10 PM |
|
Peter,
That is pretty slick. But for some reason Zone Alarm didn't like it. But otherwise it works great.
Rob
Quote peterr:
|
|
|
|
Henry
|
| Posted: 05/27/2004, 5:36 PM |
|
I think that with 42000 zip codes that would be a lot to load as an array. If I can get my ugly way to work, I'll probably stick with that.
Soif I've named my datasource Adm_SQLServer, then in the CCDLookup I refer to it as "... ,DBAdm_SqlServer) " (I know, no quotes.
It would be nice if the documentation were clear about that.
And for that matter, there's no mention of CCToSQL at all in the documentation. Some references to it here in the forum, but no proper documentation.
|
|
|
|
rrodgers
|
| Posted: 05/28/2004, 3:55 AM |
|
Are you using regular zip codes or zip + 4? I know that some regular zipcodes refer to more than one city.
Quote Henry:
I think that with 42000 zip codes that would be a lot to load as an array. If I can get my ugly way to work, I'll probably stick with that.
Soif I've named my datasource Adm_SQLServer, then in the CCDLookup I refer to it as "... ,DBAdm_SqlServer) " (I know, no quotes.
It would be nice if the documentation were clear about that.
And for that matter, there's no mention of CCToSQL at all in the documentation. Some references to it here in the forum, but no proper documentation.
|
|
|
|
Henry
|
| Posted: 05/28/2004, 11:24 AM |
|
Yay! Working now. Thanks for everyone's help and great ideas.
Yes, using regular 5-digit zips. Since this is only pre-filling the city and state fields, I'm not worried about those that might return something different. Users can type over if they need to.
|
|
|
|
JOE RAY
|
| Posted: 06/11/2004, 8:09 AM |
|
WHAT IS A ZIP FIELD
|
|
|
|
peterr
Posts: 5971
|
| Posted: 06/11/2004, 10:20 AM |
|
Postal Code.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
