•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 392,036 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 4,284 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: 1425 | Replies: 31
 |
•
•
Join Date: Feb 2008
Posts: 135
Reputation: 
Rep Power: 1
Solved Threads: 3
Junior Poster
Good to know, you are welcome
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
You can also test this in your receiver to view the full zipcode with a space between
Like this: 123 45
The first Left takes the 3 first chars the second Left takes char 4 and 5
izip = Left(zip,3)&" "&Mid(zip,4,2)
Like this: 123 45
The first Left takes the 3 first chars the second Left takes char 4 and 5
izip = Left(zip,3)&" "&Mid(zip,4,2)
•
•
Join Date: Feb 2008
Posts: 13
Reputation:
Rep Power: 1
Solved Threads: 0
Newbie Poster
Nice!
So, & is acting as a "joint fuction" The " " create the blank, and before and after the trim syntax.
If you don't mind, I have few questions (Sorry if it sound naive).
Why 4,2 returns the character 5?
(is the logic - starting @ Char 4, and trim the string value to 2 Char?)
What "Mid" is meaning?
(I would naturally have think "Right", but that's my non ASP brain!).
I am starting loving it!
So much to learn!
Regards,
Denis.
So, & is acting as a "joint fuction" The " " create the blank, and before and after the trim syntax.
If you don't mind, I have few questions (Sorry if it sound naive).
Why 4,2 returns the character 5?
(is the logic - starting @ Char 4, and trim the string value to 2 Char?)
What "Mid" is meaning?
(I would naturally have think "Right", but that's my non ASP brain!).
I am starting loving it!
So much to learn!
Regards,
Denis.
Last edited by Nolwen : Feb 16th, 2008 at 1:32 pm. Reason: There was a"Emoticon" not needed
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
Start from 4 and then counts 2 forward including 4
You have Left(), Mid() and Right() to play with and mid is the only one where you need to set a start value.
What happend in code was that you picked up zip 2 times and split it up into two values
You have Left(), Mid() and Right() to play with and mid is the only one where you need to set a start value.
What happend in code was that you picked up zip 2 times and split it up into two values
Last edited by TobbeK : Feb 16th, 2008 at 1:45 pm.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
This one does the same as previous code
izip = Left(zip,3)&" "&Right(zip,2)
izip = Left(zip,3)&" "&Right(zip,2)
•
•
Join Date: Feb 2008
Posts: 13
Reputation:
Rep Power: 1
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by TobbeK 
This one does the same as previous code
izip = Left(zip,3)&" "&Right(zip,2)
OK, Thats cool,
Played a little with this,
Now, I would like to Filter my recordset (on the reader) by Comparing The 2 Digit (Result of the trim, and the 2 first digit in the DB Table's Zip column.
Show all records that have a zipCode starting with the same 2 digits.
But I must have a wrong SQL select statement.
(If you have any guidance for me, would be appreciated)
I have 2 pages, here is the Search page:
<body>
<%
Zip = Request.QueryString("Zip")
'Zip = Request.Form("Zip")
iZip = Left(Zip,2)
Response.Write(iZip)
%>
<form action="index.asp?Zip=<%Response.Write(iZip)%>" method="Get">
<input type="text" name="Zip" id="Zip" />
<input type="submit" value="Search" />
</form>
</body>And here is the result page:
<%
Dim RecLoc__MMColParam
RecLoc__MMColParam = "1"
If (Request.Form("iZip") <> "") Then
RecLoc__MMColParam = Request.Form("iZip")
End If
%>
<%
Dim RecLoc
Dim RecLoc_cmd
Dim RecLoc_numRows
Set RecLoc_cmd = Server.CreateObject ("ADODB.Command")
RecLoc_cmd.ActiveConnection = MM_ConnLoc_STRING
RecLoc_cmd.CommandText = "SELECT * FROM Addresses WHERE Zip LIKE ?"
RecLoc_cmd.Prepared = true
RecLoc_cmd.Parameters.Append RecLoc_cmd.CreateParameter("param1", 200, 1, 255, "%" + RecLoc__MMColParam + "%") ' adVarChar
Set RecLoc = RecLoc_cmd.Execute
RecLoc_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
RecLoc_numRows = RecLoc_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<%
Zip = Request.QueryString("Zip")
'zip = Request.Form("Zip")
iZip = Left(Zip,2)
%>
<%Response.Write(iZip)%>
<input name="iZip" type="Text" value="<%Response.Write(iZip)%>" />
<%
While ((Repeat1__numRows <> 0) AND (NOT RecLoc.EOF))
%>
<div> <%=(RecLoc.Fields.Item("Shop").Value)%>, <%=(RecLoc.Fields.Item("Street").Value)%>, <%=(RecLoc.Fields.Item("Zip").Value)%>, <%=(RecLoc.Fields.Item("State").Value)%></div>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RecLoc.MoveNext()
Wend
%>
</body>
</html>
<%
RecLoc.Close()
Set RecLoc = Nothing
%> •
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
Something like this could do the trick, the wildcard (%) is placed after the variable and should look only for pattern that you have placed there
So if the variable izip contains "12" the SQL query should only look for posts that starts with "12"
"SELECT * FROM Addresses WHERE Zip LIKE '" & izip & % "'"
I have not tested this but if you take a test first with a static value like this and query the database
"SELECT * FROM Addresses WHERE Zip LIKE '12% ' "
So if the variable izip contains "12" the SQL query should only look for posts that starts with "12"
"SELECT * FROM Addresses WHERE Zip LIKE '" & izip & % "'"
I have not tested this but if you take a test first with a static value like this and query the database
"SELECT * FROM Addresses WHERE Zip LIKE '12% ' "
•
•
Join Date: Feb 2008
Posts: 13
Reputation:
Rep Power: 1
Solved Threads: 0
Newbie Poster
Hi Tobbek,
Work like a charm if hardcoded,
Does not return the data set on page in the SQL referencing to izip.
I use:
"SELECT * FROM Addresses WHERE Zip LIKE 'izip %' "
Must be a syntax thing,
I created a Form field on the Reader page to capture the izip value and it shows just fine,
So the "mechanic works" (Trimming, passing, capturing and reading the trimmed value)
, but I still can't get it to work in the WHERE close of the SQL.
Regards,
Denis.
Work like a charm if hardcoded,
Does not return the data set on page in the SQL referencing to izip.
I use:
"SELECT * FROM Addresses WHERE Zip LIKE 'izip %' "
Must be a syntax thing,
I created a Form field on the Reader page to capture the izip value and it shows just fine,
So the "mechanic works" (Trimming, passing, capturing and reading the trimmed value)
, but I still can't get it to work in the WHERE close of the SQL.
Regards,
Denis.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
okidoki then try this (a longshot)
izip = Left(zip,2)&"%"
"SELECT * FROM Addresses WHERE Zip LIKE "& izip
izip = Left(zip,2)&"%"
"SELECT * FROM Addresses WHERE Zip LIKE "& izip
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
There is mess to make some of WHERE clauses to function when inputs comes from variables with all of the ' and "" and & in proper order
You can try to get the full zipcode first with this one to see if there are any errors
zip = Request.Form("zip")
"SELECT * FROM Addresses WHERE Zip = "&zip
OR
"SELECT * FROM Addresses WHERE Zip = ' " & zip & " ' "
We dont use LIKE clause here, just hit the road
If it's works OK U should get some answer
You can try to get the full zipcode first with this one to see if there are any errors
zip = Request.Form("zip")
"SELECT * FROM Addresses WHERE Zip = "&zip
OR
"SELECT * FROM Addresses WHERE Zip = ' " & zip & " ' "
We dont use LIKE clause here, just hit the road
If it's works OK U should get some answer
Last edited by TobbeK : Feb 17th, 2008 at 1:14 pm.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb ASP Marketplace
Linear Mode
Other Threads in the ASP Forum
- Previous Thread: can i used MySQL as database server for asp?
- Next Thread: Microsoft JET Database Engine (0x80040E14), Please Help.
