Passing a Trimmed Value from a FormField

•

What is DaniWeb IT Discussion Community?

You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 391,666 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 2,878 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.

Join our community!

Please support our ASP advertiser: Lunarpages ASP Web Hosting

Thread Tools Display Modes

Views: 1422 | Replies: 31

Page 3 of 4

•

Join Date: Feb 2008

Posts: 13

Reputation: Nolwen is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

Nolwen

Offline

Newbie Poster

Re: Passing a Trimmed Value from a FormField

#21

Feb 17th, 2008

OK,

Thanks,

I need to work on this.

Thanks for your inputs,

Regards,

Denis.

•

Join Date: Feb 2008

Posts: 135

Reputation: TobbeK is an unknown quantity at this point

Rep Power: 1

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Passing a Trimmed Value from a FormField

#22

Feb 17th, 2008

Here's another one to shoot

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

Last edited by TobbeK : Feb 17th, 2008 at 1:34 pm.

•

Join Date: Feb 2008

Posts: 135

Reputation: TobbeK is an unknown quantity at this point

Rep Power: 1

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Passing a Trimmed Value from a FormField

#23

Feb 17th, 2008

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)

•

Join Date: Feb 2008

Posts: 135

Reputation: TobbeK is an unknown quantity at this point

Rep Power: 1

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Passing a Trimmed Value from a FormField

#24

Feb 17th, 2008

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

Help with Code Tags

(Toggle Plain Text)

<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

Help with Code Tags

(Toggle Plain Text)

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

Last edited by TobbeK : Feb 17th, 2008 at 2:02 pm.

•

Join Date: Feb 2008

Posts: 13

Reputation: Nolwen is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

Nolwen

Offline

Newbie Poster

Re: Passing a Trimmed Value from a FormField

#25

Feb 17th, 2008

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:

Help with Code Tags

(Toggle Plain Text)

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

•

Join Date: Feb 2008

Posts: 135

Reputation: TobbeK is an unknown quantity at this point

Rep Power: 1

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Passing a Trimmed Value from a FormField

#26

Feb 17th, 2008

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

Last edited by TobbeK : Feb 17th, 2008 at 3:55 pm.

•

Join Date: Feb 2008

Posts: 13

Reputation: Nolwen is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

Nolwen

Offline

Newbie Poster

Re: Passing a Trimmed Value from a FormField

#27

Feb 17th, 2008

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

•

Join Date: Feb 2008

Posts: 135

Reputation: TobbeK is an unknown quantity at this point

Rep Power: 1

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Passing a Trimmed Value from a FormField

#28

Feb 17th, 2008

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

Last edited by TobbeK : Feb 17th, 2008 at 4:32 pm.

•

Join Date: Feb 2008

Posts: 13

Reputation: Nolwen is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

Nolwen

Offline

Newbie Poster

Re: Passing a Trimmed Value from a FormField

#29

Feb 17th, 2008

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.

•

Join Date: Sep 2007

Posts: 1,054

Reputation: SheSaidImaPregy is an unknown quantity at this point

Rep Power: 3

Solved Threads: 61

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Passing a Trimmed Value from a FormField

#30

Feb 18th, 2008

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

Help with Code Tags

(Toggle Plain Text)

'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.