Solved

Adding additional options for searches creating errors

Posted on 2008-10-25
12
183 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

23 Experts available now in Live!

Get 1:1 Help Now