Passing a Trimmed Value from a FormField

Question

Nolwen 0 Newbie Poster

16 Years Ago

Hi,

I try to pass a value from a form field to a detail page,

I would like to be able to trim the form field content to pass only the 2 first characters in the string.

The form is like this:

Enter ZipCode:

<form action="index.asp?Zip=<%= Request.Form ("Zip")%>" method="Get" enctype="multipart/form-data">
<input type="text" name="Zip" id="Zip" />
<input type="submit" value="Search" /> 
</form>

All suggestion is appreciated,

Thank you in advance,

Denis.

asp

3 Contributors
31 Replies
251 Views
2 Days Discussion Span
Latest Post 16 Years Ago Latest Post by TobbeK

All 31 Replies

TobbeK 0 Junior Poster

16 Years Ago

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% ' "

TobbeK 0 Junior Poster

16 Years Ago

OK, last shot

"SELECT * FROM Addresses WHERE Zip LIKE '"&izip&%"'"

Otherwise, try to set the variable to integer
izip = Cint(Left(zip,2))
if your database values if in other data type then this string.

Or change the data type in your data base temporarily to text to see that things work

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

Your queries are wrong, that's why you cannot return anything correctly. Here is the query you need to use and I will explain:

'You need to trim your statement, as if
'it picks up a space at the beginning or end
'of the querystring, your query will not work.
'Lose the Left statement. Although it's good,
'substring works faster in this statement as it
'is a small string. Left works great on long strings.

'Set izip to this:

izip = Trim(Request.QueryString("Zip"))
izip = izip.substring(1,2)

'Now for your query:
"SELECT * FROM Addresses WHERE Zip LIKE '" & izip & "%'"

'You must have the wild card within the single
'quotes in order for it to work. It shouldn't matter
'at all what datatype you're using within the
'database, as it treats the contents as a string
'when you search with the LIKE statement.

'Please also be aware of your database server
'version, as not all versions use the wildcard
'feature, which will in return grab only
'the substring and not find a match. But if you
'said that it works hard coded, then you have
'a good version.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TobbeK 0 Junior Poster · Answer 1 · 2008-02-16T16:11:18+00:00

You can try place the ("zip") into a variable like this and then call it for passing

zip = "12345"
izip = Left(zip,2)

izip Output: "12"

TobbeK 0 Junior Poster · Answer 2 · 2008-02-16T16:28:46+00:00

Or like this

<%
zip = Request.Form("Zip")
izip = Left(zip,2)
%>

<form action="index.asp?Zip=<% = izip %>" method="Get" enctype="multipart/form-data">
<input type="text" name="Zip" id="Zip" />
<input type="submit" value="Search" />
</form><form action="index.asp?Zip=<%= Request.Form ("Zip")%>" method="Get" enctype="multipart/form-data">
<input type="text" name="Zip" id="Zip" />
<input type="submit" value="Search" />
</form>

Nolwen 0 Newbie Poster · Answer 3 · 2008-02-16T23:02:37+00:00

Hi Tobbek,

Thank you for your help & time,

I inserted you codes into the page (Replaced my Form),
(It is ASPvb)

It passes the value, but don't get trimmed,

Also, I am a "Verry" beginner in script and programming,
And I may need "baby steps" recomandations.

Here are the code into page,

