Hi,

How can I filter the data - with the Drop Down Menu - eg. East London, London Wide.
Database is MS Access with Dreamweaver MX using ASP.

Link exapmle: Quick Finder: www.docklandsrealestates.com/Property4me/london_lets.asp

I am not using any GO button, just wanted to bring the data automatic in the same form.

Please look forward from any friend with advice.

Thanks

Babu

Edited by - babui on 14 Feb 2005 19:32:48

Filter the recordset based on the value selected in the drop down menu - but give it a default value (a wildcard) that will select all records when the page is 1st loaded

Is there a specific reason why you don't want a submit / go button? From a usability standpoint the Go button makes a lot of sense

Live the life you love
Love the life you live
~ ~ ~ ~ ~ ~ ~
<b>Simon Martin</b> - <i>DMXzone Manager</i>
<font size=1>[ Dreamweaver MX/MX2004 | ASP | SQL | XHTML/CSS | Web Accessibility ] </font id=size1>

Hi Simon

Thanks for the reply, Waiting from afternoon to get a response on that, because I never did...this kind of menu before.
Go Button, its not important actually, I was thinking that when client will change that menu, effect will be instance..But I can still use the go button, doesn't matter realy.

But the question is - How..I can make that.
I have recordset which call rsfinder -
But don't know what code should be here.....

Filter will be in the same form, not in the another page.....here is the problem for me, I don't know how filter it in the same page.....................

Please look forward from you.

Thanks again

Babu

Edited by - babui on 14 Feb 2005 19:34:24

Hi

I need to know......how to filter on the same form...and what should be the code..

Is there any example I can search........Please advice..

Thanks

Babu

Edited by - babui on 14 Feb 2005 19:42:52

When you build your recordset - if you do
SELECT *
FROM myTable
to populate the main page - then it will get all records from that table.

