•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 374,181 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,409 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ASP advertiser: Lunarpages ASP Web Hosting
Views: 2036 | Replies: 45 | Solved
 |
•
•
Join Date: Feb 2008
Posts: 135
Reputation: 
Rep Power: 1
Solved Threads: 3
Junior Poster
What am I missing ....
I feel like a complete doodle .....
You are allowed to laugh and slap your knees :-)
I tried this also without the execute(sql).
I feel like a complete doodle .....
You are allowed to laugh and slap your knees :-)
whatinput = "Coldstreet 123A 12345 Freezytown"
array = whatinput.split(" ")
SQL = "SELECT CL.organisation, CA.street,CA.zipcode,CA.city,RG.state,RG.stateID,KO.county,KO.countyID "&_
"FROM tbclientaddress CA "&_
"JOIN tbclients CL ON CA.clientlink = CL.clientID "&_
"JOIN tbstate RG ON CA.state = RG.stateID "&_
"JOIN tbcounty KO ON CA.county = KO.countyID "&_
"WHERE " For numero = 0 To UBounds(array)
If numero = 0 Then SQL = SQL & " CA.street LIKE '%" & array(numero) & "%' "&_
If numero = 1 Then SQL = SQL & " OR CA.streetnumber LIKE '%" & array(numero) & "'" &_
If numero = 2 Then SQL = SQL & " OR CA.zipcode LIKE '%" & array(numero) & "%'" &_
If numero = 3 Then SQL = SQL & " OR CA.city LIKE '%" & array(numero) & "%'" &_
If numero > 3 Then SQL = SQL & " OR CA.city LIKE '%" & array(numero) & "%" & array(numero-1) & "%' OR CA.city LIKE '%" array(numero) & "%'"
Next
execute(sql)
Set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn
Do While Not RS.EOF
etc.. etc..
I tried this also without the execute(sql).
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
Ahh hold on a sec.
The index must be dynamic, I did not see that before.
I'll throw up some code later on...
The index must be dynamic, I did not see that before.
I'll throw up some code later on...
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
whatinput = "Coldstreet 123A 12345 Freezytown"
array = whatinput.split(" ")
SQL = "SELECT CL.organisation, CA.street,CA.zipcode,CA.city,RG.state,RG.stateID,KO.county,KO.countyID "&_
"FROM tbclientaddress CA "&_
"JOIN tbclients CL ON CA.clientlink = CL.clientID "&_
"JOIN tbstate RG ON CA.state = RG.stateID "&_
"JOIN tbcounty KO ON CA.county = KO.countyID "&_
"WHERE "
For numero = 0 To UBounds(array)
Select Case numero
Case 0
SQL = SQL & " CA.street LIKE '%" & array(numero) & "%' "
Case 1
SQL = SQL & " OR CA.streetnumber LIKE '%" & array(numero) & "%'"
Case 2
SQL = SQL & " OR CA.zipcode LIKE '%" & array(numero) & "%'"
Case 3
SQL = SQL & " OR CA.city LIKE '%" & array(numero) & "%'"
Case > 3
SQL = SQL & " OR CA.city LIKE '%" & array(numero - 1) & "%" & array(numero) & "%' OR CA.city LIKE '%" array(numero) & "%'"
End Select
Next
Set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn
Do While Not RS.EOF
etc.. etc..This is your SQL string for the following line:
"Coldstreet 123A 12345 Freezy Town" SQL = .... For .... .... Next .... 'SQL Output String: SQL = "SELECT CL.organisation, CA.street,CA.zipcode,CA.city,RG.state,RG.stateID,KO.county,KO.countyID "&_ "FROM tbclientaddress CA "&_ "JOIN tbclients CL ON CA.clientlink = CL.clientID "&_ "JOIN tbstate RG ON CA.state = RG.stateID "&_ "JOIN tbcounty KO ON CA.county = KO.countyID "&_ "WHERE CA.street LIKE '%Coldstreet%'"&_ " OR CA.streetnumber LIKE '%123A%'" &_ " OR CA.zipcode LIKE '%12345%'" &_ " OR CA.city LIKE '%Freezy%'" &_ " OR CA.city LIKE '%Freezy%Town%'" " OR CA.city LIKE '%Town%'"
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
But please answer me this... so what happens if my address is..
15011 Via Hondonado apt. E
San Diego, CA 92129
How would you handle that, and how would you split the strings?
My suggestion, place a comma or something that is unique to separate portions. Then separate them.
Via Hondonado apt. B;15046;92129;San Diego
Then do a split for ";", and replace all " " with "%".
Then you will only ever have up to 4 strings.
Street Name, Street Number, Zip Code, City.
And this way you are set incase someone has a bunch of spaces in their street name.
15011 Via Hondonado apt. E
San Diego, CA 92129
How would you handle that, and how would you split the strings?
My suggestion, place a comma or something that is unique to separate portions. Then separate them.
Via Hondonado apt. B;15046;92129;San Diego
Then do a split for ";", and replace all " " with "%".
Then you will only ever have up to 4 strings.
Street Name, Street Number, Zip Code, City.
And this way you are set incase someone has a bunch of spaces in their street name.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
Ahh hold on a sec.
The index must be dynamic, I didn't see that before.
The user input can be anything like this:
Whereinput = "Coldstreet" ' streetname
Or
Whereinput = "Coldstreet 123A" ' streetname and streetnumber
Or
Whereinput = "Coldstreet 123A 12345" ' streetname and streetnumber and zipcode
Or
Whereinput = "Coldstreet 12345" ' streetname and zipcode
Or
Whereinput = "12345 Freezytown" ' zipcode and city
Or
Whereinput = "12345" ' zipcode
Or
Whereinput = "Freezytown" ' city
I "just" have to decide which combinations that are not allowed like the ones below.
Non valid search strings:
Whereinput = "123A" ' streetnumber alone
Or
Whereinput = "123A 12345" ' streetnumber and zipcode
Or
Whereinput = "123A Freezytown" ' streetnumber and city
I'll throw up some more code later on for review...
My current code actually handle different combinations as I described, but Not as long there are any spaces in streetname and/or cityname like this.
Cold Street
or
Freezy Town
The index must be dynamic, I didn't see that before.
The user input can be anything like this:
Whereinput = "Coldstreet" ' streetname
Or
Whereinput = "Coldstreet 123A" ' streetname and streetnumber
Or
Whereinput = "Coldstreet 123A 12345" ' streetname and streetnumber and zipcode
Or
Whereinput = "Coldstreet 12345" ' streetname and zipcode
Or
Whereinput = "12345 Freezytown" ' zipcode and city
Or
Whereinput = "12345" ' zipcode
Or
Whereinput = "Freezytown" ' city
I "just" have to decide which combinations that are not allowed like the ones below.
Non valid search strings:
Whereinput = "123A" ' streetnumber alone
Or
Whereinput = "123A 12345" ' streetnumber and zipcode
Or
Whereinput = "123A Freezytown" ' streetnumber and city
I'll throw up some more code later on for review...
My current code actually handle different combinations as I described, but Not as long there are any spaces in streetname and/or cityname like this.
Cold Street
or
Freezy Town
Where = "Coldstreet 123A 12345 Freezytown" Words = Split(Where, " ") For i = 0 to Ubound(Words) If i = 0 AND i < UBound(Words) AND IsNumeric(Words(i)) = False Then Response.Write i & " = Street: " & Words(i) & "<br>" End If If i = 1 AND i < UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then Response.Write i & " = Streetnumber: " & Words(i) & "<br>" ElseIf i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then Response.Write i & " = Streetnumber: " & Words(i) & "<br>" End If If i > 0 AND i < UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then Response.Write i & " = Zipcode: " & Words(i) & "<br>" ElseIf i = UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then Response.Write i & " = Zipcode: " & Words(i) & "<br>" End If If i > 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then Response.Write i & " = City: " & Words(i) & "<br>" ElseIf i = 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then Response.Write i & " = City: " & Words(i) & "<br>" End If Next
Last edited by TobbeK : Mar 6th, 2008 at 2:39 pm.
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
Yes but how do you differentiate between the inputs?
Do you have checkboxes or something?
Do you have checkboxes or something?
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
At this point just by the string.
As I have thinked yet, and coded a bit:
streetnumber is normally no longer than 4 chars, containing a start with one or more digits and sometimes with a ending letter. It is very rare this is longer than 3-4 chars.
zipcode can only be 5 digits in length.
Now, we can by these parameters separate atleast two possible inputs. Then we have the streetname and cityname that is a bit tricky atleast when it comes to spaces.
If the input holds just a streetname or a cityname they can be used just as is for db search, and probably even when the input holds both, a split then db search.
Test the code I have provided and cut off different pieces from the input, it is actually works.
Last update:
As I have thinked yet, and coded a bit:
streetnumber is normally no longer than 4 chars, containing a start with one or more digits and sometimes with a ending letter. It is very rare this is longer than 3-4 chars.
zipcode can only be 5 digits in length.
Now, we can by these parameters separate atleast two possible inputs. Then we have the streetname and cityname that is a bit tricky atleast when it comes to spaces.
If the input holds just a streetname or a cityname they can be used just as is for db search, and probably even when the input holds both, a split then db search.
Test the code I have provided and cut off different pieces from the input, it is actually works.
Last update:
Where = "Coldstreet 123A 12345 Freezytown" Where = Trim(Where) Words = Split(Where, " ") For i = 0 to Ubound(Words) If i = 0 AND i < UBound(Words) AND IsNumeric(Words(i)) = False Then Response.Write i & " = Street: " & Words(i) & "<br>" End If If i = 1 AND i < UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then Response.Write i & " = Streetnumber: " & Words(i) & "<br>" ElseIf i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then Response.Write i & " = Streetnumber: " & Words(i) & "<br>" End If If i < UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then Response.Write i & " = Zipcode: " & Words(i) & "<br>" ElseIf i = UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then Response.Write i & " = Zipcode: " & Words(i) & "<br>" End If If i > 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then Response.Write i & " = City: " & Words(i) & "<br>" ElseIf i = 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then Response.Write i & " = City: " & Words(i) & "<br>" End If Next
Last edited by TobbeK : Mar 6th, 2008 at 3:45 pm.
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
Yes I am sure it works, but you shouldn't rely on users being narrow minded about how to enter in their address.
You should have an identifier that separates all of them, IMO
You should have an identifier that separates all of them, IMO
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
I am counting on the possibility with misspelling and even some strange hits. It cannot be absolute failsafe.
This is suppose to serve as a search engine for company quick search.
A visiting user can use two fields for that
Example:
Input fields:
what = "programming"
where = "12345 freezytown"
Output:
1 hit:
SheSaidImaPregy & Co
ASP Professional and posting shark
ColdStreet 123A
12345 Freezytown
"didn't know that guy works in Freezy', Cool !"
But I must try to design this facilty to be as exact as possible.
And in addition I am going to create a form for more advanced searches for users needed exact hits an so on.
Separators can be good to have as you suggest.
This is suppose to serve as a search engine for company quick search.
A visiting user can use two fields for that
Example:
Input fields:
what = "programming"
where = "12345 freezytown"
Output:
1 hit:
SheSaidImaPregy & Co
ASP Professional and posting shark
ColdStreet 123A
12345 Freezytown
"didn't know that guy works in Freezy', Cool !"
But I must try to design this facilty to be as exact as possible.
And in addition I am going to create a form for more advanced searches for users needed exact hits an so on.
Separators can be good to have as you suggest.
Last edited by TobbeK : Mar 6th, 2008 at 5:35 pm.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb ASP Marketplace
Linear Mode
Similar Threads
- Replace a space with an underscore SOLVED (PHP)
- Limiting grep searches to a given file type? (Web Developers' Lounge)
- Desiging a set of rules for a match (C++)
Other Threads in the ASP Forum
- Previous Thread: creating download object.
- Next Thread: Updating table in a remote database
