Solved

Paging ASP code for SQL DB error in strSQL

Posted on 2001-09-09
29
455 Views
Last Modified: 2008-03-17
I am trying to implement a paging code into my search results page and I keep getting this error when I click on the link to go to the next results page.

I can't figure out what's wrong with this statement.  I would appreciate feedback.  Thank you for your help.

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'ORDER'.
/idx/scripts/property_type.asp, line 39

This is my strSQL below:
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = " & Request.Form("prop_type") & "ORDER BY PRICE_CURRENT ASC, AREA ASC"





0
Comment
Question by:victoria
  • 11
  • 9
  • 3
  • +3
29 Comments
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6469099
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = '" & Request.Form("prop_type")
& "' ORDER BY PRICE_CURRENT ASC, AREA ASC"
0
 
LVL 5

Expert Comment

by:dgorin
ID: 6469111
One thing that could cause your error is if request.form("prop_type") is empty, or is an incorrect datatype for your database.  As thunderchicken pointed out, if it's a textual datatype field, you need to add ' around the value.  You may need to add some checks to determine the existence of the prop_type variable being returned by the form.
0
 

Author Comment

by:victoria
ID: 6469145
Thank you,
the string works, except that my first page displays the records requested but all the other pages are blank.
strange...
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6469169
Post your code so far so we make sure you are not missing anything, my guess is an error in the SQL statement
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
ID: 6470516
My blind guess is the variable "prop_type" needs to be posted in every trip which is probably not happening.

You will need to have a code on the following lines...

<Input Name="prop_type" Type="Hidden" Value="<%=Request.Form("prop_type")%>">

0
 

Author Comment

by:victoria
ID: 6470965
I get my results to work on the first page, but the second page and all the remaining 5 pages are blank.  
Also, when I search by All Property Types I get blank results page.  I would appreciate some feedback regarding my code.  Thank you much.

This is my form:

<form method="post" form action="propertytype.asp" >
        <p><font color="#414141" face="Futura Bk BT"><b>Property Type</b></font><br>
          <select name="type">
         <option value="">All Property Types
            <option value="1">Residential
            <option value="2">Condo
            <option value="3">Duplex
            <option value="6">Rental
            <option value="5">Lot
            <option value="4">Commercial Lease
            <option value="7">Commercial Sale
          </select>
          <br>
          <Input Name="type" INPUT TYPE="Image" src="../../../../idx/images/white_silver_search_property.gif" Value="<%=Request.Form("prop_type")%>" width="140" height="24">
          &nbsp; </p>
      </form>


This is the command in actionform:

If Request.Form("type") <> "" Then
     wc = "PROP_TYPE = " & Request.Form("type")
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = '" & Request.Form("type") & "' ORDER BY PRICE_CURRENT ASC, AREA ASC"
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6470984
What type of data is PROP_TYPE?  varchar?  int?

Assuming its numeric, try this

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = " & cint(Request.Form("type"))
& " ORDER BY PRICE_CURRENT ASC, AREA ASC"

0
 

Author Comment

by:victoria
ID: 6471251
I tried:
PROP_TYPE = " & cint(Request.Form("type")) & "

and I get the same results.  These are my latest changes to the code.  Yes, PROP_TYPE is a numeric data type.  Perhaps the problem is in the form?

This is my form:

<form method="post" form action="propertytype.asp" >
       <p><font color="#414141" face="Futura Bk BT"><b>Property Type</b></font><br>
         <select name="type">
        <option value="">All Property Types
           <option value="1">Residential
           <option value="2">Condo
           <option value="3">Duplex
           <option value="6">Rental
           <option value="5">Lot
           <option value="4">Commercial Lease
           <option value="7">Commercial Sale
         </select>
         <br>
         <Input Name="type" INPUT TYPE="Image" src="../../../../idx/images/white_silver_search_property.gif"
Value="<%= cint(Request.Form("type"))%>" width="140" height="24">
         &nbsp; </p>
     </form>


This is the command in actionform:

If cint(Request.Form("type")) <> "" Then
    wc = "PROP_TYPE = " & cint(Request.Form("type"))
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = " & cint(Request.Form("type"))
& " ORDER BY PRICE_CURRENT ASC, AREA ASC"
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6471298
What is the exact error you are getting, and try taking out the LIKE statement to make sure that is not the problem
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
ID: 6471423
There are couple of observations as follows:

