Won't return data when "%" is in search value

[streborr]

Some of our products contain the "%" sign in the name of the product, example: "Product1 20% GF".

The user drills down thru choices in combo boxes to find information about our products. The list 1 contains Product Category, list 2 contians Product Name (from the chosen category), list 3 contains type of properties for the chosen product.

This information is sent to the db and returns the properties of the chosen product. Everthing works perfect until the user requests information about a product with the "%" sign in the Product Name.

Question: How do I get around this without re-naming the products. (not an option)


The "%20" was added because of blank spaces.
The "20%" is part of the product name.

hpprop.asp?prodcat=High_Performance&prodname=Product1%2020%%20GF&propcat=Mechanical


Thanks for any assistance,
Rick

 

[Dynapen]

First of all, what type of DB are you using?


Are you wrapping the value in ' ' before you pass it to the DB? If you do the DB won't try to evaluate it and it will just look for the string.

Can you show us the code that actually builds the Select statement so we can help you out better. The money's gone, the brain is shot.....but the liquor we still got.

 

[streborr]

I'm using Access for DB.
The include file is the connection string.

strProdCat = trim(request.querystring("prodcat&quot)
strProdName = trim(request.querystring("prodname&quot)
strPropCat = trim(request.querystring("propcat&quot)

<!-- #include file=&quot;con_propdb.asp&quot; -->

<%

Select Case UCase(strPropCat)


Case &quot;MECHANICAL&quot;
response.write &quot;<br>&quot;
strProp = &quot;Mechanical Properties&quot;
Source = &quot;SELECT HP_Mech_Tens_Str_yld_U, HP_Mech_Tens_Str_yld_TM, HP_Mech_Tens_Str_yld_V, HP_Mech_Tens_Str_break_U, &quot; & _
&quot;HP_Mech_Tens_Str_break_TM, HP_Mech_Tens_Str_break_V, HP_Mech_Tens_Modulus_U, HP_Mech_Tens_Modulus_TM, &quot; & _
&quot;HP_Mech_Tens_Modulus_V, HP_Mech_Tens_Elong_yield_U, HP_Mech_Tens_Elong_yield_TM, HP_Mech_Tens_Elong_yield_V, &quot; & _
&quot;HP_Mech_Tens_Elong_break_U, HP_Mech_Tens_Elong_break_TM, HP_Mech_Tens_Elong_break_V, HP_Mech_Flex_Str_yield_U, &quot; & _
&quot;HP_Mech_Flex_Str_yield_TM, HP_Mech_Flex_Str_yield_V, HP_Mech_Flex_Mod_U, HP_Mech_Flex_Mod_TM, &quot; & _
&quot;HP_Mech_Flex_Mod_V, HP_Mech_Comp_Str_yield_U, HP_Mech_Comp_Str_yield_TM, HP_Mech_Comp_Str_yield_V, &quot;& _
&quot;HP_Mech_Comp_Mod_U, HP_Mech_Comp_Mod_TM, HP_Mech_Comp_Mod_V, HP_Mech_Izod_Imp_Str_Un_U, HP_Mech_Izod_Imp_Str_Un_TM, &quot; & _
&quot;HP_Mech_Izod_Imp_Str_Un_V, HP_Mech_Izod_Imp_Str_Notched73°F_U, HP_Mech_Izod_Imp_Str_Notched73°F_TM, &quot; & _
&quot;HP_Mech_Izod_Imp_Str_Notched73°F_V, HP_Mech_Hardness_U, HP_Mech_Hardness_TM, HP_Mech_Hardness_V &quot; & _
&quot;FROM High_Performance Where (HP_WPC_NAME ='&quot;& strProdName &&quot;')&quot;
Dim strArray(33)
strArray(0) = &quot;Tensile Strength @yield&quot;
strArray(3) = &quot;Tensile Strength @break&quot;
strArray(6) = &quot;Tensile Modulus&quot;
strArray(9) = &quot;Tensile Elongation @yield&quot;
strArray(12) = &quot;Tensile Elongation @break&quot;
strArray(15) = &quot;Flexural Strength @yield&quot;
strArray(18) = &quot;Flexural Modulus&quot;
strArray(21) = &quot;Compressive Strength @yield&quot;
strArray(24) = &quot;Compressive Modulus&quot;
strArray(27) = &quot;Izod Impact Strength Un-Notched&quot;
strArray(30) = &quot;Izod Impact Strength Notched @73°F&quot;
strArray(33) = &quot;Hardness (R, M, L or D)&quot;

Case &quot;THERMAL&quot;
stuff
Case &quot;ELECTRICAL&quot;
stuff
Case &quot;FLAMMABILTY&quot;
more stuff
End Select

Set rsADO = Server.CreateObject(&quot;ADODB.Recordset&quot
rsADO.Open Source, ConnectionString

%>

<table border=&quot;0&quot; bgcolor=&quot;#1a55a4&quot;><tr>
<td class=&quot;prophead&quot;><% =strProp %> </td>
<td align=&quot;right&quot;><font size=&quot;2&quot;><a href=&quot;prop.asp&quot;><b>New Search</b></a></font>  </td>
</tr>
<tr>
<td colspan=&quot;2&quot;>
<table bgcolor=&quot;#ffffff&quot; cellpadding=&quot;1&quot; cellspacing=&quot;1&quot; border=&quot;0&quot;>
<tr><td width=&quot;225&quot; class=&quot;colhead&quot;></td>
<td width=&quot;150&quot; class=&quot;colhead&quot;>Units</td>
<td width=&quot;150&quot; class=&quot;colhead&quot;>Test Method</td>
<td width=&quot;150&quot; class=&quot;colhead&quot;>Value</td></tr>

<%
For Each fldLoop In rsADO.Fields
select case i
Case 0,3,6,9,12,15,18,21,24,27,30,33
select case i
case 0,6,12,18,24,30
%>
<tr><td class=&quot;trhead&quot; bgcolor=&quot;#eeeeee&quot;><% =strArray(i) %></td>
<td class=&quot;propval&quot; bgcolor=&quot;#eeeeee&quot;><% =fldLoop.Value %></td>
<%
case 3,9,15,21,27,33
%>
<tr><td class=&quot;trhead&quot; bgcolor=&quot;#dddddd&quot;><% =strArray(i) %></td>
<td class=&quot;propval&quot; bgcolor=&quot;#dddddd&quot;><% =fldLoop.Value %></td>
<%
end select
Case 1,4,7,10,13,16,19,22,25,28,31,34
select case i
case 1,7,13,19,25,31
%>
<td class=&quot;propval&quot; bgcolor='#eeeeee'><% =fldLoop.Value %></td>
<%
case 4,10,16,22,28,34
%>
<td class=&quot;propval&quot; bgcolor=&quot;#dddddd&quot;><% =fldLoop.Value %></td>
<%
end select
Case 2,5,8,11,14,17,20,23,26,29,32,35
select case i
case 2,8,14,20,26,32
%>
<td class=&quot;propval&quot; bgcolor='#eeeeee'><% =fldLoop.Value %></td></tr>
<%
case 5,11,17,23,29,35
%>
<td class=&quot;propval&quot; bgcolor=&quot;#dddddd&quot;><% =fldLoop.Value %></td></tr>
<%
end select
end select

i = i + 1
Next
response.write &quot;</table>&quot;
response.write &quot;</td></tr>&quot;
response.write &quot;</table>&quot;
response.write &quot;</body></html>&quot;
rsADO.close
set rsADO = nothing
%>

Thanks,
Rick

 

[denoxis]

First, print your strProdName variable to see if it has %20 in it. You can use replace(strProdName,'%20',' ') to get rid of it. Your '20%' in the product name shouldn't be a problem since I just tested it in an Access query.

 

[GaryC123]

I'm missing the gist of this or is the problem to do with the values that are passed in the URL not with the DB, and then being retrieved in the next page, because of the % sign.
If so, replace it with a character that would never be used before its placed in the URL or I supposed you could even do this as part of the URL. Then in the next page replace this unused character with % before accessing the DB

Saturday 12.00

20.00

3.00am

Sunday [img http

 

[streborr]

I'm sorry for not getting back sooner. (seminar season)

When sending the strProdName which contains a &quot;%&quot; in the name, to the next page the &quot;%&quot; is dropped.

i.e. Product1 20% gf becomes Product1 20 gf.

I tried using replace but I couldn't get it to work so I just did this, on the receivung page, instead:

If strProdName = &quot;Product1 20 gf&quot; Then
strProdName = &quot;Product1 20% gf&quot;
End If

Luckily thier aren't that many products with &quot;%&quot; in the name.

Thanks for all you're help,
Rick

 

[GaryC123]

How were you doing replace?
Should be like this

strProdName = replace(strProdName,&quot;%&quot;,&quot;^&quot

of course the ^ could be any character you want Saturday 12.00

20.00

3.00am

Sunday [img http

 

[Tarwn]

When writing your options, write the value the same as you usually would in the text position, but do a replace on the string you put in value:

<option value=&quot;<%=Replace(yourProductVariable,&quot;%&quot;,&quot;%25&quot;)%>&quot;><%=yourProductName%></option>

The %25 is the character code for % so will resolve back to a % just like %20 resolves to a space character.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
With enough resources, time, and coffee, anything is possible.

 

[streborr]

Thanks GaryC123,

I've never used Replace before.
I was doing this: replace(strProdName,&quot;%&quot;,&quot;^&quot.
duh

It's amazing how well it works when it's done correctly!


Thanks to all who contributed,
Rick