Multi Criteria Search in 1 text field

[diggable]

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!

[bregent]

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

[diggable]

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!

[bregent]

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

[diggable]

Not a CF, it's in ASP.

apologies, how do you output the value to MMCOLParam to screen so I can see how it's failing?

from the form screen, if I enter 1 productID in the field without quotes - there's data in the result screen

if I enter more than 1 productid, separated by comma and with/without quotes - there's no data in the result screen

in the parameters select window (if I select the mmcolparam), where the value is usually "1" if I enter a productid without quotes and do a test - a result is provided in the pop-up window. I can see the data of all the linked productIDs

if I enter more than 1 productid, with comma separated and with or without any quotes - there's no result that comes out in the pop-up window

thanks in advance!

[bregent]

Can you paste in the complete recordset code?

[bregent]

>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?

[diggable]

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
%>

[diggable]

This line of code here -

<%
Dim PromoID__MMColParam
PromoID__MMColParam = "%"
If (Request.QueryString("ID")   <> "") Then
  PromoID__MMColParam = Request.QueryString("ID") 
End If
%>

Previously, I had it as "1", but read somewhere to make it "%" so

maybe it can include multiple productIDs. Neither seems to work accordingly.

PromoID__MMColParam = "1"

so, ideally it should be able to pick up more than 1 productid that the user enters in the search text field ie.

100087596, 100138023, 100157507

thanks again!

[bregent]

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

Nooo. You had mentioned 30 products in the original post and I wasn't sure if that was the total number of products. Populating a drop down with 5000 items isn't a good idea.

>Previously, I had it as "1", but read somewhere to make it "%" so

>maybe it can include multiple productIDs. Neither seems to work accordingly.

The wildcard will only work with the 'like' predicate, not 'in'.

>so, ideally it should be able to pick up more than 1 productid that the user enters in the search text field ie.

>100087596, 100138023, 100157507

It won't work unless you wrap each product in a quote. That's a pain for users so it would be better to modify the script to take the comma seperated form values and process through a loop to automatically add commas and save back into PromoID__MMColParam

[diggable]

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!

[diggable]

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
%>

[bregent]

Using 5 fields would be a pain. It should work with the comma seperated values. Try this. Insert this code block into your existing code where it says INSERT HERE and see if that works. It should allow the user to type a comma seperated list without quotes.

<%@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
%>

-------------------------- INSERT HERE---------------------------------------

<%
Dim PromoID__MMColParam_array, PromoID__MMColParam_temp
PromoID__MMColParam_array=split(PromoID__MMColParam,",")
For i=LBound(PromoID__MMColParam_array) to UBound(PromoID__MMColParam_array)
    If i < UBound(PromoID__MMColParam_array) Then
        PromoID__MMColParam_temp = PromoID__MMColParam_temp + "'" & trim(PromoID__MMColParam_array(i)) & "',"
    Else
        PromoID__MMColParam_temp = PromoID__MMColParam_temp + "'" & trim(PromoID__MMColParam_array(i)) & "'"
    End If
Next
PromoID__MMColParam = PromoID__MMColParam_temp
%>

---------------------END CODE BLOCK--------------------------------------------

<%
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

[diggable]

Thanks for this.

I see what you're trying to do in the code by taking each product separating it by commas.

I'm looking at your code and slowly digesting it, trying to translate each line.

I entered the code as instructed.

From the text field in the search page, I enter a product without quotes (preferred method) and a result is provided for that product.

If I enter multiple products, separated by a comma and no quotes. no result is provided.

thanks again!

[diggable]

reposting code.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Session.LCID=1033 %>
<!--#include virtual="/Connections/IKB_LOCAL1.asp" -->

<%
Dim PromoID__MMColParam
PromoID__MMColParam = "1"
If (Request.QueryString("ID")       <> "") Then
  PromoID__MMColParam = Request.QueryString("ID")     
End If
%>