01. Although it's not a severe error, try giving another name to drop-down. Such as <Select Name="Property". In short, try avoiding reserved words.

02. In following line check for appropriate value such as

If cInt(Request.Form("type")) <> 0 Then

OR

If Trim(Request.Form("type")) <> "" Then

Needless to mention, if you follow point 1 above, your control name will change in above code as well.

Yet another good debugging approach is to print the sql as
Response.Write strSQL and then run it in query analyser.
0
 

Author Comment

by:victoria
ID: 6472069
I tried to adopt some of your suggestions above and that's what I have now.  It does not change my results.
I don't get an error message.  The first page displays properly but the remaining pages are blank.

This is my form code:
 <form method="post" form action="propertytype.asp" >
        <p><font color="#414141" face="Futura Bk BT"><b>Property Type</b></font><br>
          <select name="category">
                 <option value="">All Property Types
            <option value="1">Residential
            <option value="2">Condo
            <option value="3">Duplex
            <option value="6">Rental
            <option value="5">Lot
            <option value="4">Commercial Lease
            <option value="7">Commercial Sale
          </select>
          <br>
          <Input Name="submit" INPUT TYPE="Image" src="file:///C|/idx/images/white_silver_search_property.gif" Value="<%=Trim(Request.Form("category"))%>"width="140" height="24">
          &nbsp; </p>
      </form>

This is my action form code:

If Trim(Request.Form("category")) <> "" Then
     wc = "PROP_TYPE = " & Trim(Request.For("category"))
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_TYPE = '" & Trim(Request.Form("category")) & "' ORDER BY PRICE_CURRENT ASC, AREA ASC"


Interesting thing is that when I change the strSQL to the one below, my paging works great and all the pages display properly, but it does not allow me to pull up different property categories, only residential because 1 stands for residential.

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_TYPE = 1 ORDER BY PRICE_CURRENT ASC, AREA ASC"
0
 
LVL 5

Expert Comment

by:raizon
ID: 6472238
Here is another way to do paging.

However the best way would be to do your paging in a Stored Procedure, that way only the page will be returned as the recordset and the SQL server will handle the paging rather then ADO on the webserver.

<%

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3

strSql = "Select * from tablename"

rstView = Server.CreateObject("ADODB.Recordset")
rstView.PageSize = 50
rstView.CacheSize = 1
rstView.CursorLocation = adUseClient
rstView.Open strSql, conn

If Not rstView.EOF Then                        
 If Len(Request("pagenum")) = 0 Then
   rstView.AbsolutePage = 1
 Else
   If CInt(Request("pagenum")) <= rstView.PageCount Then
     rstView.AbsolutePage = Request("pagenum")
   Else
     rstView.AbsolutePage = 1
   End If
 End If
 AbsolutePage = rstView.AbsolutePage
 PageCount = rstView.PageCount              
 
 If PageCount > 1 Then
  strRstNav = "<a href='search.asp?method=search&strType=" & strType & "&pagenum=1" & strSearchString
& "' class='searchnav'>First Record</a> | "
 
 If AbsolutePage > 1 Then
strRstNav = strRstNav & "<a href='search.asp?method=search&strType=" & strType & "&pagenum=" & AbsolutePage
- 1 & strSearchString & "' class='searchnav'>Previous Record</a> | "
 End If
 
 strRstNav = strRstNav & "<a href='search.asp?method=search&strType=" & strType & "&pagenum=" & AbsolutePage
+ 1 & strSearchString & "' class='searchnav'>Next Record</a> | "
                             
 strRstNav = strRstNav & "<a href='search.asp?method=search&strType=" & strType & "&pagenum=" & PageCount
& strSearchString & "' class='searchnav'>Last Record</a> | "

 End If
End If

'Print out your recordset
'Print out your recordset navigation
Response.Write strRstNav

%>
0
 

Author Comment

by:victoria
ID: 6472369
Raizon,
So you think that my problem is due to paging and not due to the following command:
PROP_TYPE = '" & Trim(Request.Form("category")) & "'

I am not a sophisticated programmer so I am not sure if I should start a new paging code.  I tried few and only the one Thunder gave me works well. I like the simplicity of it.  How would I go about it if I wanted to implement paging in a stored procedure?

