Skip to main content
D
diggable
Known Participant
December 9, 2009
Answered

Multi Criteria Search in 1 text field

  • December 9, 2009
  • 2 replies
  • 1664 views

I know how to build a single search field form and can retrieve the data that's expected.

There's a requirement now to query multiple products and entering it in 1 text field.

Of course, it's tedious if you have to enter 1 product at a time to query 30 products.

In SQL, I'm able to do this using the "IN" statement and hardcoding it below provides a result.

SELECT
ProductID
FROM product
where id in ('100002732','100000067','100157507')

But when I do this in DW CS4, I don't get any results.

Only when I put 1 productID 100002732 will I get any results.

SELECT
ProductID
FROM product
where id in (MMColParam)

I've placed quotes around the MMColParam and still provides no results.

I've read somewhere that cfqueryparam needs to be configured, unfortunately I'm using a MS SQL 2005 db.

Thanks in advance!

This topic has been closed for replies.
Correct answer bregent

As per your recommendation.

I kept the special block code that you provided

kept the command line intact

added the response write

entered 1 product - the product result was provided

entered 2 products, separated with a comma - no response in the result

it only showed the bottom

SELECT dbo.ix_spc_product.ID, dbo.ix_spc_product.Name, dbo.ix_spc_performance.capacity Capacity, dbo.ix_spc_planogram.Name POGName, dbo.ix_spc_planogram.Desc14 Cluster, CASE WHEN dbo.ix_spc_planogram.DBStatus = 1 THEN 'Live' WHEN dbo.ix_spc_planogram.DBStatus = 2 THEN 'Pending' END Status, CASE WHEN dbo.ix_spc_planogram.Flag4 = 0 THEN 'Non-Promo' WHEN dbo.ix_spc_planogram.Flag4 = 1 THEN 'Promo' END Promo, dbo.ix_spc_planogram.livedate FROM dbo.ix_spc_product INNER JOIN dbo.ix_spc_performance ON dbo.ix_spc_product.DBKey = dbo.ix_spc_performance.DBParentProductKey INNER JOIN dbo.ix_spc_planogram ON dbo.ix_spc_performance.DBParentPlanogramKey = dbo.ix_spc_planogram.DBKey where dbo.ix_spc_planogram.dbstatus IN (1,2) and dbo.ix_spc_product.DBKey in(SELECT dbkey Name FROM IKB.dbo.ix_spc_product WHERE id IN(?)) ORDER BY dbo.ix_spc_product.ID, dbo.ix_spc_planogram.Flag4, dbo.ix_spc_planogram.dbstatus

I don't really know if there's any way of doing this. I think the only solution is to input each product in it's own field.


>I don't really know if there's any way of doing this. I think the only solution is to input each product in it's own field.

It can certainly be done - I do it all the time except I don't use the parameter object. Try doing it with concatenated SQL string instead. Leave my array/loop code in there and then change your Command.Text string to this:

PromoID_cmd.CommandText = "SELECT dbo.ix_spc_product.ID,  dbo.ix_spc_product.Name, dbo.ix_spc_performance.capacity  Capacity, dbo.ix_spc_planogram.Name POGName,  dbo.ix_spc_planogram.Desc14 Cluster, CASE   WHEN dbo.ix_spc_planogram.DBStatus = 1 THEN 'Live'   WHEN dbo.ix_spc_planogram.DBStatus = 2 THEN 'Pending'   END Status, CASE   WHEN dbo.ix_spc_planogram.Flag4 = 0 THEN 'Non-Promo'   WHEN dbo.ix_spc_planogram.Flag4 = 1 THEN 'Promo'   END Promo, dbo.ix_spc_planogram.livedate FROM         dbo.ix_spc_product INNER JOIN                       dbo.ix_spc_performance ON dbo.ix_spc_product.DBKey = dbo.ix_spc_performance.DBParentProductKey INNER JOIN                       dbo.ix_spc_planogram ON dbo.ix_spc_performance.DBParentPlanogramKey = dbo.ix_spc_planogram.DBKey where dbo.ix_spc_planogram.dbstatus IN (1,2) and dbo.ix_spc_product.DBKey  in(SELECT  dbkey  Name FROM IKB.dbo.ix_spc_product WHERE id IN (" +PromoID__MMColParam + ")) ORDER BY dbo.ix_spc_product.ID, dbo.ix_spc_planogram.Flag4, dbo.ix_spc_planogram.dbstatus"

