Command for Query Help

[LittleNFiesty]

I have this line of code for my query and I would like it to find a particular string inside the field Industry_ID that could be P, H, or O. I have the param's set correctly but I can't remember the "=" section. I've used the LIKE before, but it won't work on this.

Product_List_cmd.CommandText = "SELECT * FROM Qry_Product_List_R_Industry WHERE Product_Cat = ? AND Industry_ID = ? ORDER BY Title,Item_No ASC"


Here is the entire code for this section in case it helps answer the question:
<%
Dim Product_List__MasterListParam
Product_List__MasterListParam = "9"
If (Request.QueryString("Product_Cat") <> "") Then
Product_List__MasterListParam = Request.QueryString("Product_Cat")
End If
%>
<%
Dim Product_List__IndustryParam
Product_List__IndustryParam = "h"
If (Request.QueryString("Industry_ID") <> "") Then
Product_List__IndustryParam = Request.QueryString("Industry_ID")
End If
%>
<%
Dim Product_List
Dim Product_List_cmd
Dim Product_List_numRows

Set Product_List_cmd = Server.CreateObject ("ADODB.Command")
Product_List_cmd.ActiveConnection = MM_AnimalSafety2_STRING
Product_List_cmd.CommandText = "SELECT * FROM Qry_Product_List_R_Industry WHERE Product_Cat = ? AND Industry_ID = ? ORDER BY Title,Item_No ASC"
Product_List_cmd.Prepared = true
Product_List_cmd.Parameters.Append Product_List_cmd.CreateParameter("param1", 200, 1, 255, Product_List__MasterListParam) ' adVarChar
Product_List_cmd.Parameters.Append Product_List_cmd.CreateParameter("param2", 200, 1, 255, Product_List__IndustryParam) ' adVarChar

Set Product_List = Product_List_cmd.Execute
Product_List_numRows = 0
%>

 

[ggriffit]

you may be able to use the IN clause instead, e.g.

Product_List_cmd.CommandText = "SELECT * FROM Qry_Product_List_R_Industry WHERE Product_Cat = ? AND Industry_ID in ('P','H','O') ORDER BY Title,Item_No ASC"

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[LittleNFiesty]

That didn't work because the values in the field in the database are H or P, H or P, H, O. So I need it to find the variable (P, H, or O) in the field, it could have one or all of them in the field. so I need a "contains" command.

 

[ggriffit]

which database are you using as the CONTAINS SQL will be different for each ?

a slightly verbose version could be

(
(Industry_ID like '%P%')
OR
(Industry_ID like '%H%')
OR
(Industry_ID like '%O%')
)

This will return any rows where Industry_ID contains a P, H or an O or more than one of these characters.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[LittleNFiesty]

I got it figured out. Here is the final line of code:

Product_List_cmd.CommandText = "SELECT * FROM Qry_Product_List_R_Industry WHERE Product_Cat = ? AND Industry_ID LIKE '%" + Replace(Product_List__IndustryParam, "'", "''") + "%' ORDER BY Title,Item_No ASC"

Thanks for all your guys' help!!!