Can you tell me why it does not trim the form field value:
(What do I do wrong (Is it syntax, or else)

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!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.Form("Zip")
izip = Left(zip,2)
%>

<form action="index.asp?Zip=<%=  izip %>" method="Get">
<input type="text" name="Zip" id="Zip" />
<input type="submit" value="Search" /> 

</form> 


</body>
</html>

Thanks again,

Regards,

Denis

TobbeK 0 Junior Poster · Answer 4 · 2008-02-16T23:21:54+00:00

Maybe you need to change your textfield a little

From:
<input type="text" name="Zip" id="Zip" />
To
<input type="text" name="Zip" value="Zip" />

When you request the value by Request.Form("zip") then this should work.

For your own training:

Create testpage.asp and the use the different request methods to pick up the value.
As you can see below the 'zip = Request.Form("Zip") is in use for now and the method is set to "post"

When using the "get" method you should also change the request to 'zip = Request.QueryString("Zip") and that one is closed by now

<%
<form action="testpage.asp" method="Post">
<input type="text" name="Zip" value="Zip" />
<input type="submit" value="Search" />

</form>

'zip = Request.QueryString("Zip")
zip = Request.Form("Zip")
izip = Left(zip,2)

Response.Write(izip)
%>

Nolwen 0 Newbie Poster · Answer 5 · 2008-02-16T23:33:20+00:00

Thanks,

I tried that,

No luck,

Playing around with syntax, but as I said, not sure to go anywhere...

Any help is welcome.

Regards,

Denis.

TobbeK 0 Junior Poster · Answer 6 · 2008-02-16T23:38:55+00:00

OKIDOKI
My mistake, need to separate the HTML from ASP. Now it is working

<form action="testpage.asp" method="Post">
<input type="text" name="Zip" value="" />
<input type="submit" value="Search" />
</form>
<%

'zip = Request.QueryString("Zip")
zip = Request.Form("Zip")
izip = Left(zip,2)

Response.Write(izip)
%>

TobbeK 0 Junior Poster · Answer 7 · 2008-02-16T23:42:55+00:00

To be sure, this is when using the "get" method

<form action="testpage.asp" method="get">
<input type="text" name="Zip" value="" />
<input type="submit" value="Search" />
</form>
<%

zip = Request.QueryString("Zip")
'zip = Request.Form("Zip")
izip = Left(zip,2)

Response.Write(izip)
%>

TobbeK 0 Junior Poster · Answer 8 · 2008-02-16T23:58:05+00:00

I have put this into two files, and it works fine and cut the value to view only the 2 first characters

This is the index.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!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>
</html>

This is the receiving testpage.asp

<%
zip = Request.QueryString("Zip")
'zip = Request.Form("Zip")
izip = Left(zip,2)

Response.Write(izip)

%>

Nolwen 0 Newbie Poster · Answer 9 · 2008-02-17T00:00:10+00:00

Yes!!

That is exactly what I needed,

Great example, and Clue, and some food for my "Noodles".

Now, I just have to Filter my recordset by the Query string Izip.

Thanks again for your time and knowledge.

Working on it,

Will be back with more questions I am sure.

Thanks a LOT!

Denis

TobbeK 0 Junior Poster · Answer 10 · 2008-02-17T00:02:32+00:00

TobbeK 0 Junior Poster

16 Years Ago

Good to know, you are welcome

TobbeK 0 Junior Poster · Answer 11 · 2008-02-17T00:16:38+00:00

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)

Nolwen 0 Newbie Poster · Answer 12 · 2008-02-17T00:31:30+00:00

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.

TobbeK 0 Junior Poster · Answer 13 · 2008-02-17T00:42:49+00:00

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

TobbeK 0 Junior Poster · Answer 14 · 2008-02-17T00:48:42+00:00

This one does the same as previous code

izip = Left(zip,3)&" "&Right(zip,2)

Nolwen 0 Newbie Poster · Answer 15 · 2008-02-17T02:10:05+00:00

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
%>

Nolwen 0 Newbie Poster · Answer 16 · 2008-02-17T23:54:21+00:00

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.

TobbeK 0 Junior Poster · Answer 17 · 2008-02-18T00:02:43+00:00

okidoki then try this (a longshot)

izip = Left(zip,2)&"%"

"SELECT * FROM Addresses WHERE Zip LIKE "& izip

TobbeK 0 Junior Poster · Answer 18 · 2008-02-18T00:12:30+00:00

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

Nolwen 0 Newbie Poster · Answer 19 · 2008-02-18T00:20:50+00:00

OK,

Thanks,

I need to work on this.

Thanks for your inputs,

Regards,

Denis.

TobbeK 0 Junior Poster · Answer 20 · 2008-02-18T00:33:41+00:00

Here's another one to shoot

"SELECT * FROM Addresses WHERE Zip LIKE ' " & izip & " % ' "
OR
"SELECT * FROM Addresses WHERE Zip LIKE ' " & izip & " '% "

TobbeK 0 Junior Poster · Answer 21 · 2008-02-18T00:43:55+00:00

You do have this at the same page as the SQL query so that the SQL qyery finds the variable zip ??

Zip = Request.QueryString("Zip")
'Zip = Request.Form("Zip")
iZip = Left(Zip,2)

TobbeK 0 Junior Poster · Answer 22 · 2008-02-18T00:59:14+00:00

The sending page, I have rewrited it a bit, hope you dont mind

<body>
<%
Zip = Request.QueryString("Zip")
'Zip = Request.Form("Zip")
iZip = Left(Zip,2)

