Solved

Adding additional options for searches creating errors

Posted on 2008-10-25
12
193 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 500 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 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