Thank you for your comment and for the paging code.
If I can't resolve it then I may try your code.
At this point I am going to use a different field PROP_DESC(varchar) and see if that solves it.  There seems to be a problem in the field of PROP_TYPE (int).  
0
 
LVL 5

Expert Comment

by:raizon
ID: 6472404
I do think your problem is due to the command which you posted.

However you will get better performance by paging either through ADO and even better through a StoredProcedure.

As for your code I would set Request.Form("category") to a variable first.

In your code you have used 4 times.  Each time you do that it takes processing time to access the Request object and get that value out of the forms collection.


Try this and see if this helps.

Here we are setting a variable to the category and converting it to an Integer. (Your sql query that you said works uses an Integer).  

We are also removing the ' that surrounded your PROP_TYPE value in your query since it is of an int datatype you don't need the '

<%
intCat = Cint(Trim(Request.Form("category")))

If intCat <> "" Then
    wc = "PROP_TYPE = " & intCat
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_TYPE = " & intCat & " ORDER BY PRICE_CURRENT ASC, AREA ASC"

%>
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:victoria
ID: 6473054
I get the first page working fine, but when I click on the next page or any other page, it is blank.
when I go to detail page, it tells me that there is type mismatch.  I tried everything suggested above and I keep having the same problem so instead I am pasting my entire code below except for db access info.  I really appreciate all the help I can get on this because I've spent way too much time on this.  Everyone, I want to really thank you for all your feedback.
_________________________________________________

form:
  <form method="post" form action="propertytype.asp" >
        <p><font color="#414141" face="Futura Bk BT"><b>Property Type</b></font><br>
          <select name="category">
                    <option value="">All Property Types
            <option value="1">Residential
            <option value="2">Condo
            <option value="3">Duplex
            <option value="4">Rental
            <option value="5">Lot
            <option value="7">Commercial Lease
            <option value="8">Commercial Sale
          </select>
          <br>
          <Input Name="category" INPUT TYPE="Image" src="file:///C|/idx/images/white_silver_search_property.gif" width="140" height="24">
          &nbsp; </p>
      </form>
________________________________________________
results page:

<html><head>
<title>Property Type Search</title>
</head><body>

<%
intPage = Request("page")
If isNumeric(intPage) = False Or intPage < 1 Then
   intPage = 1
End If

'intCat = Cint(Trim(Request.Form("category")))

'If intCat <> "" Then
'   wc = "PROP_TYPE = " & intCat
'End If

'If Request.Form("category") <> "" Then
'      wc = "PROP_DESC LIKE '" & Request.Form("category")
'End If

If Request.Form("category") <> "" Then
      wc = "PROP_TYPE" & Request.Form("category")
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
'strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_TYPE = " & intCat & " ORDER BY PRICE_CURRENT ASC, AREA ASC"
'strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_DESC = '" & Request.Form("category") & "' ORDER BY PRICE_CURRENT ASC, AREA ASC"
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = '" & Request.Form("category") & "' ORDER BY PRICE_CURRENT ASC, AREA ASC"
strDSN = "Provider=SQLOLEDB; Data Source=DATAINFO; Initial Catalog=DBFILE; User ID=ID; Password=PASS"
rs.Open strSQL, strDSN, 1

If Not (rs.BOF Or rs.EOF) Then
   rs.PageSize = 5
   rs.AbsolutePage = intPage
   intRecCount = rs.PageSize
   intPageCount = rs.PageCount
   Response.Write "<p><b>Page " & intPage & " of " & intPageCount & "</b></p>"
 
 
 
Response.Write "<p><b>Page </b> "
For intNum = 1 To intPageCount
   Response.Write "<a href=propertytype.asp?page=" & intNum & ">" & intNum & "</a> "
Next

Response.Write "<p><b>Back or Next </b>"
If Cint(intPage) > 1 Then
   Response.Write "<a href=propertytype.asp?page=" & intPage - 1 & ">&lt;&lt;</a>"
Else
   Response.Write "&lt;&lt;"
End If

Response.Write "&nbsp;"

If Cint(intPage) < Cint(intPageCount) Then
   Response.Write "<a href=propertytype.asp?page=" & intPage + 1 & ">&gt;&gt;</a> "