Response.Write(iZip)
%> 
<form action="index.asp" method="POST">
<input type="text" name="Zip" value="Zip" />
<input type="submit" value="Search" /> 

</form> 
</body>

I assume this is the index.asp. With this you should get a full zip code from the database

<%
Dim RecLoc__MMColParam
RecLoc__MMColParam = "1"
If (Request.Form("iZip") <> "") Then 
RecLoc__MMColParam = Request.Form("iZip")
End If
%>
<%
Dim RecLoc, RecLoc_cmd, RecLoc_numRows,Zip,izip

'Zip = Request.QueryString("Zip")
Zip = Request.Form("Zip")
iZip = Left(Zip,2)

Set RecLoc_cmd = Server.CreateObject ("ADODB.Command")
RecLoc_cmd.ActiveConnection = MM_ConnLoc_STRING
RecLoc_cmd.CommandText = "SELECT * FROM Addresses WHERE Zip =" & Zip
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>
<%
%>
<%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
%>

Nolwen 0 Newbie Poster · Answer 23 · 2008-02-18T01:42:20+00:00

It's getting there,

Still the list display the address filtered by the full zip code,

If I enter 91306, it returns the only address for the full Zip Code, not displaying the 91307,91XXX, ect...

In this case I try to list any Address starting with 91.

Here is the Codes for The Index.asp:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/ConnLoc.asp" -->
<%
Dim RecLoc__MMColParam
RecLoc__MMColParam = "1"
If (Request.Form("iZip") <> "") Then 
RecLoc__MMColParam = Request.Form("iZip")
End If
%>
<%
Dim RecLoc, RecLoc_cmd, RecLoc_numRows,Zip,izip

'Zip = Request.QueryString("Zip")
Zip = Request.Form("Zip")
iZip = Left(Zip,2)

Set RecLoc_cmd = Server.CreateObject ("ADODB.Command")
RecLoc_cmd.ActiveConnection = MM_ConnLoc_STRING
RecLoc_cmd.CommandText = "SELECT * FROM Addresses WHERE Zip =" & Zip
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>


<form method="Get">
<input type="text" name="Zip" id="Zip" value="<% Response.Write(iZip)%>" />

</form> 


<% 
While ((Repeat1__numRows <> 0) AND (NOT RecLoc.EOF)) 
%>
  <div><%=(RecLoc.Fields.Item("Shop").Value)%>, <%=(RecLoc.Fields.Item("Street").Value)%>, <%=(RecLoc.Fields.Item("Zip").Value)%></div>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  RecLoc.MoveNext()
Wend
%>
</body>
</html>
<%
RecLoc.Close()
Set RecLoc = Nothing
%>

TobbeK 0 Junior Poster · Answer 24 · 2008-02-18T02:53:00+00:00

Correct me if I'm wrong, you receive the exact match 91306 but nothing else ?

If that so, it is working properly with the code.
Now, change your SELECT query, test if one of these will works with the izip variable.

izip = Left(zip,2)

"SELECT * FROM Addresses WHERE Zip LIKE ' " & izip & " % ' "
OR
"SELECT * FROM Addresses WHERE Zip LIKE ' " & izip & " '% "

Nolwen 0 Newbie Poster · Answer 25 · 2008-02-18T03:19:23+00:00

Correct.

It is Returning only the match for full ZipCode,

I tried the 2 different SQL you nicely wrote for this,

THe First one, returns nothing (Only the FormField display the 2 first digit as suppposed to.

The second one trow a Jet error (Syntax) as follow:

Microsoft JET Database Engine (0x80040E14)
Syntax error in query expression 'Zip LIKE ' 91 '%'.
/sites/test/index.asp, line 23

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727; InfoPath.2; Windows-Media-Player/10.00.00.3990; .NET CLR 3.0.04506.30)

Page:
POST 9 bytes to /sites/test/index.asp

POST Data:
Zip=91307

Aaaah SQL and ' & " & ( , and )...,

Maybe learning Traditional Chinese would be simpler?!

=))

THanks anyway,

Your help is greatly appreciated.

Denis

Nolwen 0 Newbie Poster · Answer 26 · 2008-02-18T03:46:20+00:00

Changed the datatype as txt in db,

No success,

Need to sleep on this one,

Thanks again for all your time and knowledge on this one,

Later

Regards,

Denis.

Passing a Trimmed Value from a FormField

Recommended Answers Collapse Answers

All 31 Replies

Recommended Answers