Then comment out these two line


'PromoID_cmd.Prepared = true
'PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param1", 200, 1, 255, PromoID__MMColParam) ' adVarChar

Then execute and see if that works. If it doesn't post the screen output of the SQL again.

2 replies

B
bregent
Participating Frequently
December 10, 2009

>In SQL, I'm able to do this using the "IN" statement and hardcoding it below provides a result.

>

>SELECT
>ProductID
>FROM product
>where id in ('100002732','100000067','100157507')

If they are searching by product id and there is a finite number of products, wouldn't it be better to use a listbox populated with possible values, rather than forcing the user to enter is multiple id's?

D
diggableAuthor
Known Participant
December 10, 2009

Thanks for your help.

As for the listbox, do you mean putting a drop down list with all 5000 products?

thanks again!

====== This is the search page: =======

<div id="LayoutResultPFP">
  <form id="form1" name="form1" method="get" action="searchbyPromoAnalysis-result.asp">
    
    <textarea name="ID" id="ID" cols="45" rows="5"></textarea>
<input type="submit" name="button" id="button" value="Submit" />
  </form>

====== Result page: ===============

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Session.LCID=1033 %>
<!--#include virtual="/Connections/IKB_LOCAL1.asp" -->
<%
Dim PromoID__MMColParam
PromoID__MMColParam = "%"
If (Request.QueryString("ID")   <> "") Then
  PromoID__MMColParam = Request.QueryString("ID") 
End If
%>
<%
Dim PromoID
Dim PromoID_cmd
Dim PromoID_numRows

Set PromoID_cmd = Server.CreateObject ("ADODB.Command")
PromoID_cmd.ActiveConnection = MM_IKB_LOCAL1_STRING
PromoID_cmd.CommandText = "SELECT dbo.ix_spc_product.ID,  dbo.ix_spc_product.Name, dbo.ix_spc_performance.capacity  Capacity, dbo.ix_spc_planogram.Name POGName,  dbo.ix_spc_planogram.Desc14 Cluster, CASE   WHEN dbo.ix_spc_planogram.DBStatus = 1 THEN 'Live'   WHEN dbo.ix_spc_planogram.DBStatus = 2 THEN 'Pending'   END Status, CASE   WHEN dbo.ix_spc_planogram.Flag4 = 0 THEN 'Non-Promo'   WHEN dbo.ix_spc_planogram.Flag4 = 1 THEN 'Promo'   END Promo, dbo.ix_spc_planogram.livedate FROM         dbo.ix_spc_product INNER JOIN                       dbo.ix_spc_performance ON dbo.ix_spc_product.DBKey = dbo.ix_spc_performance.DBParentProductKey INNER JOIN                       dbo.ix_spc_planogram ON dbo.ix_spc_performance.DBParentPlanogramKey = dbo.ix_spc_planogram.DBKey where dbo.ix_spc_planogram.dbstatus IN (1,2) and dbo.ix_spc_product.DBKey  in(SELECT  dbkey  Name FROM IKB.dbo.ix_spc_product WHERE id in (? )  ) ORDER BY dbo.ix_spc_product.ID, dbo.ix_spc_planogram.Flag4, dbo.ix_spc_planogram.dbstatus"
PromoID_cmd.Prepared = true
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param1", 200, 1, 255, PromoID__MMColParam) ' adVarChar

Set PromoID = PromoID_cmd.Execute
PromoID_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
PromoID_numRows = PromoID_numRows + Repeat1__numRows
%>

<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim PromoID_total
Dim PromoID_first
Dim PromoID_last

' set the record count
PromoID_total = PromoID.RecordCount

' set the number of rows displayed on this page
If (PromoID_numRows < 0) Then
  PromoID_numRows = PromoID_total
Elseif (PromoID_numRows = 0) Then
  PromoID_numRows = 1
End If

' set the first and last displayed record
PromoID_first = 1
PromoID_last  = PromoID_first + PromoID_numRows - 1

' if we have the correct record count, check the other stats
If (PromoID_total <> -1) Then
  If (PromoID_first > PromoID_total) Then
    PromoID_first = PromoID_total
  End If
  If (PromoID_last > PromoID_total) Then
    PromoID_last = PromoID_total
  End If
  If (PromoID_numRows > PromoID_total) Then
    PromoID_numRows = PromoID_total
  End If