Else
   Response.Write "&gt;&gt;"
End If
      Do While Not rs.EOF And intRecCount > 0
   %>
   
 <table width="75%" border="0" color="#00509F">
  <tr>
  <%
  If rs.Fields("PHOTO_NO").Value > "0" Then
  %>  
    <td rowspan="3" width="15%"><a href="http://www.sarasotamls.com/report.asp"><IMG SRC="http://dev.priv.sar_fl.xmlsweb.com//Images/MLS_Photos/<%= rs.Fields("MLS_NUMBER") %>.1.jpg" width="80" height="80"></a>
  <% ElseIf rs.Fields("PHOTO_NO").Value = "0" Then %>
          <td rowspan="3" width="15%"><a href="http://www.sarasotamls.com/report.asp"><IMG SRC="http://www.sarasotamls.com//Images/MLS_Photos/NoPhoto.1.jpg" width="80" height="80"></a>
            
      <% End If %>

      </td>
    <td width="46%"><b>MLS Number: </b><a href="detail.asp?MLS_NUMBER=" & rs("MLS_NUMBER") & ">"</a> <%= rs("MLS_NUMBER") %>
      </td>
    <td width="38%"><b>Price: $</b> <%= rs.Fields("PRICE_CURRENT") %></td>
    <td rowspan="3" width="1%"></td>
  </tr>
  <tr>
    <td width="46%"><b>Bed/Bath: </b> <%= rs.Fields("BDRMS") %><b>/</b><%= rs.Fields("BATHS_TOT") %>
    </td>
    <td width="38%"><b>Property Type: </b> <%= rs.Fields("PROP_DESC") %></td>
  </tr>
  <tr>
    <td width="46%"> <b>SqFt: </b> <%= rs.Fields("SQFT") %> </td>
    <td width="38%"><b>Subdivision: </b> <%= rs.Fields("SUB_DIV") %></td>
  </tr>
</table>
   <hr>
 
<%
    intRecCount = intRecCount - 1
    rs.MoveNext
   Loop
End If

rs.Close
Set rs = Nothing

%>

</body></html>
__________________________________________________
detail page:
<%@ Language=VBScript %>
<% Option Explicit %>
<!--include virtual="/adovbs.inc"-->
<html>
<head>
<title>Detail</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<%
Dim objConn, wc, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString =("Provider=SQLOLEDB; Data Source=DATAINFO; Initial Catalog=DBFILE; User ID=ID Password=PASS")
objConn.Open

dim strSQL
strSQL = "Select * from detail_p0 where MLS_NUMBER=" & request.querystring("MLS_NUMBER") & ""
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open, strSQL, objConn

'Response.Write strSQL
'Response.Flush '(so the error is after, this will still display)
'Response.write Request.querystring("Listing_ID")

'We are displaying results of listings with requested Area
'Response.Write "<B>Property Detail</B><BR>"
While Not objRS.EOF
%>
<table align="center" width="60%" border="0">
  <tr>
    <td width="32%" height="26">&nbsp;</td>
    <td width="26%" height="26">&nbsp;</td>
    <td width="21%" height="26">&nbsp;</td>
    <td width="21%" height="26"><a href="http://localhost/idx/forms/f_idx_home.asp"><img src="../../../../idx/images/new_search_button.gif"></a>&nbsp;</td>
  </tr>
  <tr>
    <td align="center" bgcolor="E6E6FA" colspan="4"> <b><font face="Futura Bk BT" size="3">Property
      Detail</font></b></td>
  </tr>
