code not display values on the screen

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

code not display values on the screen

Author

Topic

chinedu
Starting Member

8 Posts

Posted - 2004-02-27 : 20:55:01

Greetings everyone!

I have got some serious issues and I will be truly indebted to anone who can assist me with the solution.

I have 2 pages: page1 is called search.asp.

Once you enter your search parameter and hit the submit button,
It looks more like this:
<form name="form1" method="post" action="page2.asp">
<input type="text" name="street">

Just a small example.

Once you enter your search parameter, and hit the submit button, you are taken to
page2.asp which is the code below.

The problem that I am having so far is that whenever I perform a search.
I always get this message: "Click on street name to locate on map and nothing else".

I have used:
response.write SQL
response.end

just to see if values are properly being passed from search.asp to page2.asp and yes they are.
Yet, nothing else happens.

I am really stuck with this.
Thanks in advance.

Can I please ask for your help one more time?

I am not sure if the problem is coming from the sql code below:

SQL = "select full_name, min(xmin(box3d(shape))), min(ymin(box3d(shape))), " _
& "max(xmax(box3d(shape))), max(ymax(box3d(shape))) " _
& "from vw where full_name like '"& street &"%' group by full_name "

Notice the whereclause: where full_name like '" &street &"%'

I just converted that from postgres sql where it originally read:
where full_name ~* '" &street &"%'

So I am not sure if the problem is because I didn't translate the sql code properly or there is some other problem somewhere.
Your help is truly appreciated.
Please see entire code below and thanks in advance.

<%

Dim city_list(55)

city_list(5)="Chicago"
city_list(10)="Alpharetta"
city_list(15)="College Park"
city_list(20)="East Point"
city_list(25)="Fairburn"
city_list(30)="Hapeville"
city_list(35)="Mountain Park"
city_list(40)="Palmetto"
city_list(45)="Roswell"
city_list(50)="Union City"
city_list(55)="Fulton County"

'For i=0 to UBound(city_list)
'If i mod 5=0 then
'response.write city_list(i) & "<br>"
'End If
'Next

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=cat"

street = Request.form("street")

SQL = "select full_name, min(xmin(box3d(shape))), min(ymin(box3d(shape))), " _
& "max(xmax(box3d(shape))), max(ymax(box3d(shape))) " _
& "from vw where full_name like '"& street &"%' group by full_name "

Set cur = conn.Execute( SQL )

'response.write SQL
'response.end

cnt = 0
%>
<div align="center">
<font size="-1">Click on street name to locate on map.</font>
<table>
<tr>
<td>
<Font size=3 color=#330099><B>
Click below on address to display the map
</b></font>
</td>
</tr>

<%
Do Until cur.EOF
cnt = cnt + 1
iname = cur(0).Value
ileft = cur(1).Value - 250
ibottom = cur(2).Value - 250
iright = cur(3).Value + 250
itop = cur(4).Value + 250

if ileft <> -250 Then
psn = ileft & " " & ibottom & " " & iright & " " & itop
%>
<tr>
<td>
<a href="#" onClick="zoomToExtent('<%=psn%>');return false;">
<font size="-1"><%=iname%></font>
</a>
</td>
</tr>
<%
Else
%>
<tr>
<td><font size="-1"><%=iname%> (no coordinates)</font></td>
</tr>
<%
End If

cur.MoveNext
Loop
%>
</table>
<%
if cnt = 0 Then
%>
<Center><B>No results</center>
<%
End If
cur.Close()
conn.Close()
%>

By the way, this is separate from the help I received earlier like 2 days ago from the great ehorn.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-27 : 22:33:39

Helo again,

I am thinking the problem is with the SQL:

SQL = "select full_name, min(xmin(box3d(shape))), min(ymin(box3d(shape))), " _
& "max(xmax(box3d(shape))), max(ymax(box3d(shape))) " _
& "from vw where full_name like '"& street &"%' group by full_name "

I am not familiar with PostgreSQL syntax but the min(....) and max(...) stuff is not valid T-SQL for SQL Server. The where clause looks OK though.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-02-28 : 02:09:59

The min() and max() functions are fine. But xmax, ymax, and box3d are not standard. Are these custom functions that you have defined in SQL Server?

Also, your LIKE clause will only find streets starting with the street they entered. If you want streets containing the text the user entered, then you need a % at the beginning also: LIKE '%main%'

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

chinedu
Starting Member

8 Posts

Posted - 2004-02-28 : 07:01:28

Hello good people and again thanks for the response.

The sql syntax is postgres syntax which is fine because we are using postgres database.
So all these stuff - min() and max() functions are fine. But xmax, ymax, and box3d are fine because they postgres functions.
The concern I had was the whereclause as indicated earlier.
I am not familiar with postgres myself but there is someone who says he knows postgres who told me that the tilde (~) in postgres means substring and that the * is more the like statemenet in t-sql.
So combined you would get, according to him, this ~*.
Now, I am not sure if his explanation is correct.

As far as the % search is concerned, I want it the way I coded it because I want anything that begins with their input parameter to be displayed.
For instance, if I enter Chicago as input param, I will like anything that begins with Chicago to be displayed.
Also as explaine earlier, when I enter say Chicago on the search.asp page, and I do response.write SQL on pag2.asp, I see Chicago being passed as a value. So that is being passed correctly.
What I don't understand is why the results are not being displayed on the screen.

chinedu
Starting Member

8 Posts

Posted - 2004-02-28 : 10:31:24

Finally found out what this ~* means in postgres.
Please take a look.

http://www.postgresql.org/docs/7.4/static/functions-matching.html#FUNCTIONS-POSIX-TABLE

chinedu
Starting Member

8 Posts

Posted - 2004-03-01 : 10:59:19

I have resolved this problem.
It just occured to me that since I am still using postgres database, there is no reason to change to syntax from ~* to LIKE.
As soon as I changed it back, vavoom! It started working.
I want to thank all of you for your contributions.
I have long believed that whether you get the solution you are looking for or not, any contribution made by anyone increases your knowledge.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources