Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding additional options for searches creating errors

Posted on 2008-10-25
12
Medium Priority
?
206 Views
Last Modified: 2010-03-20
Priot to making suggestions I urge you to read http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23833298.html which was part one of my problem.

The Issue now is that I am receiving the above error when I add on to the code below. Microsoft notes only that it has to do with Parameter Mapping if this helps.

<%
Dim rsListings__varPCity
rsListings__varPCity = "*"
If (Request("ResiCity") <> "") Then
  rsListings__varPCity = Request("ResiCity")
End If
%>
<%
Dim rsListings
Dim rsListings_cmd
Dim rsListings_numRows
 
Set rsListings_cmd = Server.CreateObject ("ADODB.Command")
rsListings_cmd.ActiveConnection = MM_HomeownerServices_STRING
rsListings_cmd.CommandText = "SELECT * FROM TListings WHERE PropertyCity = IIF(? = '*', PropertyCity, ?)"
rsListings_cmd.Prepared = true
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param1", 200, 1, 255, rsListings__varPCity) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param2", 200, 1, 255, rsListings__varPCity) ' adVarChar
 
Set rsListings = rsListings_cmd.Execute
rsListings_numRows = 0
%>

This works well, but I need to add to this a parameter for counties, like the cities. As well as one for price and for levels. Please see the link and you will have a greater understanding.

When I do this in Dreamweaver, it overwrites the code for parameter 2 in the selct statement. When I put it back manual and change the parameter of the new line to 3, I get this error.

All help appreciated
0
Comment
Question by:GuitarFingers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 22803455
Can you give us the code behind for the query without the City or County?  It will help to know what dreamweaver generates for those numeric fields like Bedrooms and Levels etc.
0
 
LVL 9

Author Comment

by:GuitarFingers
ID: 22803816
<%
Dim rsListings__varPCity
rsListings__varPCity = "*"
If (Request("ResiCity") <> "") Then
  rsListings__varPCity = Request("ResiCity")
End If
%>
<%
Dim rsListings__varPrice
rsListings__varPrice = "1000000"
If (Request("ListingPrice") <> "") Then
  rsListings__varPrice = Request("ListingPrice")
End If
%>
<%
Dim rsListings__varBaths
rsListings__varBaths = "0"
If (Request("Bathrooms") <> "") Then
  rsListings__varBaths = Request("Bathrooms")
End If
%>
<%
Dim rsListings__varBeds
rsListings__varBeds = "0"
If (Request("Bedrooms') <> "") Then
  rsListings__varBeds = Request("Bedrooms')
End If
%>
<%
Dim rsListings__varLevels
rsListings__varLevels = "1"
If (Request("Levels") <> "") Then
  rsListings__varLevels = Request("Levels")
End If
%>
<%
Dim rsListings
Dim rsListings_cmd
Dim rsListings_numRows

Set rsListings_cmd = Server.CreateObject ("ADODB.Command")
rsListings_cmd.ActiveConnection = MM_HomeownerServices_STRING
rsListings_cmd.CommandText = "SELECT * FROM TListings WHERE PropertyCity = IIF(? = '*', PropertyCity, varPCity) AND ListingPrice<= ? AND Bathrooms>= ? AND Bedrooms>= ? AND Levels = ?"
rsListings_cmd.Prepared = true
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param1", 200, 1, 255, rsListings__varPCity) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param2", 5, 1, -1, rsListings__varPrice) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param3", 5, 1, -1, rsListings__varBaths) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param4", 5, 1, -1, rsListings__varBeds) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param5", 5, 1, -1, rsListings__varLevels) ' adDouble

Set rsListings = rsListings_cmd.Execute
rsListings_numRows = 0
%>
0
 
LVL 18

Accepted Solution

by:
mdougan earned 2000 total points
ID: 22803899
OK, hopefully, this will do it.  I guessed that your new form field was named ResiCounty and that your database field was PropertyCounty.  If not, then change those references as necessary.  When dreamweaver generates your code, it only replaces the first var in the IIF statement with a question mark, you then have to manually change the second reference to a question mark.  Then, you have to make sure that you double the number of Parameter.Appends for any of these with an IIF statement.

The way it works is that at some point, each question mark is replaced by the next parameter, so, the order of the Parameter Appends should be in the exact order that you want them to replace the question marks.
<%
Dim rsListings__varPCity
rsListings__varPCity = "*"
If (Request("ResiCity") <> "") Then 
  rsListings__varPCity = Request("ResiCity")
End If
%>
<%
Dim rsListings__varCounty
rsListings__varCounty = "*"
If (Request("ResiCounty") <> "") Then 
  rsListings__varCounty = Request("ResiCounty")
End If
%>
<%
Dim rsListings__varPrice
rsListings__varPrice = "1000000"
If (Request("ListingPrice") <> "") Then 
  rsListings__varPrice = Request("ListingPrice")
End If
%>
<%
Dim rsListings__varBaths
rsListings__varBaths = "0"
If (Request("Bathrooms") <> "") Then 
  rsListings__varBaths = Request("Bathrooms")
End If
%>
<%
Dim rsListings__varBeds
rsListings__varBeds = "0"
If (Request("Bedrooms') <> "") Then 
  rsListings__varBeds = Request("Bedrooms')
End If
%>
<%
Dim rsListings__varLevels
rsListings__varLevels = "1"
If (Request("Levels") <> "") Then 
  rsListings__varLevels = Request("Levels")
End If
%>
<%
Dim rsListings
Dim rsListings_cmd
Dim rsListings_numRows
 
Set rsListings_cmd = Server.CreateObject ("ADODB.Command")
rsListings_cmd.ActiveConnection = MM_HomeownerServices_STRING
rsListings_cmd.CommandText = "SELECT * FROM TListings WHERE PropertyCity = IIF(? = '*', PropertyCity, ?) AND PropertyCounty = IIF(? = '*', PropertyCounty, ?) AND ListingPrice<= ? AND Bathrooms>= ? AND Bedrooms>= ? AND Levels = ?" 
rsListings_cmd.Prepared = true
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param1", 200, 1, 255, rsListings__varPCity) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param2", 200, 1, 255, rsListings__varPCity) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param3", 200, 1, 255, rsListings__varCounty) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param4", 200, 1, 255, rsListings__varCounty) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param5", 5, 1, -1, rsListings__varPrice) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param6", 5, 1, -1, rsListings__varBaths) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param7", 5, 1, -1, rsListings__varBeds) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param8", 5, 1, -1, rsListings__varLevels) ' adDouble
 
Set rsListings = rsListings_cmd.Execute
rsListings_numRows = 0
%>

Open in new window

0
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 9

Author Comment

by:GuitarFingers
ID: 22806887
mdougan,

Aside from a couple small matters (line 32 quotation marks, and price field) this works flawless! I also so much appreciate the explanations along with the code. Now... using your logic, I included one more field for a subtype. For example residential could be a house, condo or mobile home.  I have the new code attached.

The odd thing is, I get no errors and THINK I have done everything per example, but I only get listings to show up if there is no selection, or if the selection is made for house. I have checked the database and there are in fact condos and mobile homes. I also checked to be certain about spellings and extra spaces. I figure I have done something wrong somewhere.

My search page is http://www.nky-mls.com/listings-most-recent.asp through the 'residential search' accordian on the left.

Thank You


<%
Dim rsListings__varPCity
rsListings__varPCity = "*"
If (Request("ResiCity") <> "") Then 
  rsListings__varPCity = Request("ResiCity")
End If
%>
<%
Dim rsListings__varCounty
rsListings__varCounty = "*"
If (Request("ResiCounty") <> "") Then 
  rsListings__varCounty = Request("ResiCounty")
End If
%>
<%
Dim rsListings__varResiType
rsListings__varResiType = "*"
If (Request("ResiType") <> "") Then 
  rsListings__varResiType = Request("ResiType")
End If
%>
<%
Dim rsListings__varPrice
rsListings__varPrice = "1000000"
If (Request("ResiPrice") <> "") Then 
  rsListings__varPrice = Request("ResiPrice")
End If
%>
<%
Dim rsListings__varBaths
rsListings__varBaths = "0"
If (Request("Bathrooms") <> "") Then 
  rsListings__varBaths = Request("Bathrooms")
End If
%>
<%
Dim rsListings__varBeds
rsListings__varBeds = "0"
If (Request("Bedrooms") <> "") Then 
  rsListings__varBeds = Request("Bedrooms")
End If
%>
<%
Dim rsListings__varLevels
rsListings__varLevels = "1"
If (Request("Levels") <> "") Then 
  rsListings__varLevels = Request("Levels")
End If
%>
<%
Dim rsListings
Dim rsListings_cmd
Dim rsListings_numRows
 