<%
Dim PromoID__MMColParam_array, PromoID__MMColParam_temp
PromoID__MMColParam_array=split(PromoID__MMColParam,",")
For i=LBound(PromoID__MMColParam_array) to UBound(PromoID__MMColParam_array)
    If i < UBound(PromoID__MMColParam_array) Then
        PromoID__MMColParam_temp = PromoID__MMColParam_temp + "'" & trim(PromoID__MMColParam_array(i)) & "',"
    Else
        PromoID__MMColParam_temp = PromoID__MMColParam_temp + "'" & trim(PromoID__MMColParam_array(i)) & "'"
    End If
Next
PromoID__MMColParam = PromoID__MMColParam_temp
%>

<%
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">Multi-Product Capacity Result4</div>
<div id="LayoutResultPFP"> 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
%>

[bregent]

>If I enter multiple products, separated by a comma and no quotes. no result is provided.

Not surprising since when you entered it manually like this it didn't work either. I just wanted to eliminate the possibility of a typo being entered. Try this, comment out the command object execute line and insert this instead.

response.write PromoID_cmd.CommandText

This should output the sql statement to the screen. I believe it will include the substituted parameter values. Then we can see why it's failing.

[diggable]

Thanks for this, I'm learning a lot on this!

This is the output that I got.

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

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/product/searchbyPromoAnalysis-result.asp, line 63

this is line 63 though?   PromoID_total = PromoID.RecordCount

[bregent]

>FROM IKB.dbo.ix_spc_product WHERE id IN (?))

Bummer, I was hoping the commandtext would include the value of the parameter but it doesn't. The error you are seeing is expected, since you are not executing the command object SQL. What you may want to try is putting the execute back in and then place the response.write after that. Maybe once the command is executed the commandtext will contain the param value which we need for troubleshooting.  I don't think so but it's worth a shot. I haven't used parameter objects in quite a while.

[diggable]

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.

[bregent · Accepted solution]

>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.

[diggable]

Aww my!

It works! I changed the text field to a text area and entered around 10 products, with just a comma separating them and no quotes.

voila! all 10 results were there!

Objective: Enter multiple products in a single text field, with comma separated for each product, no quotes

Result: all products queried in result

===================================================================================

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Session.LCID=1033 %>

<%
Dim PromoID__MMColParam
PromoID__MMColParam = "1"
If (Request.QueryString("ID")       <> "") Then
  PromoID__MMColParam = Request.QueryString("ID")     
End If
%>

<%
Dim PromoID__MMColParam_array, PromoID__MMColParam_temp
PromoID__MMColParam_array=split(PromoID__MMColParam,",")
For i=LBound(PromoID__MMColParam_array) to UBound(PromoID__MMColParam_array)
    If i < UBound(PromoID__MMColParam_array) Then
        PromoID__MMColParam_temp = PromoID__MMColParam_temp + "'" & trim(PromoID__MMColParam_array(i)) & "',"
    Else
        PromoID__MMColParam_temp = PromoID__MMColParam_temp + "'" & trim(PromoID__MMColParam_array(i)) & "'"
    End If
Next
PromoID__MMColParam = PromoID__MMColParam_temp
%>

<%
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 (" +PromoID__MMColParam + ")) ORDER BY dbo.ix_spc_product.ID, dbo.ix_spc_planogram.Flag4, dbo.ix_spc_planogram.dbstatus"

---------------------- REM Bottom OUT ---------------------

<!--PromoID_cmd.Prepared = true
PromoID_cmd.Parameters.Append PromoID_cmd.CreateParameter("param1", 200, 1, 255, PromoID__MMColParam) ' adVarChar response.write PromoID_cmd.CommandText-->

---------------------- REM TOP OUT ---------------------

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">Multi-Product Capacity Result</div>
<div id="LayoutResultPFP"> 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
%>
=======================================================================

[bregent]

>it works!

Great. I did notice that it looks like you are trying to use the html comment tag to comment out asp code.

<!--PromoID_cmd.Prepared = true
PromoID_cmd.Parameters.AppendPromoID_cmd.CreateParameter("param1", 200, 1, 255, PromoID__MMColParam)' adVarChar response.write PromoID_cmd.CommandText-->

That does not work. You need to use an apostrophe.