</table>
<table align="center" width="60%" border="0">
  <tr>
    <td height="23" width="19%"><font size="2"><b>Price: $</b> </font></td>
    <td height="23" width="30%"><font size="2"><%= objRS.Fields("PRICE_CURRENT") %>&nbsp;</font></td>
    <td width="22%"><font size="2"></font></td>
    <td width="29%"><font size="2"></font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"><b>MLS Number: </b>&nbsp;</font></td>
    <td width="30%"><font size="2"><%= objRS.Fields("MLS_NUMBER") %>&nbsp;</font></td>
    <td width="22%"><font size="2"></font></td>
    <td width="29%"><font size="2"></font></td>
  </tr>
  <tr>
    <td colspan="4"><font size="2"><a href="http://www.sarasotamls.com/report.asp"><img src="http://dev.priv.sar_fl.xmlsweb.com//Images/MLS_Photos/<%= ObjRS.Fields("MLS_NUMBER") %>.1.jpg" width="320" height="240"></a>
      <%
  If ObjRS.Fields("PHOTO_NO").Value > "0" Then
  %>
      <% ElseIf ObjRS.Fields("PHOTO_NO").Value = "0" Then %>
      <a href="http://www.sarasotamls.com/report.asp"><img src="http://www.sarasotamls.com//Images/MLS_Photos/NoPhoto.1.jpg" width="100" height="100"></a>
      <% End If %>
      &nbsp;</font></td>
  </tr>
  <tr>
    <td height="22" width="19%"><font size="2"><b>Property Type: </b> </font></td>
    <td width="30%" height="22"><font size="2"><%= objRS.Fields("PROP_DESC") %></font></td>
    <td width="22%"><font size="2"><b>Waterfront: </b> &nbsp;</font></td>
    <td width="29%"><font size="2"><%= objRS.Fields("WATERFRONT_YN") %> &nbsp;</font></td>
  </tr>
  <tr>
    <td width="19%" height="23"><font size="2"><b>City, State: </b> &nbsp;</font></td>
    <td width="30%" height="23"><font size="2"><%= objRS.Fields("CITY") %><b>,
      </b><%= objRS.Fields("STATE") %>&nbsp;</font></td>
    <td width="22%" height="23"><font size="2"><b>Waterfront Type: </b> &nbsp;</font></td>
    <td width="29%" height="23"><font size="2"><%= objRS.Fields("WATERFRONT_FEATS") %>
      &nbsp;</font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"><b>Zip: </b> &nbsp;</font></td>
    <td width="30%"><font size="2"><%= objRS.Fields("ZIP") %>&nbsp;</font></td>
    <td width="22%" height="23"><font size="2"><b>Pool: </b> &nbsp;</font></td>
    <td width="29%" height="23"><font size="2"><%= objRS.Fields("POOL_YN") %>
      &nbsp;</font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"><b>Subdivision: </b>&nbsp;</font></td>
    <td width="30%"><font size="2"><%= objRS.Fields("SUB_DIV") %>&nbsp;</font></td>
    <td width="22%"><font size="2"><b>SqFt: </b>&nbsp;</font></td>
    <td width="29%"><font size="2"><%= objRS.Fields("SQFT") %>&nbsp;</font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"><b>Zone: </b>&nbsp;</font></td>
    <td width="30%"><font size="2"><%= objRS.Fields("ZONE") %>&nbsp;</font></td>
    <td width="22%"><font size="2"><b>Acres: </b>&nbsp;</font></td>
    <td width="29%"><font size="2"><%= objRS.Fields("ACRES") %>&nbsp;</font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"><b>Year Built: </b>&nbsp;</font></td>
    <td width="30%"><font size="2"><%= objRS.Fields("YEAR_BUILT") %>&nbsp;</font></td>
    <td width="22%"><font size="2"><b>Lot Size: </b>&nbsp;</font></td>
    <td width="29%"><font size="2"><%= objRS.Fields("LOT_SIZE") %>&nbsp;</font></td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td colspan="3">&nbsp;</td>
  </tr>
  <tr>
    <td width="19%"><b><font size="2">Interior: </font></b></td>
    <td colspan="3"><font size="2"><%= objRS.Fields("POOL_YN") %>&nbsp;</font></td>
  </tr>
  <tr>
    <td valign="top" width="19%"><font size="2"><b>Exterior: </b>&nbsp;</font></td>
    <td colspan="3"><font size="2"><%= objRS.Fields("POOL_YN") %>&nbsp;</font></td>
  </tr>
  <tr>
    <td valign="top" width="19%"><font size="2"><b>Schools:</b>&nbsp;</font></td>
    <td colspan="2"><font size="2"><b>Elementary: </b> <%= objRS.Fields("SCHOOLS_E") %><br>
      <b>Middle: </b> <%= objRS.Fields("SCHOOLS_M") %><br>
      <b>High: </b> <%= objRS.Fields("SCHOOLS_H") %>&nbsp;</font></td>
    <td width="29%"><font size="2"></font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"></font></td>
    <td width="30%"><font size="2"></font></td>
    <td width="22%"><font size="2"></font></td>
    <td width="29%"><font size="2"></font></td>
  </tr>
  <tr>
    <td width="19%"><font size="2"><b>Courtesy of: </b>&nbsp;</font></td>
    <td width="30%"><font size="2"><%= objRS.Fields("L_OFF_NAME") %>&nbsp;</font></td>
    <td width="22%"><font size="2"></font></td>
    <td width="29%"><font size="2"></font></td>
  </tr>
  <tr>
    <td valign="top" bgcolor="E6E6FA" width="19%"><font size="2"><b>Disclaimer:
      </b>&nbsp;</font></td>
    <td bgcolor="E6E6FA" colspan="3"><font size="2">All information is deemed
      reliable but not guaranteed and should be verified by personal inspection
      or with the appropriate professionals. The materials contained within this
      page may not be reproduced without the express written consent of Sarasota
      Association of REALTORS&reg;. &copy;Copyright 2001 Sarasota Association
      of REALTORS&reg; Multiple Listing Service. All rights reserved.</font></td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td width="30%">&nbsp;</td>
    <td width="22%">&nbsp;</td>
    <td width="29%">&nbsp;</td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td width="30%">&nbsp;</td>
    <td width="22%">&nbsp;</td>
    <td width="29%">&nbsp;</td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td width="30%">&nbsp;</td>
    <td width="22%">&nbsp;</td>
    <td width="29%">&nbsp;</td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td width="30%">&nbsp;</td>
    <td width="22%">&nbsp;</td>
    <td width="29%">&nbsp;</td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td width="30%">&nbsp;</td>
    <td width="22%">&nbsp;</td>
    <td width="29%">&nbsp;</td>
  </tr>
  <tr>
    <td width="19%">&nbsp;</td>
    <td width="30%">&nbsp;</td>
    <td width="22%">&nbsp;</td>
    <td width="29%">&nbsp;</td>
  </tr>