Set rsListings_cmd = Server.CreateObject ("ADODB.Command")
rsListings_cmd.ActiveConnection = MM_HomeownerServices_STRING
rsListings_cmd.CommandText = "SELECT * FROM TListings WHERE PropertyCity = IIF(? = '*', PropertyCity, ?) AND PropertyCounty = IIF(? = '*', PropertyCounty, ?) AND Subtype = IIF(? = '*', Subtype, ?) AND ListingPrice<= ? AND Bathrooms>= ? AND Bedrooms>= ? AND Levels = ?" 
rsListings_cmd.Prepared = true
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param1", 200, 1, 255, rsListings__varPCity) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param2", 200, 1, 255, rsListings__varPCity) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param3", 200, 1, 255, rsListings__varCounty) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param4", 200, 1, 255, rsListings__varCounty) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param5", 200, 1, 255, rsListings__varResiType) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param6", 200, 1, 255, rsListings__varResiType) ' adVarChar
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param7", 5, 1, -1, rsListings__varPrice) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param8", 5, 1, -1, rsListings__varBaths) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param9", 5, 1, -1, rsListings__varBeds) ' adDouble
rsListings_cmd.Parameters.Append rsListings_cmd.CreateParameter("param10", 5, 1, -1, rsListings__varLevels) ' adDouble
 
Set rsListings = rsListings_cmd.Execute
rsListings_numRows = 0
%>

Open in new window

0
 
LVL 18

Expert Comment

by:mdougan
ID: 22807484
I don't see anything wrong in your code.  How is SubType defined in the database?  It is possible that SubType is some sort of 2 character code like CO for co-op, or SF for single family, if so, we'd have to be sure that what gets passed along from the form is the code, not the text description.  

For City, you'd mentioned adding a blank entry, or maybe an entry with an asterisk in the database, did you do the same for Subtypes?

Your search page is pretty cool, but all of the listings I saw were for houses... any idea of a combination of inputs I can use to see a condo?
0
 
LVL 9

Author Comment

by:GuitarFingers
ID: 22807626
Thanks fot the reply, Im going to dig in to see about the items you mention. You 'should' be able to get a condo by selecting nothing but the property type condo. Same for mobile home. Works for house.
0
 
LVL 9

Author Comment

by:GuitarFingers
ID: 22810037
Hey There..!!

I am at a total loss here for this one. I double checked the field name for the Request(""), double checked the default value *. Went back in to the table and retyped the Condo and Mobile home so no spaces. Thought I might have had it when I found out the field were set to 'memo' instead of 'text', but when I changed that, it still doesn't produce any results for Mobile Home or Condo. I even opened it up and ran the query in Access directly and it does work there.

Im stumped
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22810853
I went over and looked at the source for your search form and saw a few inconsistencies between the three drop-down select lists.  The first one is wrapped in a label tag...

<td>City</td>
                <td><label>
                    <select name="ResiCity" class="SelectCityDropdown" id="ResiCity">

The second one is not wrapped in a label tag, and also has the same class of SelectCityDropdown

<td>County</td>
                <td><select name="ResiCounty" class="SelectCityDropdown" id="ResiCounty">

The third one is wrapped in a label tag, and has a different class

<td><label>
                    <select name="ResiType" class="textSearchTextFields" id="ResiType">

So, maybe it could be related to that?

Can you give me all of the criteria for finding one specific Condo and one specific Mobile Home, because none of the searches I've tried, even leaving Type blank, have brought back anything other than houses.

0
 
LVL 18

Expert Comment

by:mdougan
ID: 22810871
also, the number 255 in the CreateParameter field is specifying the length of the VarChar parameter... generally, the length of the parameter should match the length of the field in the database.  How are all three fields defined in the database... you said you set them all to TEXT, and the default for text is usually 50 for length... max is 255.  It probably shouldn't matter, but check to see if there are any differences there.  Also, check to see if the Type allows nulls and if there are any records with NULL values in that column.  Grasping at straws, but just trying to eliminate all possibilities.

Cheers!
0
 
LVL 9

Author Comment

by:GuitarFingers
ID: 22844966
mdougan,

haven't forgot you my friend, they made me busy on something else. Will be back to this in a couple of days. Thanks
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22845560
No worries, cheers!
0
 
LVL 9

Author Comment

by:GuitarFingers
ID: 23001824
mdougan....

They have kept me so busy I have still not seen the time to get to this. I am sure the error is in my coding and as you have been such a help, here are the points. Please look for future posts, and thanks so much for your time.
0

Featured Post

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

730 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