I have a search all link which basically opens up the dynamic page with no query string. I need then the recordset to bring back all of the categories on that page. For example 4,6,7 and 8.

Can someone help me out. We are scheduled to go live in a week and this was something I thought I had working already. When I got the data in, I get all records back with my solution. No good.

Thanks in advance for your help!

<%
Dim product_details__MMColParam
product_details__MMColParam = "1"
If (Request.QueryString("Category_ID" <> "" Then
product_details__MMColParam = Request.QueryString("Category_ID"
End If
%>
<%
Dim product_details
Dim product_details_numRows

Set product_details = Server.CreateObject("ADODB.Recordset"
product_details.ActiveConnection = MM_mysql_db_STRING
product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID = " + Replace(product_details__MMColParam, "'", "''" + ""
product_details.CursorType = 0
product_details.CursorLocation = 2
product_details.LockType = 1
product_details.Open()

product_details_numRows = 0
%>

It looks like you are using DW MX/MX2004; check out my tutorial <i>DW MX/MX2004: Controlling Recordset Defaults</i>: www.dmxzone.com/go?7072 , and it might get the gears moving.

~ ~ ~ ~ ~ ~ ~
Chris Charlton <i>- DMXzone Manager</i>
<font size=1>[ Studio MX/MX2004 | PHP/ASP | SQL | XHTML/CSS | XML | Actionscript | Web Accessibility | MX Extensibility ]</font id=size1>

Normally, when I want to show everything in a recordset to start with and then have the option to narrow the results down with a search I used the wildcard character %

This will only work however when you are using text..... I see from your example that you are using numbers and if they are in a non-text field you will have issues.

One way to fix this (not very pretty) is to have two recordsets on your page and use either one based on a condition to check if your querystring is empty.....


For eaxmple:

&lt;% If Request.Querysting("Category_ID" &lt;&gt; "" then %&gt;



&lt;%
Dim product_details__MMColParam
product_details__MMColParam = "1"
If (Request.QueryString("Category_ID" &lt;&gt; "" Then
product_details__MMColParam = Request.QueryString("Category_ID"
End If
%&gt;
&lt;%
Dim product_details
Dim product_details_numRows

Set product_details = Server.CreateObject("ADODB.Recordset"
product_details.ActiveConnection = MM_mysql_db_STRING
product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID = " + Replace(product_details__MMColParam, "'", "''" + ""
product_details.CursorType = 0
product_details.CursorLocation = 2
product_details.LockType = 1
product_details.Open()

product_details_numRows = 0
%&gt;

&lt;% else %&gt;

&lt;%
Dim product_details__MMColParam
product_details__MMColParam = "1"
If (Request.QueryString("Category_ID" &lt;&gt; "" Then
product_details__MMColParam = Request.QueryString("Category_ID"
End If
%&gt;
&lt;%
Dim product_details
Dim product_details_numRows

Set product_details = Server.CreateObject("ADODB.Recordset"
product_details.ActiveConnection = MM_mysql_db_STRING
product_details.Source = "SELECT * FROM mysql_store.products "
product_details.CursorType = 0
product_details.CursorLocation = 2
product_details.LockType = 1
product_details.Open()

product_details_numRows = 0
%&gt;







Regards
Vince

DMX Manager

Visit my home: www.chez-vince.com
(Now completely CSS based and bye bye to all tables!)

VBScript | ASP | HTML | CSS | SQL | Oracle | Hosting

Thanks for both suggestions. I understand the advanced option. What I'm trying to do is set the product_details_MMColParam = "1 or 4 or 5" I think the reason it doesn't work is due to the wrong syntax and this line:

WHERE Category_ID = " + Replace(product_details__MMColParam, "'", "''" + ""

Hi Michael

A couple of questions for you. How do you know what the categories are on the page and what database are you using?

Lee Diggins - DMXzone Manager

Sharing Knowledge Saves Valuable Time!!!

They are static per page. site256.webhost4life.com/stroudtx/girl_newborn.asp is a good example. This is somewhat of a dynamic page. All of the products on here are going be fore newborn girls. Therefore, my recordset would be satic for this page. I would then modify it for each of the other pages in the site. When you click on Sets for example, it sets the Category_ID in the URL to 1. What I want to happen is when you click to "See All" or come to the page above, you see all of the products that make up the menu items on the left. These are 1-7. One another page they might be 7-12 and 6 and 2 as an example.

Thanks. As you can see we are close to going live. We are using MySQL with ASP vb.

Mike

Edited by - stroudtx on 07 Feb 2005 01:05:20

Edited by - stroudtx on 07 Feb 2005 01:06:29

Hi Michael

Try changing this:

&lt;%
Dim product_details__MMColParam
product_details__MMColParam = "1"
If (Request.QueryString("Category_ID" &lt;&gt; "" Then
product_details__MMColParam = Request.QueryString("Category_ID"
End If
%&gt;

To this:

&lt;%
Dim product_details__MMColParam
product_details__MMColParam = "4 or 6 or 7 or 8"
%&gt;

It might work, then again the syntax maybe wrong unfortunately I don't use mySQL.

Post back.


Lee Diggins - DMXzone Manager

Sharing Knowledge Saves Valuable Time!!!

I had tried that before. When I put or in thier I get every record vs. when I just have a single digit.

Does it have something to do with DW Where Statement. I don't know why they have all that wierd stuff at the end of the Replace ( ) either.

Thanks for your help! BTW, does my site look ok on your box?

product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID = " + Replace(product_details__MMColParam, "'", "''" + ""

Hi Michael

What about:

&lt;%
Dim product_details__MMColParam
product_details__MMColParam = "4 or Category_ID = 6 or Category_ID = 7 or Category_ID = 8"
%&gt;

Do you know if MySQL supports the 'WHERE IN' statement or something similar, as this would be ideal, something like this:

SELECT * FROM mysql_store.products WHERE Category_ID IN ('4','6','7','8')

To test your results, change:
product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID = " + Replace(product_details__MMColParam, "'", "''" + ""

To:
product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID IN ('4','6','7','8')"


Lee Diggins - DMXzone Manager

Sharing Knowledge Saves Valuable Time!!!

Thanks! It works perfect now. I don't understand why though? I hope this post also helps others with the same question.

Best of luck to you!!

Mike

product_details__MMColParam = "4 or Category_ID = 6 or Category_ID = 7 or Category_ID = 8"


<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi Michael

What about:

&lt;%
Dim product_details__MMColParam
product_details__MMColParam = "4 or Category_ID = 6 or Category_ID = 7 or Category_ID = 8"
%&gt;

Do you know if MySQL supports the 'WHERE IN' statement or something similar, as this would be ideal, something like this:

SELECT * FROM mysql_store.products WHERE Category_ID IN ('4','6','7','8')

To test your results, change:
product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID = " + Replace(product_details__MMColParam, "'", "''" + ""

To:
product_details.Source = "SELECT * FROM mysql_store.products WHERE Category_ID IN ('4','6','7','8')"


Lee Diggins - DMXzone Manager

Sharing Knowledge Saves Valuable Time!!!
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>