In the recordset builder click on the Advanced button and add a WHERE clause
something like
WHERE regionID = varRegionID (assuming that regionID represents the PK for the region eg East London, London Wide)
You then need to add that variable - so click on the + symbol
Name the variable as in the WHERE clause (varRegionID) give it a default value '%' (everything) and a runtime value request.form("cmbRegion" so that it picks up the value from the combo box when submitted.

Now when the page loads up it will populate with everything, but the user can then filter the results so that it only shows those records for the chosen region. Because we have not set the ACTION for the form the combo box will submit the data to the same page

Live the life you love
Love the life you live
~ ~ ~ ~ ~ ~ ~
<b>Simon Martin</b> - <i>DMXzone Manager</i>
<font size=1>[ Dreamweaver MX/MX2004 | ASP | SQL | XHTML/CSS | Web Accessibility ] </font id=size1>

Hi Simon
Thanks for the reply to my post. I was away for a short holiday, couldn't check the post. Sorry for that. Today I am trying to configure according your advice, there is a little bit error I am getting, like:
Syntex Error.

Here I should mention that - Region is my culum name of the area and I am trying to filter with this field.

So when I am working with quary

WHERE RegionID = varRegionID

Variable = varRegionID
Default Value = %
Run-time value = Request.Form("cmbRegion" [Note: This is the Combo Box Name & Form name is Form])


The error: Syntex error in quary expression 'regionID=%'

Please could you tell me where is the problem here?

My default Primary ID is 'PropertyID'

Look forward from you.

Babu

Edited by - babui on 21 Feb 2005 16:31:21

Just to check, are you trying to filter one list/menu based on the value selected in another on the page without it reloading?

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Vince,

Thanks to look at the post, well basicaly I wanted to use a drop down menu on the top of the page - Region,

By default page loads all the records together, what I want is Selecting Region Drop Down menu filter the page and get the result.

You can view the example here: www.docklandsrealestates.com/Property4me/london_lets.asp

I am bit confuse here, I have got the recordset used already to bring the default data, for the drop down menu do I have to make another recordset or use with the existing one? If so, then How can I filter the records by Region.

Look forward from you.

Babu

Hi
Well all the above advice helps me a lot to understand, one more advice please -

How about this - First page (London_lets.asp) default loaded with all the records, when drop down will select the go to the next page and filter? Is the logic work like that?

Please advice.

Babu

Hi, ok I think I have got it....

When you filter the recordset with the value from the dropdown menu you have to use the URL....(you should do this as this is a search function and it will allow the user to save to favorites).

So, ensure that your dropdown menu is inside of a form and set the action to the current page (london_lets.asp ) and set the method to get...

Now, in the Runtime value of your recordset add the following:

Request.Querystring("your_dropdown_menu_name"

And the recordset will be filtered on what ever is in the URL querystring. If nothing is present, it will return everything as you have specified % as the default value.



Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Just spotted something else.....

When ysing the wildcard % you can only use that with text, it will not work with a numerical value....

In your recordset to show all properties you will need to link to the region table to get the region name:

e.g.

Select property_table.property_name, region_table.region_name
from property_table, region_table
where property_table.region_id = region_table.region_id and region_table.region_name LIKE 'varRegion'

Name: varRegion
Default Value: %
Runtime Value: Request.Querystring("your_dropdown_list_name"

Sorry for the confusion...should have read all of your post first!


Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Vince

Thanks again, OK done the first bit using GET method, its inside form as well.

Runtime value is Request.Querystring("cmbRegion"

Now the quary:

SELECT *
FROM myLet
WHERE Region = varRegion
ORDER BY Per_week ASC

???

Is that right, getting Syntex Error.

Look forward from you.

Babu

Edited by - babui on 21 Feb 2005 18:58:02

There is only one table containing all the fields

No saperate table. Culum Name is 'Region'

Babu

OK, making another table now for Region

Babu

Hi Vince

Do I have make any relationship between two table:

Table Name: myLet + Let_Region



Babu

Hi Vince

I have created another table name 'Let_Region'

So now the quary is like

SELECT myLet.Status, myLet.Region, myLet.Title, myLet.Short_Description, myLet.Type, myLet.Furnished, myLet.Bedrooms, myLet.Per_week, myLet.Property_ID, Let_Region.Region_Name
FROM myLet, Let_Region
WHERE myLet.Region_id=Let_Region.Region_id and Let_Region.Region_Name LIKE 'varRegion'
ORDER BY Per_week ASC

Variable: varRegion
Default Value: %
Runtime Value: Request.Querystring("cmbRegion"

Getting Error:

Type mismatch in expression.

Look forward from you.

Babu

Can you replace the % in default value with a value from your table and see what that does?

Also, can you detail the field types that you are using (only the ones called in the query)

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Quote

Can you replace the % in default value with a value from your table and see what that does?

Also, can you detail the field types that you are using (only the ones called in the query)

Regards

Quote

Hi Vince

Default value means: Region 'East London'???



Babu

yes

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi

Can anyone see my code, where I am lost?

&lt;%
Dim rsLondon_lets
Dim rsLondon_lets_numRows

Set rsLondon_lets = Server.CreateObject("ADODB.Recordset"
rsLondon_lets.ActiveConnection = MM_myproperties_STRING
rsLondon_lets.Source = "SELECT myLet.Status, myLet.Region, myLet.Title, myLet.Short_Description, myLet.Type, myLet.Furnished, myLet.Bedrooms, myLet.Per_week, myLet.Property_ID, myLet.Region_id, Let_Region.Region_id, Let_Region.Region_Name FROM myLet, Let_Region WHERE myLet.Region_id=Let_Region.Region_id and Let_Region.Region_Name LIKE '" + Replace(rsLondon_lets__varRegion, "'", "''" + "' ORDER BY Per_week ASC"
rsLondon_lets.CursorType = 2
rsLondon_lets.CursorLocation = 3
rsLondon_lets.LockType = 1
rsLondon_lets.Open()

rsLondon_lets_numRows = 0
%&gt;


NOTE: Another Recordset:


&lt;%
Dim rsfinder
Dim rsfinder_numRows

Set rsfinder = Server.CreateObject("ADODB.Recordset"
rsfinder.ActiveConnection = MM_myproperties_STRING
rsfinder.Source = "SELECT * FROM Let_Region"
rsfinder.CursorType = 0
rsfinder.CursorLocation = 2
rsfinder.LockType = 1
rsfinder.Open()

rsfinder_numRows = 0
%&gt;


Combo Box:


&lt;select name="cmbRegion" id="cmbRegion"&gt;
&lt;option value="value"&gt;Please Select One&lt;/option&gt;
&lt;%
While (NOT rsfinder.EOF)
%&gt;
&lt;option value="&lt;%=(rsfinder.Fields.Item("Region".Value)%&gt;"&gt;&lt;%=(rsfinder.Fields.Item("Region".Value)%&gt;&lt;/option&gt;
&lt;%
rsfinder.MoveNext()
Wend
If (rsfinder.CursorType &gt; 0) Then
rsfinder.MoveFirst
Else
rsfinder.Requery
End If
%&gt;
&lt;/select&gt;



Form:


Form Name: Form

Action: london_lets.asp (Same Page)

Method: GET

Please help.

Babu

Hi

I uploaded my sites, but still unfinished.......www.housewithamouse.co.uk/london_lets.asp
Unknown

I need to get rid of this....

Still couldn't do anything, I am stuck, can't go furthure please dear 'FRIENDS' HELP....

Edited by - babui on 24 Feb 2005 13:49:28