End If
%>

<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (PromoID_total = -1) Then

  ' count the total records by iterating through the recordset
  PromoID_total=0
  While (Not PromoID.EOF)
    PromoID_total = PromoID_total + 1
    PromoID.MoveNext
  Wend

  ' reset the cursor to the beginning
  If (PromoID.CursorType > 0) Then
    PromoID.MoveFirst
  Else
    PromoID.Requery
  End If

  ' set the number of rows displayed on this page
  If (PromoID_numRows < 0 Or PromoID_numRows > PromoID_total) Then
    PromoID_numRows = PromoID_total
  End If

  ' set the first and last displayed record
  PromoID_first = 1
  PromoID_last = PromoID_first + PromoID_numRows - 1
 
  If (PromoID_first > PromoID_total) Then
    PromoID_first = PromoID_total
  End If
  If (PromoID_last > PromoID_total) Then
    PromoID_last = PromoID_total
  End If

End If
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SearchByProductIDPromo</title>
<link href="../styleguide/styleguide.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
#apDiv1 {
    position:absolute;
    left:19px;
    top:16px;
    width:614px;
    height:55px;
    z-index:1;
}
#apDiv2 {
    position:absolute;
    left:18px;
    top:87px;
    width:645px;
    height:48px;
    z-index:2;
}
#apDiv3 {
    position:absolute;
    left:19px;
    top:187px;
    width:669px;
    height:45px;
    z-index:3;
}
-->
</style>
</head>

<body>
<div id="LayoutResultTitle">Promo Products</div>
<div id="LayoutResultPFP">Status:  <br />
Total Count: <%=(PromoID_total)%></div>
<div id="LayoutResultData">
<table width="1100" border="0" cellpadding="0" cellspacing="0" class="SearchDataTable" id="tablesorter-demo">
 
  <thead>
    <tr>
    <th width="75" align="left">ID</th>
    <th width="200" align="left">Name</th>
    <th width="50" align="left">Capacity</th>
    <th width="250" align="left">POG Name</th>
    <th width="100" align="left">Cluster</th>
    <th width="75" align="left">Status</th>
    <th width="60" align="left">Promo</th>
    <th width="75" align="left">Live Date</th>
   
  </thead> <tbody>
    <%
While ((Repeat1__numRows <> 0) AND (NOT PromoID.EOF))
%>
  <tr>
    <td width="75"><%=(PromoID.Fields.Item("ID").Value)%></td>
    <td width="200"><%=(PromoID.Fields.Item("Name").Value)%></td>
    <td width="50"><%=(PromoID.Fields.Item("Capacity").Value)%></td>
    <td width="250"><%=(PromoID.Fields.Item("POGName").Value)%></td>
    <td width="100"><%=(PromoID.Fields.Item("Cluster").Value)%></td>
    <td width="75"><%=(PromoID.Fields.Item("Status").Value)%></td>
    <td width="60"><%=(PromoID.Fields.Item("Promo").Value)%></td>
    <td width="75"><%=(PromoID.Fields.Item("livedate").Value)%></td>
  </tr>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  PromoID.MoveNext()
Wend
%>
  </tbody>
</table>
<p> </p>
<p> </p>
<p> </p>
</div>
</body>
</html><%
PromoID.Close()
Set PromoID = Nothing
%>

D
diggableAuthor
Known Participant
December 10, 2009

I see your point about the script, that'll be another post for me to make.

Thanks for the Like predicate sample for "%" something to add to my knowledgebase.

However, with or without the quotes if I enter more products separated with a comma there's no results.

If it's only 1 product without the quotes I get a result.

In the Parameters window I even ran tests.

Name: MMColParam

type: Text or Numeric

Value: Request.Querystring("ID")

Default value: 1

with Default Value of a productID, it returns the result

with Default Value of productIDs, with/without quotes and separated by Commas - no results

I think we're getting really close here.

thanks again!


I think I figured something out, but I'll definitely need your thoughts on this.

I tested by creating 5 textboxes, each box will be populated with a productID.

I created 5 MMColParams for each textbox

SELECT  dbkey

Name FROM product