</table>
<p>&nbsp;</p>
<table align="center" width="425">
  <tr>
    <td align="left" valign="top"> </td>
  </tr>
</table>

<%

'Response.Write objRS.Fields("MLS_NUMBER") & objRS.Fields("BDRMS") & objRS.Fields("BATHS_TOT") & objRS.Fields("SQFT") & objRS.Fields("SUB_DIV") & "<BR>"

'Move on to the next property
objRS.MoveNext
Wend





'Clean up our ADO objects
objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing
%>

</body>
</html>

__________________________________________
I realize this is a lot to take a look at.
I am sure that you'll know where to look for the problem.
You've all been so very helpful spending a lot of time on this, I really appreciate your time and expertise.
I've learned a lot, but I haven't resolved the initial question above and I am very eager to put this to rest.

Thank you again for your help.
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6474332
Change
<a href="detail.asp?MLS_NUMBER=" & rs("MLS_NUMBER") & ">"</a>

to


<a href="detail.asp?MLS_NUMBER=<%=rs("MLS_NUMBER")%>"><%= rs("MLS_NUMBER") %></a> <%= rs("MLS_NUMBER") %>
0
 
LVL 6

Expert Comment

by:Nitin Sontakke
ID: 6474409
Few more observations:

01. In following line why is "Input" occuring twice without completing the first Input Tag?

<Input Name="category" INPUT TYPE="Image" src="file:///C|/idx/images/white_silver_search_property.gif"
width="140" height="24">

02. It is recommended that you use relative file path to your images. The code above will run only on YOUR box.

03. You have already used Name="Category" for the drop-down. So use different one for the Input Type=Image. Please use unique names to all your controls, images, etc.

0
 

Author Comment

by:victoria
ID: 6475280
Hi,
I haven't gotten my All property Types field to work in the form document and my pages besides the first page upload blank without an error message.
I know that the problem lies in the strSQL command of the results page:
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS LIKE 'A' AND PROP_TYPE = '" & Request.Form("category")
& "' ORDER BY PRICE_CURRENT ASC, AREA ASC"

In fact the problem is in the communication of this command string with the form and how it returns the results page:
'" & Request.Form("category") & "'

I appreciate your feedback regarding this question tremendously as I am working under a very tight deadline.
category refers to PROP_TYPE field which is an integer.
The values in this field are: 1, 2, 3, 4, 5, 6, 7 and 8.
and each stand for different property type.

Thank you much.

0
 

Expert Comment

by:Raniamea
ID: 6475401
your problem is that u are using
request.form and in the second page there is no form data posted so this value comes back empty
u should get the value of this form field in the first page and save it in a session variable in the first page only then use this session variable in the sql statement...
tell me if u need help with the code
0
 

Expert Comment

by:Raniamea
ID: 6475416
your problem is that u are using
request.form and in the second page there is no form data posted so this value comes back empty
u should get the value of this form field in the first page and save it in a session variable in the first page only then use this session variable in the sql statement...
tell me if u need help with the code
0
 

Author Comment

by:victoria
ID: 6475491
Raniamea,
Yes, could you give me a hand with the code.
How do i do that?
Thank you so much.
0
 

Author Comment

by:victoria
ID: 6475516
Thunder,
I tried to email you but I got an undeliverable message.  
I am not sure, looks like your server does not accept messages past certain time in the evening.  This happened to me before also.  I am not able to reach you, so please comment on the response I wrote at 2:16pm.
You help is greatly appreciated.
victoria
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6475519
Do you have AIM or ICQ?

I can help you this evening if you need it
0
 

Expert Comment

by:Raniamea
ID: 6475576
If Request.Form("category") <> "" Then
     Session("category")=Request.Form("category")
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_TYPE = " & Session("category") & " ORDER BY
PRICE_CURRENT ASC, AREA ASC"
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6475627
Session variables are not necessary in this, it requires more memory, thus system resources.
0
 

Author Comment

by:victoria
ID: 6476675

form:
________________________________________________
  <form method="post" form action="propertytype.asp" >
        <p><font color="#414141" face="Futura Bk BT"><b>Property Type</b></font><br>
          <select name="category">
                 <option value="">All Property Types
            <option value="1">Residential
            <option value="2">Condo
            <option value="3">Duplex
            <option value="4">Rental
            <option value="5">Lot
            <option value="7">Commercial Lease
            <option value="8">Commercial Sale
          </select>
          <br>
          <INPUT TYPE="Image" src="file:///C|/idx/images/white_silver_search_property.gif <%='" & intCat & "'%>" width="140" height="24">
          &nbsp; </p>
      </form>

results page:
_____________________________________________________

intCat = Trim(Request.Form("category"))

If intCat <> "" Then
   wc = "PROP_TYPE = " & intCat
End If

Set rs = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT TOP 50 * FROM detail_p0 WHERE STATUS = 'A' AND PROP_TYPE = '" & intCat & "' ORDER BY PRICE_CURRENT ASC, AREA ASC"

________________________________________________________
I still get blank pages without an error message except for the first page which works beautifully.  When I request All Property Types from the drop down menu, all my results pages are blank.

Some of the problem lies in my input link and how it communicates with my strSQL statement to pull up all pages:

<INPUT TYPE="Image" src="file:///C|/idx/images/white_silver_search_property.gif <%='" & intCat & "'%>" width="140" height="24">

Could anyone please take a look at my input link and let me know how I can correct it.  I would appreciate if you could copy the above script and make corrections on the lines provided.  Thank you much.

0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6476697
Let's start from scratch, forget the image for now, we can fix that later, post this code

<form method="post" action="propertytype.asp">
       <p><font color="#414141" face="Futura Bk BT"><b>Property Type</b></font><br>
         <select name="category">
                <option value="">All Property Types
           <option value="1">Residential
           <option value="2">Condo
           <option value="3">Duplex
           <option value="4">Rental
           <option value="5">Lot
           <option value="7">Commercial Lease
           <option value="8">Commercial Sale
         </select>
         <br>
         <INPUT TYPE="Submit" value="Submit">
         &nbsp; </p>
     </form>


This works fine
0
 
LVL 11

Accepted Solution

by:
thunderchicken earned 50 total points
ID: 6482926
Victoria, if I helped you out, can you please award points?

Thanks
0
 

Author Comment

by:victoria
ID: 6502440
Thunder,
Thank you so much for all your help in answering this question.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now