WHERE id like MMColParam or id Like MMColParam2 or id Like MMColParam3 or id Like MMColParam4 or ID Like MMColParam5

Well, it's the result that I was hoping for.

Of course, ideally 1 textbox would be better no need to cut/paste to 5 textboxes.

code below:

<%
Dim PromoID__MMColParam
PromoID__MMColParam = "1"
If (Request.QueryString("ID")       <> "") Then
  PromoID__MMColParam = Request.QueryString("ID")     
End If
%>
<%
Dim PromoID__MMColParam2
PromoID__MMColParam2 = "1"
If (Request.Querystring("ID2")   <> "") Then
  PromoID__MMColParam2 = Request.Querystring("ID2") 
End If
%>
<%
Dim PromoID__MMColParam3
PromoID__MMColParam3 = "1"
If (Request.QueryString("ID3") <> "") Then
  PromoID__MMColParam3 = Request.QueryString("ID3")
End If
%>
<%
Dim PromoID__MMColParam4
PromoID__MMColParam4 = "1"
If (Request.QueryString("ID4") <> "") Then
  PromoID__MMColParam4 = Request.QueryString("ID4")
End If
%>
<%
Dim PromoID__MMColParam5
PromoID__MMColParam5 = "1"
If (Request.QueryString("ID5")  <> "") Then
  PromoID__MMColParam5 = Request.QueryString("ID5")
End If
%>
<%
Dim PromoID
Dim PromoID_cmd
Dim PromoID_numRows

Set PromoID_cmd = Server.CreateObject ("ADODB.Command")
PromoID_cmd.ActiveConnection = MM_IKB_LOCAL1_STRING
PromoID_cmd.CommandText = "SELECT dbo.ix_spc_product.ID,  dbo.ix_spc_product.Name, dbo.ix_spc_performance.capacity  Capacity, dbo.ix_spc_planogram.Name POGName,  dbo.ix_spc_planogram.Desc14 Cluster, CASE   WHEN dbo.ix_spc_planogram.DBStatus = 1 THEN 'Live'   WHEN dbo.ix_spc_planogram.DBStatus = 2 THEN 'Pending'   END Status, CASE   WHEN dbo.ix_spc_planogram.Flag4 = 0 THEN 'Non-Promo'   WHEN dbo.ix_spc_planogram.Flag4 = 1 THEN 'Promo'   END Promo, dbo.ix_spc_planogram.livedate FROM         dbo.ix_spc_product INNER JOIN                       dbo.ix_spc_performance ON dbo.ix_spc_product.DBKey = dbo.ix_spc_performance.DBParentProductKey INNER JOIN                       dbo.ix_spc_planogram ON dbo.ix_spc_performance.DBParentPlanogramKey = dbo.ix_spc_planogram.DBKey where dbo.ix_spc_planogram.dbstatus IN (1,2) and dbo.ix_spc_product.DBKey  in(SELECT  dbkey  Name FROM IKB.dbo.ix_spc_product WHERE id like ? or id Like ? or id Like ? or id Like ? or ID Like ?) ORDER BY dbo.ix_spc_product.ID, dbo.ix_spc_planogram.Flag4, dbo.ix_spc_planogram.dbstatus"
PromoID_cmd.Prepared = true
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param1", 200, 1, 255, PromoID__MMColParam) ' adVarChar
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param2", 200, 1, 255, PromoID__MMColParam2) ' adVarChar
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param3", 200, 1, 255, PromoID__MMColParam3) ' adVarChar
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param4", 200, 1, 255, PromoID__MMColParam4) ' adVarChar
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param5", 200, 1, 255, PromoID__MMColParam5) ' adVarChar

Set PromoID = PromoID_cmd.Execute
PromoID_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
PromoID_numRows = PromoID_numRows + Repeat1__numRows
%>

B
bregent
Participating Frequently
December 9, 2009

When inputting the form, are you entering the products wrapped in quotes and comma seperated?

D
diggableAuthor
Known Participant
December 9, 2009

extracts data:

from the form, entering 1 product data without quotes (this is how I prefer to have it entered without quotes)

doesn't extract:

from the form, entering multiple products with comma with or without quotes

thank you sir!

B
bregent
Participating Frequently
December 10, 2009

Can you output the value of MMColParam to the screen so you can see why it's failing? Is this a CF applicaiton?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices